Stored Procedure Query

[Thatguy2001au]

Hi,

I am using a Function to call a Stored Procedure in SQL Server 2000 and passing it the variables - @Cuname and @Cupassword (ie: Cuname and Cupassword which are variables that have values takin fom a Form - Username and Password).

I want to select values in certain fields in a Table from the Database that I want returned depending on if the Username and Password match. I want to return the following:

customer_id
cfname
clname
cuername
cpassword

all from a CUSTOMERS table. If the username and password don't match, then i would like to return an error or some sort of variable so i can Tell that there was no match in the database.

Below is basically what i want to do, but i am unsure as to how to write up a stored procedure to make this work. If anyone can help me out, it would be greatly appreciated.

The reason I want the values returned is so I can then place them inside an array or cookie or something like that.


SELECT customer_id, cfname, clname, cusername, cpassword FROM CUSTOMERS WHERE cusername = @Cuname AND cpassword = @Cupassword

IF (@Cuname is NULL) OR (@Cuname = '') AND
(@Cupassword is NULL) OR (@Cupassword = '')

THEN
return and error

ELSE
return all the values which i can place in a cookie or Session Variable.

END IF

Thanks

[Roy Sinclair]

This should get you started:


CREATE PROCEDURE msp_CheckForValidUser
(
@CuName nvarchar(10) = null
,@CuPassword nvarchar(10) = null
)
AS
declare @ReturnValue int
if @CuName = null or @CuPassword = null
begin
Raiserror(N'Invalid Request.', 16, 1)
Return -@@Error
end
SELECT
customer_id
,cfname
,clname
,cusername
,cpassword
FROM CUSTOMERS
WHERE cusername = @Cuname AND cpassword = @Cupassword
if @@ROWCOUNT = 0
begin
Raiserror(N'Invalid Request.', 16, 1)
Return -@@Error
end
set @ReturnValue = @@ROWCOUNT
return @ReturnValue



What you also need to do is check for errors in your ASP code as well to pick up the error message returned by the stored procedure if things aren't ok.

[oracleguy]

You'll need to use the ADO command object to retrieve the values back too.

[Thatguy2001au]

Thanks Guys

You've definately put me on the right track. with a little bit of modification to my asp code and adding the stored procedure, things seem to be working fine now.

thanks alot for your help.