SQL Server Performance Forum – Threads Archive

SPROC for dynamic column???

I have the follwoing SPROC (it’s not working yet): CREATE PROCEDURE [dbo].[usp_UpdateBudgetUpdate]
@Budget decimal (18,3), @WPID nvarchar(100), @FY nvarchar(4)
AS
UPDATE Budget SET WP_Budget.+’@FY’+ = @Budget WHERE Identifier = @WPID
GO
The column WP_Budget.* can either be:
WP_Budget.FY04
WP_Budget.FY05
WP_Budget.FY06
WP_Budget.FY07
WP_Budget.FY08
WP_Budget.FY09
WP_Budget.FY10
WP_Budget.FY11
WP_Budget.FY12
WP_Budget.FY13……..and up to 20 I’d like to not have to create 20 SPROCs. Any suggestions are welcome! Thanks!

---

You could use this syntax to create dynamic SQL – DECLARE @SQL VARCHAR(1000) SET @SQL = ‘UPDATE Budget SET WP_Budget.’
+ CAST(@FY AS VARCHAR(10))
+ ‘ = ‘ + CAST(@Budget AS VARCHAR(100))
+ ‘ WHERE Identifier = ‘ + CAST(@WPID AS VARCHAR(10)) EXEC @SQL Hopefully someone will chime in soon with the link to the famous web page where all the pitfalls of dynamic SQL are explained … … if you’ve ever heard the term ‘SQL injection attack’ – well, this is exactly the kind of syntax that can allow an attack of that type. So be careful what you do with it, and where. (By the way, if the @FY and @WPID parameters of the procedure are both declared as numeric types, then you needn’t worry about the injection bit too much.)

---

Read this to know more about Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html Also make sure the table is normalised
http://www.datamodel.org/NormalizationRules.html Madhivanan Failing to plan is Planning to fail

---

I second normalization suggestion by Madhivanan. If the table was normalized you wouldn’t have the problem you have now.

---

Thanks everyone for the help!

---

don’t forget to add leading zeroes in the column. you can do that by appending ‘0’ and using the LEFT function (ie. LEFT(‘0’ + CAST(1 AS VARCHAR),2)) May the Almighty God bless us all!
www.empoweredinformation.com

---

]]>