Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored Procedure updating columns Dynamically

Posted on 2012-04-02
2
Medium Priority
?
292 Views
Last Modified: 2012-08-14
I have a stored procedure that I want to have save a value to a column based on the input I throw at it. I'm checking for existing column and datatype.

@Registration_ID NUMERIC
,@ColumnName VARCHAR(255)
,@ColumnValue VARCHAR(255)
,@ColumnDataType VARCHAR(255) OUTPUT
      

      SELECT @ColumnDataType = [Data_Type]
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME='Checklist'
      AND COLUMN_NAME=@ColumnName
      IF @ColumnDataType <> ''

----- @ColumnDataType here is set to a value of 'bit'

                        UPDATE [dbo].[Checklist]
                        SET @ColumnName =@ColumnValue
                              ,[Modified_Timestamp] = CURRENT_TIMESTAMP
                              ,[Modified_Action] = 'Update'
                        WHERE [Registration_ID] = @Registration_ID

       END


However when I run the SP, it does not update the column to the value I supply.

I tried using CAST with a dynamic SQL but it ended up in misery, so I'm avoiding that route unless I have to.
The SQL I used was:

SET @ColumnName = CAST(@ColumnValue AS @ColumnDataType)

Any suggestions as to what I'm doing wrong?
0
Comment
Question by:Rodrigo Munera
2 Comments
 
LVL 7

Accepted Solution

by:
micropc1 earned 2000 total points
ID: 37799136
Since @ColumnName is not a field name you need to execute the dynamic update statement in either the EXEC command or the sp_executesql stored procedure.

Like...

EXEC('UPDATE [dbo].[Checklist] SET ' + @ColumnName + '=' + @ColumnValue + '
	,[Modified_Timestamp] = CURRENT_TIMESTAMP
	,[Modified_Action] = ''Update'' 
	WHERE [Registration_ID] = ' + @Registration_ID)

Open in new window


http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
0
 
LVL 4

Author Closing Comment

by:Rodrigo Munera
ID: 37800834
Thank YOU!
I had started going in this direction but was thinking about the string wrong.
Your example cleared up for me a lot and helped me get to a working solution!

Ended up using SP_EXECUTESQL, that way I could declare the variables I was passing and was able to type them correctly :) thankyouthankyouthankyou!!!

This is what the final code ended up looking like:


SET @SQLString=N'UPDATE [dbo].[Checklist]
                              SET '+@ColumnName+' = @ColValue
                                    ,[Modified_Operator] = @Oper
                                    ,[Modified_Timestamp] = CURRENT_TIMESTAMP
                                    ,[Modified_Action] = ''Update''
                              WHERE [Registration_ID] = @Reg_ID';
SET @Params=N'@ColValue '+@ColumnDataType+'
                         ,@Oper VARCHAR(10)
                         ,@Reg_ID NUMERIC';

EXECUTE SP_EXECUTESQL      @SQLString
                                       ,@Params
                                       ,@ColValue=@ColumnValue
                                       ,@Oper=@Operator
                                       ,@Reg_ID=@Registration_ID;
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question