Increasing script efficiency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Increasing script efficiency

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-03 : 11:37:21

I have a script:

UPDATE dbo.format_mortgage_event_history
SET Amended_Completed_Date = '200' + SUBSTRING(Amended_Completed_Date,3,LEN(Amended_Completed_Date))
WHERE LEFT(Amended_Completed_Date,2) = '10'

for changing the date year format in a column.

Is there a way I can reference a variable for the column name ('Amended_Completed_Date' in this case) at the beginning of the script instead of having the change Amended_Completed_Date four times when using this script for another column name?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 13:00:52

You need to use dynamic SQL for that

DECLARE @Sql varchar(8000)

SET @Sql='UPDATE dbo.format_mortgage_event_history
SET '+@ColName + '= ''200'' + SUBSTRING('+ @ColName +',3,LEN('+ @ColName +'))
WHERE LEFT(' +@ColName + ',2) = ''10'''

EXEC(@Sql)

You may pass value of @ColName as a parameter to SP which has this script

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-03 : 13:02:41

Got it! Many thanks visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources