CODEWITHSUNDEEP
sql
MindBrain
MindBrain

Reputation: 7768

SQL Server : stored procedure not returning the correct results

I have created a stored procedure which deletes a list of usernames from the database. However the output of this script is weird. It treats the input which is the username input as a column. Why is it doing so ?

CREATE PROCEDURE [dbo].[sp_deleteUsersFromDb]( @username varchar(500)) AS
BEGIN
   DECLARE @SQL varchar(600)

   EXEC('UPDATE [dbo].USER SET DELETED = 1 WHERE USERNAME IN (' + @username + ')')

   PRINT N'DELETED THE USERS FROM THE DB'
END

When I run this I get

Msg 207, Level 16, State 1, Line 1
Invalid column name 'user1'.
DELETED THE USERS FROM THE DATABASE

Table schema is :

CREATE TABLE [dbo].[USER](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VERSION] [int] NOT NULL,
[USERNAME] [varchar](32) NOT NULL,
[DELETED] [bit] NULL,

    CONSTRAINT [USER_PK] 
   PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

Upvotes: 0

Views: 65

Answers (1)

AUSteve
AUSteve

Reputation: 3248

The @username parameter must contain quoted usernames, eg "'user1', 'user2'"

Upvotes: 2

Related Questions