CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
smk081
smk081

Reputation: 1145

SQL Server Stored Procedure Parameter

I am trying to create a stored procedure with one parameter. I want the stored procedure to perform an update query and the parameter that I pass when it executes is the table that should be updated. I have been unsuccessful with creating the procedure with the parameter.

CREATE PROCEDURE cleanq7 @tablename varchar(100)
AS
BEGIN
    UPDATE @tablename 
    SET IMPOSSIBLE_CASE = '1'
    WHERE q7='1'
GO

The message I receive when I run this is:

Msg 102, Level 15, State 1, Procedure cleanq7, Line 6
Incorrect syntax near '1'.

I tried just the indented update query on a table in test database and it functioned as expected, so I imagine this is an issue with my syntax for declaring the stored procedure.

Any help would be greatly appreciated!

Upvotes: 1

Views: 1655

Answers (1)

M.Ali
M.Ali

Reputation: 69524

CREATE PROCEDURE cleanq7
    @tablename NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(MAX); 

    SET @Sql = N'UPDATE ' + QUOTENAME(@tablename) +
               N' SET IMPOSSIBLE_CASE = ''1''
                  WHERE q7 = ''1'''

    EXECUTE sp_executesql @Sql
END
GO

Since you are passing the table name you will need to build your UPDATE statement dynamically and then Execute it using system stored procedure sp_executesql.

When you pass the table name as a String Sql Server treats it as a string not as an Object name. Using QUOTENAME() function puts square brackets [] around the passed table name and then sql server treats it as an object name.

QuoteName function also protects you against Sql injection attack.

Upvotes: 6

Related Questions