CODEWITHSUNDEEP
sql-serverstored-proceduressyntax-error
vivek padelkar
vivek padelkar

Reputation: 313

Getting error near 'SET' keyword in stored procedure

I created a stored procedure but I get error near 'SET' keyword:

Msg 156, Level 15, State 1, Procedure []usp_chkuseridpwd], Line 17
Incorrect syntax near the keyword 'set'.
incorrect syntax near 'set', expecting variable.

My code:

alter procedure [usp_chkuseridpwd]
as
begin
    declare 
        /*variable declaration */
        @sql as varchar(max),
        @tblname varchar(max),
        @user_name varchar(max),
        @user_password varchar(max),
        @count int,
        @passwd as varchar(max),

    set @sql = 'select COUNT(*) from user_master where USER_NAME like '+@user_name+' and pasword like '+@user_password+''
    exec(@sql)
end

Upvotes: 0

Views: 1057

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

You have one extra comma in the @passwd as varchar(max),

Removing the comma will solve your problem.

UPDATE:

This is not relevant to the error.

But you may need to include '%' when you are using with LIKE operator to get more relevant results. With out wildcard '%' you need to pass the exact string to get result. In case if you are know the partial string of the user name and want to fetch all the records, you need this '%'

So the stored procedure will be:

ALTER PROCEDURE [usp_chkuseridpwd]
AS
BEGIN
    DECLARE 
        /*variable declaration */
        @sql as varchar(max),
        @tblname varchar(max),
        @user_name varchar(max),
        @user_password varchar(max),
        @count int,
        @passwd as varchar(max) -- removing the comma 

    -- Added % for the LIKE operator
    SET @sql = 'SELECT COUNT(*) FROM user_master WHERE USER_NAME LIKE %' + @user_name + '% AND pasword LIKE %' + @user_password + '%'
    EXEC(@sql)
END

Upvotes: 2

toha
toha

Reputation: 5510

The query should be:

alter procedure [usp_chkuseridpwd]
as
begin
    declare 
        /*variable declaration */
        @sql as varchar(max),
        @tblname varchar(max),
        @user_name varchar(max),
        @user_password varchar(max),
        @count int,
        @passwd as varchar(max)

    set @sql = 'select COUNT(*) from user_master where USER_NAME like '+@user_name+' and pasword like '+@user_password+''
    exec(@sql)
end

Upvotes: 0

Related Questions