CODEWITHSUNDEEP
sql-servert-sql
MissKnacki
MissKnacki

Reputation: 279

SQL SERVER : Stored Procedure WITH OUTPUT

I try to create a stored procedure in sql server in order to check username and password in authentication procedure. I have 3 parameters : @username to check, @password to check and @authent that return 1 if the user is authent and 0 if not. Apparently I did wrong because when I run code, I have this error :

Incorrect syntax to '@authent'

CREATE PROCEDURE authentUser 
    -- Add the parameters for the stored procedure here
    @username varchar50, 
    @password varchar50,
    @authent bit OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF EXISTS (SELECT * FROM PROFACE.dbo.Utilisateurs WHERE username = @username AND password = @password)
        @authent = 1;
END
RETURN
GO

I don't understand what is wrong with my code and how can I correct it.

Upvotes: 0

Views: 667

Answers (2)

Red Devil
Red Devil

Reputation: 2393

Try this 

CREATE PROCEDURE authentUser 
        -- Add the parameters for the stored procedure here
        @username varchar50, 
        @password varchar50,
        @authent bit OUTPUT
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for procedure here
        IF EXISTS (SELECT * FROM PROFACE.dbo.Utilisateurs WHERE username = @username AND password = @password)
           set @authent = 1;
    END
    RETURN
    GO

Upvotes: 2

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14198

Firstly, You need to use Set or Select to assign value

set @authent = 1; -- or select @authent = 1

Secondly, Make sure that you execute stored procedure like this way

DECLARE @result bit;

EXEC authentUser
    @username = '123',
    @password = '123',
    @authent = @result OUTPUT;

SELECT @result;

Read the following post to have a better understanding

How to set variable from a SQL query?

Upvotes: 3

Related Questions