Returning values from Stored Procedure

Hi Using Following Stored Procedure,

Which always returns Null,

What s the error,

CREATE PROCEDURE prLoginAuth

(

@.pStrUserName varchar(50),
@.pStrPassword varchar(50),
@.pOutput Varchar(20) Output
)

AS

Declare @.V_Facilities Varchar(50)

SELECT Facilities=@.V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@.pStrUserName and Password=@.pStrPassword)

If(@.V_Facilities=null)

Set @.pOutput = @.V_Facilities

Return @.pOutput;

Else

Set @.pOutput = @.V_Facilities

Return @.pOutput;

GO

Anyone correct this query , I want return the output from this procedure

Thanx in advance

Selva.R

I believe you are mixing two techniques for returning data from a stored procedure.

1. If you declare the parameter 'pOutput' as an output type parameter, you should NOT use it as the return value from the stored procedure. Simply assigning a value to it does the trick. (e.g. Select @.pOutput = @.V_Facilities). You pickup the result from your parameters collection.

2. If you decide to return the result as the returncode from the stored procedure, there is no need to define a parameter from the type OUTPUT. You just return the value (e.g. RETURN @.V_Facilities) and pick it up as the return value from your ExecuteScalar method

Hope this helps.

|||

In addition to what has already been mentioned, you can only ever return an integer using RETURN in a stored procedure. So any value that you try to return would have to be convertable to an integer ('0001' ok, 'hey joe' not ok :)

Second, you are never setting the value of @.V_Facilites, and I am guessing (since you didn't provide data or output :) that you are getting back a result set with a null value, as well as the return value = null

To set the value of a parameter to a column value, the syntax is:

SELECT @.variableName = column
FROM ....

Turned the other way around, it is an older syntax for renaming the output name of a column, so it wouldn't matter what was in the FROM clause, it would still return the value of the variable (in this case null since you haven't initialized it). For example:

DECLARE @.testValue int
SET @.testValue = 1

SELECT bob = @.testValue
--FROM anything

Returns

bob
--
1