Cannot return recordset when I'm using parameters?

[Jacob Avlund]

Hello all,

I have a simple stored procedure:

CREATE PROCEDURE log_in
@user varchar,
@password varchar
AS
SELECT ID FROM users
WHERE user = '@user' AND password = '@password'
GO

and some code which adds the two parameters and then returns a resultset or
a scalar depending on which method in my connection object (database
application wrapper object) I'm calling. Problem is this: null is returned.
The SP runs fine in SQL Server Enterprise Manager and when I hardcode the
correct values into the SP, the scalar is returned as it's supposed to. I
know that the values are passed to the parameterization and no exceptions of
any kind are thrown. Anyone know what's wrong? I've been searching the web
for days now for a solution, but no luck.

If this is possible without using output variables, I'd really appreciate
it, as I'd have to make lots of changes to my database wrapper class if
that's not the case.

Best regards and thank in advance,

J.

 

[W.G. Ryan eMVP]

What does the calling code look like? I'll bet if you check out Bill
Vaughn's article at http://www.betav.com/msdn_magazine.htm "Retrieving the
Gazoutas: Understanding SQL Server Return Codes and Output Parameters"
You'll probably find the answer