Stored Procedures and Return Values 1

[SuperCyber]

I am trying to build a WHERE clause within a store procedure. Depending on the int value passed to @ID (the employee id number) it looks to see if the value of empAdmin is "YES" or "NO". The problem is that the @RETURN_VALUE is not returning anything for @Admin, but the SP returns the correct value for the empAdmin column. Below is the SP and the Output, I'm running SQL 2000 any help would be greatly appreciated.

Alter Procedure sproc_getAdmin
@ID int
AS
declare @Admin varchar(3)
SELECT empAdmin FROM tblEmployees WHERE empID=@ID
SET @Admin = 'empAdmin'
RETURN @Admin

************************************************************

Running dbo."sproc_getAdmin" ( @ID = 6 ).

empAdmin
------------------------------------------------------
YES
(1 row(s) returned)
@RETURN_VALUE =
Finished running dbo."sproc_getAdmin".

Thanks,
Brian

 

[tlbroadbent]

You must declare an OUTPUT parameter in a SP. You also need to load the value in the Select statement. See the following example.

Alter Procedure sproc_getAdmin
@ID int,
@Admin char(3) Output
AS

--How to execute this procedure
-- Declare @Admin varchar(3)
-- Execute sproc_getAdmin
-- @ID=6,
-- @Admin=@Admin Output

SELECT @Admin=empAdmin
FROM tblEmployees
WHERE eEmpID=@ID Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SuperCyber]

Im not sure what I'm doing wrong, the return is looking for the @Admin value but that is what I want to return...

Alter Procedure sproc_getAdmin
@ID int,
@Admin varchar(3) OUTPUT
AS
SELECT @Admin=empAdmin
FROM tblEmployees
WHERE empID=@ID

**********************************************************
Running dbo.&quot;sproc_getAdmin&quot; ( @ID = 6, @Admin = <DEFAULT> ).

Procedure 'sproc_getAdmin' expects parameter '@Admin', which was not supplied.
Procedure 'sproc_getAdmin' expects parameter '@Admin', which was not supplied.
Finished running dbo.&quot;sproc_getAdmin&quot;.



If I declare @Admin variable below the &quot;AS&quot; the return shows the correct value for the column empAdmin, but it isnt converting it to the correct output.

Alter Procedure sproc_getAdmin
@ID int
AS
DECLARE @Admin varchar(3)
SELECT @Admin=empAdmin
FROM tblEmployees
WHERE empID=@ID
RETURN @Admin

***********************************************************

Running dbo.&quot;sproc_getAdmin&quot; ( @ID = 6 ).

Syntax error converting the varchar value 'YES' to a column of data type int.
Syntax error converting the varchar value 'YES' to a column of data type int.
Finished running dbo.&quot;sproc_getAdmin&quot;.


Thanks,
Brian

 

[tlbroadbent]

Return will only return an integer value. If you want to use a Return code to return a value to your program then make the value an integer and convert it to text in the program. Otherwise, you need to use the OUTPUT parameter to return the value.

I think this would do what you want and be easier to use.

Alter Procedure sproc_getAdmin @ID int
AS

--How to execute this procedure
--
-- Execute sproc_getAdmin @ID=6
-- The return code will be
-- 1 for 'Yes' or 0 for 'No'

DECLARE @Admin Int

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

RETURN @Admin Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SuperCyber]

Terry,

Thank you very much the stored procedure works great! My only problem now is that it returns a -1 to my ASP page, but in the Query Analyzer it works properly. This is my ASP code, basicly I am just returning the code and writing the response until I figure this out.
** strUserID pulls the EmpID from the Session object.

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

Response.Write varReturn


Thanks again,
Brian

 

[SuperCyber]

I'm not sure if this is the correct way to do it but it works. Since I reference the return variable with &quot;SET prm2&quot; object I stuck it into my &quot;if..then..else&quot; 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(&quot;ADODB.Command&quot
Set cmdGetAdmin.ActiveConnection = connAPS
cmdGetAdmin.CommandType = adCmdStoredProc
cmdGetAdmin.CommandText = &quot;sproc_getAdmin&quot;

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

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

Brian