sql function with two parameters. Call from VB .NET

Question

I wrote a sql function which returns a scalar: 1 if the password is correct, 0 otherwise.

   CREATE FUNCTION check_credential(  @User_IN nvarchar(50),  @Pwd_IN nvarchar(50)) 
   RETURNS int
   AS
   BEGIN
   DECLARE @PwdRES int
   DECLARE @PWD_DB varbinary(255)

   IF (@User_IN is not null and @User_IN != '' and @Pwd_IN is not null and @Pwd_IN != '' )
begin
    SELECT  @PWD_DB = password FROM myTable WHERE username = @User_IN
    SET @PwdRES = pwdcompare(@Pwd_IN, @PWD_DB )
end
ELSE
    SET @PwdRES = 0

   RETURN @PwdRES
   END

And this works correctly.

I'm using the following code for calling sql function:

    Dim conn As SqlConnection
    Dim sqlcmd As SqlCommand
    Dim da As SqlClient.SqlDataAdapter
    Dim table As DataTable
    Dim Result As Integer

    conn = New SqlConnection(ConnectionString)

    sqlcmd = New SqlClient.SqlCommand()
    sqlcmd.Connection = conn
    conn.Open()

    sqlcmd.CommandType = CommandType.StoredProcedure
    sqlcmd.CommandText = "check_credential"

    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@username", Utilities.NothingToDBNull(user)))
    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@password", Utilities.NothingToDBNull(password)))

At this point I want execute the sqlcmd and get the returning value.

podiluska · Accepted Answer · 2012-09-12 08:56:24Z

3

A function is different to a stored procedure

Either convert your function to a stored procedure or change your command to

"select dbo.check_credential(@username, @password)"

set the CommandType as Text and use ExecuteScalar

Result = Convert.ToInt32(cmd.ExecuteScalar())

edited Sep 12, 2012 at 8:56

answered Sep 12, 2012 at 8:44

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

GVillani82 Over a year ago

Is not possible use function?

John Woo Over a year ago

@Joseph82 wrap the function inside stored procedure.

podiluska Over a year ago

@Joseph82 Why is it so important that it is a function?

GVillani82 Over a year ago

Ok, I changed my command in "select dbo.check_credential(@username, @password)". Now how can I assign the retunring value to the predefined variable (Dim Result As Integer) ?

GVillani82 Over a year ago

@podiluska because there are many function I'm using ...and I can't convert all in procedure

|

Simon · Accepted Answer · 2012-09-12 08:46:45Z

1

Make @PwdRES an output parameter. Also declare the parameter as output in your code and then it will contain the value returned from the function once the command has been executed:

sqlcmd.Parameters.Add("@PwdRES ", SqlDbType.Int)
sqlcmd.Parameters("@PwdRES ").Direction = ParameterDirection.Output)

You will need to convert your Function to a Stored Procedure (or add a stored procedure that calls the function) to get it to work I think.

answered Sep 12, 2012 at 8:46

Simon

6,17214 gold badges65 silver badges99 bronze badges

1 Comment

Simon Over a year ago

Take a look at the examples in this thread: social.msdn.microsoft.com/Forums/sv/adodotnetdataproviders/…

Breeze · Accepted Answer · 2016-02-24 09:55:30Z

0

You need to define an output parameter in that case.

In the SQL function:

@Value int output,   // Define it in the Parameters

Get your calculated value in this parameter say

@value = 0

Now in the C# code:

SqlParameter user = cmd.Parameters.Add("@value", SqlDbType.int);
user.Direction = ParameterDirection.Output;

Here you can check whether user is 0 or 1

edited Feb 24, 2016 at 9:55

Breeze

2,0982 gold badges34 silver badges45 bronze badges

answered Sep 12, 2012 at 8:45

RL89

1,9165 gold badges23 silver badges40 bronze badges

Collectives™ on Stack Overflow

sql function with two parameters. Call from VB .NET

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related