Help me..calling a scalar function in sql server within c#?

basim salih

8y3k

i have a scalar function "[finance_dep].[sum_view1]" in sql server :

ALTER function [finance_dep].[sum_view1] (@dt1 datetime, @dt2 datetime )
returns decimal
as begin
Declare @d decimal
set @d=(select sum([amount_dnr]) from [finance_dep].[view1] where [wsl_date] between @dt1 and @dt2)
return @d
end

when i calling it from c#:

public static decimal sum_v(DateTime @dt1, DateTime @dt2)
{
return exscalarfunction("select cast(finance_dep.sum_view1(@dt1,@dt2) as decimal) ", CommandType.Text,
CreateParam("@dt1", SqlDbType.DateTime, @dt1),
CreateParam("@dt2", SqlDbType.DateTime, @dt2));
}
protected static decimal exscalarfunction(string querystoredprocedure, CommandType type, params SqlParameter[] param)
{
cn.Open();
//*******************
decimal f;
cmd = new SqlCommand(querystoredprocedure, cn);
cmd.CommandType = type;
cmd.Parameters.AddRange(param);
//********************
f = (decimal) cmd.ExecuteScalar();// here appear error with message ()
//***********************************
cn.Close();
return f;
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah8y ago
Accepted answer
yes

SQL server code is working fine as you confirmed,

now after getting value in var, you can check if var is not null then do casting or return 0 or something like that
+2
basim salih8y ago
Thank you very much That itswork correctly ...
+1
basim salih8y ago
i checked the function in sql server .. its ok.

if i apply var result = cmd.ExecuteScalar(); , is the next step perform cast?
+1
Nilesh Shah8y ago
proceed step by step

did you first test the SP by passing same parameter in SQL server (run from SSMS using exec command)? does the SP alone work ok?

if SP is alright, then dont cast directly to decimal, instead first get the result in a var, then see what you get.

e.g.

var result = cmd.ExecuteScalar();
+1