How to get a single column's value from a stored procedure call within a stored procedure?

Question

I have the following procedure:

SP_Validate_User_Main
(
  @UserName VARCHAR(20),
  @Password VARCHAR(20),
  @From VARCHAR(10),
  @IP VARCHAR(15)
)

-- does a bunch of stuff, then the following line is the last to execute

EXECUTE SP_Validate_User @aUserName = @UserName, @aPassword = @Password

Depending on the validity of the data passed to SP_Validate_User, 1 of 2 statements will then get executed:

SELECT 
    someNumber AS ErrorCode, 
    otherNumber AS AttemptsRemaining, 
    thirdNumber AS LockoutTimeRemaining
RETURN

or

SELECT * 
FROM Users 
WHERE ...

RETURN

I'm trying to expand my first procedure, SP_Validate_User_Main, to insert a record into a new audit table every time a user attempts to login, regardless of whether or not it was successful. So I've added these 2 lines to SP_Validate_User_Main:

INSERT INTO LoginAudit (IP, Username, Password, AttemptDate, Result)
VALUES (@IP, @UserName, @Password, GETDATE(), @Result)

How can I assign a value to @Result such that it would contain either ErrorCode or 0? If ErrorCode exists in the data set returned by SP_Validate_User, I want that value. If it does not, I want it to contain the value 0.

I found this SO question:

Select columns from result set of stored procedure

Which seems to be asking what I want, but has no accepted solution and the top-voted answer is inapplicable as SP_Validate_User will return 1 of 2 schemas.

All of the other questions I've found while searching around were in regards to handling the data code-side rather than DB.

Basically, I'm looking for the SQL equivalent of this psuedo-code:

if (resultSet.Columns.Count > 3)
    @Result = 0;
else
    @Result = resultSet["ErrorCode"];

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Nov 07 '16 at 21:18
@marc_s Thanks for the tip. We actually call 'em `USP_whatever`, but I changed the names a bit whenever posting to SO. I was not aware of that, however. — sab669, Nov 07 '16 at 21:22
Are you looking for something like @@ERROR or ERROR_NUMBER() — S3S, Nov 07 '16 at 22:01

score 2 · Answer 1 · answered Nov 07 '16 at 21:46

The SP_Validate_User stored proc is the one that is in two parts, one statement that returns an error code, and one that pulls back users, correct?

If this is what's happening, why don't you add the IP parameter to the SP_Validate_User proc, then have two insert statements in that proc, one that inserts if the error condition is met, and one that inserts if the error condition is not met.

INSERT INTO LoginAudit (IP, Username, Password, AttemptDate, Result)
VALUES (@IP, @UserName, @Password, GETDATE(), someNumber)

SELECT 
someNumber AS ErrorCode, 
otherNumber AS AttemptsRemaining, 
thirdNumber AS LockoutTimeRemaining

RETURN

or

INSERT INTO LoginAudit (IP, Username, Password, AttemptDate, Result)
VALUES (@IP, @UserName, @Password, GETDATE(), 0)

SELECT * 
FROM Users 
WHERE ...

RETURN

This might be easiest. `Validate _ User _ Main` actually calls one of multiple sub-SP depending on a few things, so I wanted to add it there to reduce redundancy across the child SPs, but I night have to... — sab669, Nov 07 '16 at 22:03
I see your point. You could maybe add a dummy column to the SELECT * FROM Users WHERE ... query, so its something like SELECT 0 as ErrorCode, * from Users WHERE...Then you could always just assign the ErrorCode value to @Result — Pops, Nov 07 '16 at 22:12

How to get a single column's value from a stored procedure call within a stored procedure?

1 Answers1