Solved

Stored Procedure updating columns Dynamically

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

18 Experts available now in Live!

Get 1:1 Help Now