Dynamic SQL code using cursors

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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL code using cursors

Author

Topic

Bjcascone
Starting Member

37 Posts

Posted - 2010-12-28 : 16:16:55

I am trying to write a piece of code to do the following: Dynamically build a set of scripts that populate the following code

'Begin
If Exists (Select 1 From '+@Dbname+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '''+@TableName+'_Load'')

INSERT INTO '+@LLDB+'.dbo.StratificationLoadTables

Select
'+@AuditId+',
'''+@TableName+'_Load'',
'''+@StratDate+''',
'''+@StratAmt+''',
Year('+@StratDate+') as YY,
Month('+@StratDate+') as MM,
Day('+@StratDate+') as DD,
Sum('+@StratAmt+') as Volume,
Sum(1) as Total
FROM '+@DBName+'.dbo.'+@TableName+'_Load
Where YEAR('+@StratDate+') = 2010
Group By Year('+@StratDate+'), Month('+@StratDate+'),Day('+@StratDate+')
Order By Year('+@StratDate+'), Month('+@StratDate+'),Day('+@StratDate+')

End'

Basically i am looking for this script to populate the variables based on what is in each line of the cursor and repeat itself. this alleviates me having to write this code out for each table that needs to have this code ran against it.

I currently have the following but i have reached a road block (it might just be from staring at it for too long) any help will be appreciated (new approaches are welcomed as well, since i am sure that this is not the best way to go about this.

SP -

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Build_DailyStratProc] (@ContainerName as Varchar(100) ) AS

Declare @DBName as Varchar(100)
Declare @Sql as Varchar(8000)
Declare @FormName as Varchar(50)
Declare @AuditID as VARCHAR(50)
Declare @StratDate as Varchar(50)
Declare @StratAmt as Varchar(50)
Declare @TableName as Varchar(50)
DECLARE @LLDB AS VARCHAR(50)
DECLARE @SQL2 AS VARCHAR(8000)
DECLARE @SQL3 AS VARCHAR(8000)
Declare @StartTime as Varchar(100)
Declare @EndTime as Varchar(100)
Declare @StepNumber as varchar(5)
Declare @StepName as Varchar(100)
Declare @SQL4 as Varchar(8000)

Set @StepNumber = '3'
Set @StepName = 'Daily Stratification on _Load tables'
Set @StartTime = GetDate()
Set @TableName = 'N/A'
Set @FormName = 'CCA_frmWizCentralStratify'
SET @LLDB = ''+@ContainerName+'LoadLogger'

Set @SQL4 = '

declare Driver Cursor for
Select Column2 as DBName, [Text] as TableName, Column3 as AuditID, Column4 as StratDate, Column5 as StratAmt
From '+@ContainerName+'.dbo.__GlobalWizProcesses
Where FormName = '''+@FormName+'''
ORDER BY Column2,[Text]'

--EXEC sp_executesql @SQL4

Print @SQL4

SET @SQL3 = '

Insert into '''+@ContainerName+'LoadLogger''.dbo.StratificationLoadTablesHist
Select * From '''+@ContainerName+'LoadLogger''.dbo.StratificationLoadTables
Truncate Table '''+@ContainerName+'LoadLogger''.dbo.StratificationLoadTables
'
Print @SQL3

Open Driver
Fetch Next From Driver into @DBName, @TableName, @AuditId, @StratDate, @StratAmt
While (@@FETCH_STATUS = 0)
BEGIN

Set @Sql = 'Begin
If Exists (Select 1 From '+@Dbname+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '''+@TableName+'_Load'')

INSERT INTO '+@LLDB+'.dbo.StratificationLoadTables

Select
'+@AuditId+',
'''+@TableName+'_Load'',
'''+@StratDate+''',
'''+@StratAmt+''',
Year('+@StratDate+') as YY,
Month('+@StratDate+') as MM,
Day('+@StratDate+') as DD,
Sum('+@StratAmt+') as Volume,
Sum(1) as Total
FROM '+@DBName+'.dbo.'+@TableName+'_Load
Where YEAR('+@StratDate+') = 2010
Group By Year('+@StratDate+'), Month('+@StratDate+'),Day('+@StratDate+')
Order By Year('+@StratDate+'), Month('+@StratDate+'),Day('+@StratDate+')

End'

Print @SQL
Fetch Next From Driver into @DBName, @TableName, @AuditId, @StratDate, @StratAmt

END

CLOSE DRIVER
DEALLOCATE DRIVER

Set @EndTime = GETDATE()

SET @SQL2 = '
Insert into '''+@ContainerName+'LoadLogger''.dbo.StepDriverTable
(
[StepNumber],
[StepName],
[StartTime],
[EndTime],
[TableName]
)

Select '+@StepNumber+' as StepNumber, '+@StepName+' as StepName, '+@StartTime+' as StartTime, '+@EndTime+' as EndTime, '+@TableName+' as TableName'
Print @SQL2

GO

I think my problem is some where in the multiple @SQL portions but i am having trouble figuring out where. any help is appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources