具有两个参数的sql函数。从VB .NET拨打电话 [英] sql function with two parameters. Call from VB .NET

问题描述

我写了一个返回标量的sql函数：如果密码正确，则返回1，否则返回0。

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

这正常工作。

我正在使用以下代码调用sql函数：

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)))

这时我要执行sqlcmd并获取返回值。

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

推荐答案

函数与不同存储过程

将函数转换为存储过程或将命令更改为

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

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

将CommandType设置为 Text 并使用 ExecuteScalar

set the CommandType as Text and use ExecuteScalar

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

这篇关于具有两个参数的sql函数。从VB .NET拨打电话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！