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 2000 Forums
 SQL Server Development (2000)
 SPROC for dynamic column???

Author  Topic 

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-04-28 : 11:26:18
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!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-28 : 11:55:29
1 proc. dynamic SQL.
Go to Top of Page

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-28 : 12:32:38
CREATE PROCEDURE [dbo].[usp_UpdateBudgetUpdate]
(
@Budget decimal (18,3)
, @WPID nvarchar(100)
, @FY nvarchar(4)
)
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(1000)

SET @sql = 'UPDATE Budget SET WP_Budget.' + @FY + ' = ' + @Budget + ' WHERE Identifier = ' + @WPID

EXEC (@sql)
GO

Hearty head pats
Go to Top of Page

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-07 : 23:38:35
Thanks Bex for the help!
Go to Top of Page
   

- Advertisement -