How to return an Output Parameter

[abienz]

Hi guys, I've got a relatively simple question here to do with SQL and ASP, hope you can help.

Here's my sp...

CREATE PROCEDURE [sp_insert_siteListings]

(@siteUsername [varchar](50),
@sitePassword [varchar](50))

AS

BEGIN

DECLARE @UserVal [int]

EXEC sp_validate_Username @siteUsername, @UserVal OUTPUT

IF @UserVal = 0

INSERT INTO [siteListings]
([siteUsername],
[sitePassword])
VALUES
(@siteUsername,
@sitePassword)
END

RETURN @UserVal
GO

CREATE PROCEDURE [sp_Validate_Username]

@UserName [varchar](50),
@UserExists [int] OUTPUT

AS

BEGIN
DECLARE @UserVar [varchar](50)

SELECT @UserVar = siteUsername
FROM siteListings
WHERE siteUsername = @UserName

IF @UserVar <> NULL
SET @UserExists = 1
ELSE
SET @UserExists = 0
END
GO

These I'm happy with.

My ASP code is like so... (modified)

strSQLS = &quot;EXEC sp_insert_SiteListings '&quot; & fUserName & &quot;', '&quot; & fPassword & &quot;'&quot;

Set objRSS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRSS.Open strSQLS, objConn

I've obviously declared all variables and included a connection object too. What I want to know is what to write to display and use the @UserVal OUTPUT variable from my SQL. I've tried objRSS.Parameters(&quot;@UserVal&quot.Value but I get an error saying that object doesn't support the Parameters method, do I HAVE to use a Command object to do this? If so , how do I go about it?

any help would be appreciated.

TIA

Al.

 

[rac2]

Here is a way to do that. It uses a command object.

var cmdAdd = Server.CreateObject("ADODB.Command")
    cmdAdd.ActiveConnection = objConn
    cmdAdd.CommandType = adCmdText
    strSQLS = "EXEC sp_insert_SiteListings '" & fUserName & "', '" & fPassword & "'"
    cmdAdd.CommandText = strSQLS

    cmdAdd.Parameters.Append( cmdAdd.CreateParameter("is_user", adInteger, adParamReturnValue, 4, 0 )) )
		
Set objRSS = cmdAdd.Execute()
    objRSS.close()
isUser = cmdAdd.Parameters.Item("is_user").value

Possibly in VBScript
isUser = cmdAdd.Parameters(&quot;is_user&quot

Also you need to have somewhere
adCmdText = 1
adParamReturnValue = 4

As you are not using the recordset you could append three parameters and use adCmdStoredProc = 4

var cmdAdd = Server.CreateObject("ADODB.Command")
    cmdAdd.ActiveConnection = objConn
    cmdAdd.CommandType = adCmdStoredProc
    cmdAdd.CommandText = "sp_insert_SiteListings"

    cmdAdd.Parameters.Append( cmdAdd.CreateParameter("is_user", adInteger, adParamReturnValue, 4, 0 )) )
    cmdAdd.Parameters.Append( cmdAdd.CreateParameter("user_name", adVarchar, adParamInput, 50, fUserName )) )
    cmdAdd.Parameters.Append( cmdAdd.CreateParameter("secret_word", adVarchar, adParamInput, 50, fPassword )) )
		
cmdAdd.Execute()

isUser = cmdAdd.Parameters.Item("is_user").value

 

[rac2]

Oh by the way a return value and an output parameter are two different things. There is one return value and it is an integer; there can be any number of output parameters and they can be of any data type.

The general idea in my post is correct but I may not have translated it correctly from JScript to VBScript.