Getting return value from stored procedure

Question

0.00/5 (No votes)

See more:

I have the following stored procedure:

SQL

ALTER PROCEDURE spLogin
       @CAD_WEB_LOGIN_USERNAME varchar(60),
       @CAD_WEB_LOGIN_PASSWORD varchar(60),
       @Result int output
AS
BEGIN
    SELECT @Result=CAD_NUMBER
     FROM   CMACADDR
     WHERE  CAD_WEB_LOGIN_USERNAME = @CAD_WEB_LOGIN_USERNAME
            AND CAD_WEB_LOGIN_PASSWORD = @CAD_WEB_LOGIN_PASSWORD

     RETURN @Result -- return value

END

Note: It's Returning Null value
What's the logic for this Stored Procedure
Thanks

Posted 23-Dec-13 1:55am

KRI19

Updated 23-Dec-13 4:31am

gvprabu

v2

Add a Solution

4 solutions

Solution 1

Have a read of this stackoverflow [^] question the first answer should help you understand how to do it

Posted 23-Dec-13 1:57am

Simon_Whale

v2

Solution 2

Quote:
What's the logic for this Stored Procedure

The SELECT query returns the NULL value based on logon UserName & Password(that means no matching records for that user).

If you don't want NULL then you could convert that to blank string.

Posted 23-Dec-13 2:01am

thatraja

Solution 4

you cant try this also...it's a return values from the @Result or you can check also with the help of Alt+F5. just try this

SQL

ALTER PROCEDURE spLogin
@CAD_WEB_LOGIN_USERNAME varchar(60),
@CAD_WEB_LOGIN_PASSWORD varchar(60)
AS
BEGIN
DECLARE @Result INT
SET @Result=0
IF EXISTS(SELECT CAD_NUMBER FROM  CMACADDR 
WHERE CAD_WEB_LOGIN_USERNAME = @CAD_WEB_LOGIN_USERNAME
AND CAD_WEB_LOGIN_PASSWORD = @CAD_WEB_LOGIN_PASSWORD)
BEGIN
	SET @Result=(SELECT ISNULL(CAD_NUMBER,0)
	FROM CMACADDR
	WHERE CAD_WEB_LOGIN_USERNAME = @CAD_WEB_LOGIN_USERNAME
	AND CAD_WEB_LOGIN_PASSWORD = @CAD_WEB_LOGIN_PASSWORD)
END 
 Select @Result
RETURN

Posted 23-Dec-13 2:49am

joginder-banger

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Renuka Ruke · Accepted Answer · 2013-12-23T02:08:00

SQL

ALTER PROCEDURE spLogin
@CAD_WEB_LOGIN_USERNAME varchar(60),
@CAD_WEB_LOGIN_PASSWORD varchar(60)
AS
BEGIN
DECLARE @Result INT
SET @Result=0
IF EXISTS(SELECT CAD_NUMBER FROM  CMACADDR 
WHERE CAD_WEB_LOGIN_USERNAME = @CAD_WEB_LOGIN_USERNAME
AND CAD_WEB_LOGIN_PASSWORD = @CAD_WEB_LOGIN_PASSWORD)
BEGIN
	SET @Result=(SELECT ISNULL(CAD_NUMBER,0)
	FROM CMACADDR
	WHERE CAD_WEB_LOGIN_USERNAME = @CAD_WEB_LOGIN_USERNAME
	AND CAD_WEB_LOGIN_PASSWORD = @CAD_WEB_LOGIN_PASSWORD)
END 

RETURN @Result -- return value

If returns 0 means no match else give you CAD_NUMBER