CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdynamic-sql
richa verma
richa verma

Reputation: 287

Dynamic SQL not working for changing variable column name in SQL Server

I want to write a stored procedure which can be used to update IDs of owner name, backup contact, other contacts etc in a table. These IDs are to fetched from some other table. Instead of writing different stored procedure for all these contact information, I want to write a single dynamic SQL in which I can pass the column name as a variable name.

My stored procedure looks like:

CREATE PROCEDURE spUpdateUser
    (@recordid [NVARCHAR](50),
     @id [NVARCHAR](10),
     @user [NVARCHAR](50))
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET NOCOUNT ON;

    SET @sql = N'UPDATE [dbo].[table1]
                 SET'+ QUOTENAME(@user) + ' = (SELECT [dbo].[table2].User
                                               FROM [dbo].[table2]
                                               WHERE [dbo].[table2].id = ' + QUOTENAME(@id) + ')
                WHERE record = ' + QUOTENAME(@recordid)

   EXEC sp_executesql @sql;
END;
GO

After executing the query it runs without error but the user is not changed in table1.

What is missing in the procedure?

Upvotes: 0

Views: 153

Answers (1)

Thom A
Thom A

Reputation: 95534

Don't inject your parameters, parametrise them:

CREATE PROCEDURE spUpdateUser
    -- Add the parameters for the stored procedure here
(   @recordid [nvarchar](50), --Are your IDs really an nvarchar?
    @id [nvarchar](10), --Are your IDs really an nvarchar?
    @user sysname --As this is an object, lets use the correct datatype
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    DECLARE @sql NVARCHAR(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    SET @sql= N'UPDATE [dbo].[table1]' + @CRLF +
              N'SET ' + QUOTENAME(@user) + N' = (SELECT [table2].User' + @CRLF + --3 part naming for columns is deprecated, don't use it
              N'       FROM [dbo].[table2]' + @CRLF +
              N'       WHERE [table2].id= @id)' + @CRLF + --3 part naming for columns is deprecated, don't use it
              N'WHERE record = @recordid;';

   --PRINT @SQL; --Your Best Friend
   EXEC sp_executesql @sql, N'@id nvarchar(10), @recordid nvarchar(50)', @id, @recordid; --Assumes that IDs are an nvarchar again
END;
GO

Note I've left some comments in there for you to consume and review as well.

Upvotes: 2

Related Questions