SQL stored procedure call with output parameter in VBScript

Question

I wrote a VBScript function to call a stored procedure. In the past, I wrote several functions that call stored procedures with input parameters, but in this case I need to work with the Output parameter.

In another application, I call the same stored procedure using the Entity Framework, so the stored procedure is fine.

Here is my code:

Function checkAccess(userid,link) isAllowed = false set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = "Check_Permission" cmd.ActiveConnection = Conn cmd.NamedParameters = true cmd.CommandType = adCmdStoredProc cmd.Parameters.Append(cmd.CreateParameter("@Login", adVarChar, adParamInput, 50, userId)) cmd.Parameters.Append(cmd.CreateParameter("@LinkId", adInteger, adParamInput, 50, link)) cmd.Parameters.Append(cmd.CreateParameter("@IsAllowed", adBoolean, adParamOutput, 10, isAllowed)) checkAccess = isAllowed End Function 

This function always returns false. How can I make it work?

Answer 1

You should return the value of your output parameter:

 checkAccess = cmd.Parameters("@IsAllowed").Value 

In addition, the output parameters in ADO do not require an initial value, and the adBoolean parameters do not require a size, so you can change your last parameter to:

 cmd.Parameters.Append(cmd.CreateParameter("@IsAllowed", adBoolean, adParamOutput)) 

You can also get rid of the isAllowed variable since it is no longer needed.