Stored Procedures and Return Values

[SuperCyber]

My problem is that I get a -1 returned to my ASP page from my SP, but in the Query Analyzer it works properly. Basicly I am just returning the code and writing the response until I figure this out. Below I have listed the SP (Thanks to Terry Broadbent) and my ASP code. Any help is greatly appreciated.


Alter Procedure sproc_getAdmin @ID int
AS
DECLARE @Admin Int

SELECT @Admin=
Case empAdmin
When 'Yes' Then 1 Else 0 End
FROM tblEmployees
WHERE empID=@ID

RETURN @Admin

*********************************************************
** strUserID pulls the EmpID from the Session object.

Dim lngID, cmdGetAdmin, prm, varReturn
lngID = strUserID
Set cmdGetAdmin = Server.CreateObject("ADODB.Command&quot
Set cmdGetAdmin.ActiveConnection = connAPS
cmdGetAdmin.CommandType = adCmdStoredProc
cmdGetAdmin.CommandText = "sproc_getAdmin"
Set prm = cmdGetAdmin.CreateParameter("@ID",adInteger,adParamInput,,lngID)
cmdGetAdmin.Parameters.Append prm
cmdGetAdmin.Execute varReturn

Response.Write varReturn


Thanks,
Brian

 

[bassguy]

Hi, I think the error may be in the lack of setting @admin as an output variable before the AS

here is what I do and it seems to work


ALTER proc ArtworkTypeAdd
@ArtworkType varchar(25) = null,
@retval varchar(50)=null output
as
if @ArtworkType is null

return 1
else if (select count(*) from ArtworkType where ArtworkType like @ArtworkType)>0
return 2
else insert into ArtworkType(ArtworkType) values(@ArtworkType)
return 0

I hope this helps

Bassguy

 

[SuperCyber]

I'm not sure if this is the correct way to do it but it works. Since I reference the return variable with "SET prm2" object I stuck it into my "if..then..else" statement and it returns the proper value from the SP. Hope this can help someone else.

Dim varID, cmdGetAdmin, prm, prm2, varReturn
varID = strUserID
varReturn = null
Set cmdGetAdmin = Server.CreateObject("ADODB.Command&quot
Set cmdGetAdmin.ActiveConnection = connAPS
cmdGetAdmin.CommandType = adCmdStoredProc
cmdGetAdmin.CommandText = "sproc_getAdmin"

Set prm = cmdGetAdmin.CreateParameter("@ID",adInteger,adParamInput,,varID)
cmdGetAdmin.Parameters.Append prm
Set prm2 = cmdGetAdmin.CreateParameter("@Admin",adInteger,adParamReturnValue,,varReturn)
cmdGetAdmin.Parameters.Append prm2
cmdGetAdmin.Execute

if prm2 = 1 then
connAPS.Close
Set connAPS = nothing
Response.Redirect"../Admin_page.asp"
else
connAPS.Close
Set connAPS = nothing
Response.Redirect"../emp_page.asp"
end if

Brian