Parsing to script output - 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 2000 Forums

SQL Server Development (2000)

Parsing to script output

Author

Topic

richie_b_97
Starting Member

8 Posts

Posted - 2008-06-12 : 06:45:21

I have a script that uses variables to generate SQL

e.g.

--Declare counter
declare @countr as int
set @countr = 1
declare @openMonth as int
set @openMonth = 6

declare @sqlBH as nvarchar (1000)
set @sqlBH = ''
declare @sqlBHFull as nvarchar (1000)
set @sqlBHFull = ''
WHILE @countr < 12
	BEGIN
		-- BH_AllocEffortCost1 = acCost1,BH_AllocEffortCost2 = acCost2,  ... etc...
		set @sqlBHFull = @sqlBHFull+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)+','
		IF @countr >= @openMonth
			BEGIN
				set @sqlBH = @sqlBH+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)+','
			END
		set @countr = @countr + 1
	END
--for December, minus the trailing comma
set @sqlBH = @sqlBH+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)
set @sqlBHFull = @sqlBHFull+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)

and the created values are used later in Update statements

UPDATE BudgetHeader 
SET @sqlBH

My question is, for debugging purposes how do I generate a final script where the variables have been evaluated and put into the correct place?

I want to see a final script which instead of having

UPDATE BudgetHeader 
SET @sqlBH

has

UPDATE BudgetHeader 
SET BH_AllocEffortCost1 = acCost1,BH_AllocEffortCost2 = acCost2, ...

Is there some software that will do this for me (preferably freeware) - all I have is MS SQL Server Utils (e.g. Enterprise Manager & Query Analyzer)?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-12 : 07:00:02

Print @sqlBH - Will give you what you need.

richie_b_97
Starting Member

8 Posts

Posted - 2008-06-12 : 07:53:42

No, this will just output the value of @sqlBH. I need the output to be a complete script with variables expanded into whatever they have evaluated to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 08:11:21

[code]--Declare counter
declare @countr as int
set @countr = 1
declare @openMonth as int
set @openMonth = 6

declare @sqlBH as nvarchar (1000)
set @sqlBH = ''
declare @sqlBHFull as nvarchar (1000)
set @sqlBHFull = ''
WHILE @countr < 12
BEGIN
-- BH_AllocEffortCost1 = acCost1,BH_AllocEffortCost2 = acCost2, ... etc...
set @sqlBHFull = @sqlBHFull+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)+','
IF @countr >= @openMonth
BEGIN
set @sqlBH = @sqlBH+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)+','
END
set @countr = @countr + 1
END
--for December, minus the trailing comma
set @sqlBH = @sqlBH+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)
set @sqlBHFull = @sqlBHFull+'BH_AllocEffortCost'+cast(@countr as nvarchar)+'=acCost'+cast(@countr as nvarchar)

declare @Sql varchar(8000)

SET @Sql='UPDATE BudgetHeader
SET'+ @sqlBH
PRINT (@Sql)[/code]

richie_b_97
Starting Member

8 Posts

Posted - 2008-06-13 : 03:54:54

The example I gave above is about 1/8 of a much larger script. What I'm after is a way of printing my entire script with the variables evaluated. The form of the script will change depending on what the value of the variables are.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources