Solved

Stored Procedure updating columns Dynamically

Posted on 2012-04-02
2
283 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now