Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Stored Procedure updating columns Dynamically

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

789 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