Calling SQL Stored Procedure with Output Parameter in VBScript

Question

I've written a VBScript function to call a stored procedure. In the past, I've written a number of functions calling stored procedures with input parameters, but in this instance, I need to work with an Output parameter. In another application, I call the exact same stored procedure using the Entity Framework, so the stored procedure is fine. Here's 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 do I make it work?

Accepted Answer

You should return the value of your output parameter:

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

Also, output parameters in ADO don't require an initial value and adBoolean parameters don't require a size, so you could change your the last paramter to:

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

You could also get rid of your isAllowed variable since it is no longer necessary.

Calling SQL Stored Procedure with Output Parameter in VBScript

Solution

Problem