|
I am trying to add a new stored procedure and I keep getting statement errors.
Can someone please take a look and this statement and tell me if they can see where is it failing?
The query analyzer is telling me that it is failing on the set command but to me this is correct.
CREATE PROCEDURE dbo.sp_Auth_GetCredentials
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Auth_GetCredentials]
@user varchar(50),
@pwd varchar(50)
AS
DECLARE @ReturnValue as int
SELECT @ReturnValue = COUNT(*) FROM auth WHERE (userName = @user AND userPwd = @pwd)
RETURN @ReturnValue
|
|
|
You have several errors in your code.
1. You are trying to Alter a stored procedure before you creat it.
2. Your are using the RETURN statement incorrectly. WHat you want to to is use the RETURN Statement for bringing back the error status. It will do that automatically. Then you check if it is 0. If it is there were no SQL Errors if it is NOT 0 than SQL Server had an error which you can then handle in your Asp.Net code.
I think what you wnat to do to create this procedure is this...
CREATE PROCEDURE dbo.sp_Auth_GetCredentials
@user varchar(50),
@pwd varchar(50),
@ReturnValue int Output
AS
SELECT @ReturnValue = COUNT(*)
FROM auth
WHERE userName = @user
AND userPwd = @pwd
GO
ENjoy!
JB
|
|
|
|
|
|
|
// |