Calling SQL function in C#

Hello,

I am trying to call a SQL Function in C#, as I want to get the value binded back to the column in a datagrid.

Any answers will be appreciated ............Urgent.

:)

coinsdrop, dont know of a way using ado.net to call functions. but as a work around, you could create a stored procedure and select avariable which is set to the value of the function. then youcould use a Sql Command object and call the stored procedureprogrammatically. hope this is helpful. -- jp|||

Hi coinsdrop,

This depends on what kind of result you're retrieving.

If you're getting a record collection as result set, you can create a SqlDataAddapter, fill the result of a SELECT statement to a DataSet.

If you're trying to return some single value, you can use output parameters or return value.

I suggest you take a look at the ADO.NET tutorial. It covers all these technologies.

http://msdn2.microsoft.com/en-us/data/aa937699.aspx

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

Call a storedproc in select from block

Hi everyone,

I have a storedproc. This proc send back a value. how can i call this storedproc in select from block. Or what is your advise for other ways....

Select *

, (Exec MyStoredProc MyParam) as Field1

From Table1

It's complicated. You need to set up a (possible looped back) linked server and invoke it through the OPENROWSET() function. It is is explained here

http://www.sqlmag.com/Article/ArticleID/19842/sql_server_19842.html

You may need to enable the 'Ad Hoc Distributed Queries' sp_configure options in SQL 2005 (not sure ... I didn't try)

From a TSQL perspective, one of the main reasons we don't let you invoke procedures from inside queries is that we cannot determine the shape of the result set at the time we compile the query plan. Moreover, if the procedure has side effects, the semantics become unclear (e.g. resuls are different if you invoke once per row or once per query).

By using the "trick" above, the full distributed query machinery kicks into action which involves a lot of extra overhead not found in normal queries. Frankly, I'm not sure what the semantic is if you try something like

SELECT * FROM mytable JOIN OPENROWSET(..., 'EXEC myproc') ...

Perhaps you can try rewriting your code to use a table-valued function if it doesn't have side effects. This will give you better performance and predictable semantics.

calculations memory error

Deleted the measure part of a cube (by mistake) and put them back again (the same measures).

It works fine. All measures were there and all calculations when browsing the cube.

but I cannot change the calculations. "internal memory error corruption" (or like),

They don't show up in the calculations.

How can I get back my calculations?

Process the cube : no errors but still no calculations

There was a known bug in the BI Dev Studio in the calculation tab which caused this error. Upgrading to SP2 should solve this.

Calculating change in something compared to avg of 3 weeks back

Hi all.
Goal: report showing me for each category, the change in number or
percentage compared to the same category. I will try to explain it:
I have a SQL that returns something like
MyValue | Category | Date
63.61 Cat1 2006-11-30 00:00:00.000
65.51 Cat1 2006-12-07 00:00:00.000
63.31 Cat1 2006-12-14 00:00:00.000
60.51 Cat1 2006-12-21 00:00:00.000
50.01 Cat1 2006-12-28 00:00:00.000
10.71 Cat2 2006-11-30 00:00:00.000
20.91 Cat2 2006-12-07 00:00:00.000
19.61 Cat2 2006-12-14 00:00:00.000
18.51 Cat2 2006-12-21 00:00:00.000
13.01 Cat2 2006-12-28 00:00:00.000
My SQL already made sure that each item gets "grouped" into the time unit
that I wise, in this case that unit is a week.
The reports should end up showing a table, in which I have something like:
Cat1 : +10%
Cat2: -3%
The 10 and 3 % values are calculated by taking the last unit of time (last 7
days) and comparing it to the average of the 3 previous units of time (not
including the last one, so it is basically comparing row 0 to avg of rows
1,2 and 3.)
Now, I thought that I may be able to do it using the Table functions, but
all I managed to do is find the "=Previous(Fields!MyVal.Value)" function.
This would be great if I could do something like Previous(Previous()), but I
cannot. Anyone has a simple way of writing in a table cell a function
calculating the value of the previous 3 cells? I couldn't figure out any way
that lets me refer in one cell in the table other cells in the table, which
kind of sucks cause I expected it to be like Excel in that sense.
Any help (for doing it my way or a completely different way)?
thanksOn Mar 2, 7:27 pm, "csmba" <c...@.nowhere.com> wrote:
> Hi all.
> Goal: report showing me for each category, the change in number or
> percentage compared to the same category. I will try to explain it:
> I have a SQL that returns something like
> MyValue | Category | Date
> 63.61 Cat1 2006-11-30 00:00:00.000
> 65.51 Cat1 2006-12-07 00:00:00.000
> 63.31 Cat1 2006-12-14 00:00:00.000
> 60.51 Cat1 2006-12-21 00:00:00.000
> 50.01 Cat1 2006-12-28 00:00:00.000
> 10.71 Cat2 2006-11-30 00:00:00.000
> 20.91 Cat2 2006-12-07 00:00:00.000
> 19.61 Cat2 2006-12-14 00:00:00.000
> 18.51 Cat2 2006-12-21 00:00:00.000
> 13.01 Cat2 2006-12-28 00:00:00.000
> My SQL already made sure that each item gets "grouped" into the time unit
> that I wise, in this case that unit is a week.
> The reports should end up showing a table, in which I have something like:
> Cat1 : +10%
> Cat2: -3%
> The 10 and 3 % values are calculated by taking the last unit of time (last 7
> days) and comparing it to the average of the 3 previous units of time (not
> including the last one, so it is basically comparing row 0 to avg of rows
> 1,2 and 3.)
> Now, I thought that I may be able to do it using the Table functions, but
> all I managed to do is find the "=Previous(Fields!MyVal.Value)" function.
> This would be great if I could do something like Previous(Previous()), but I
> cannot. Anyone has a simple way of writing in a table cell a function
> calculating the value of the previous 3 cells? I couldn't figure out any way
> that lets me refer in one cell in the table other cells in the table, which
> kind of sucks cause I expected it to be like Excel in that sense.
> Any help (for doing it my way or a completely different way)?
> thanks
I would suggest performing this action as part of the stored procedure
or query that sources the report; since T-SQL is quite a bit more
dynamic. You should probably use either a loop or cursor to parse
through the information to determine the desired output. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer