Dynamic SQL and cursor - 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

Transact-SQL (2000)

Dynamic SQL and cursor

Author

Topic

jwe
Starting Member

13 Posts

Posted - 2007-06-26 : 10:50:47

Hi

I wrote a cursor for the part of the dynamic SQL that is too long for
varchar(8000). It all works fine but I have to put this into a stored
procedure.
When I run the code below with the print statements it give me what I want ie.

CREATE VIEW DBO.auditEVENT_Deleted
AS
SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM (SELECT PrimaryKey
,
Max(Case ColumnName WHEN [EventID] THEN LastValue ELSE '' END) AS [EventID]
Max(Case ColumnName WHEN [EntryDate] THEN LastValue ELSE '' END) AS
[EntryDate]
Max(Case ColumnName WHEN [NotifiedDate] THEN LastValue ELSE '' END) AS
[NotifiedDate]
Max(Case ColumnName WHEN [ArrivedDate] THEN LastValue ELSE '' END) AS
[ArrivedDate]
Max(Case ColumnName WHEN [StartedDate] THEN LastValue ELSE '' END) AS
[StartedDate]

Etc.

I want to execute @SQL1+@SQL2 at the end but because I am using a cursor it
comes out like this:

CREATE VIEW DBO.auditEVENT_Deleted
AS
SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM (SELECT PrimaryKey
, Max(Case ColumnName WHEN [EventID] THEN LastValue ELSE '' END) AS [EventID]
CREATE VIEW DBO.auditEVENT_Deleted
AS
SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM (SELECT PrimaryKey
, Max(Case ColumnName WHEN [EntryDate] THEN LastValue ELSE '' END) AS
[EntryDate]
CREATE VIEW DBO.auditEVENT_Deleted
AS
SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM (SELECT PrimaryKey
, Max(Case ColumnName WHEN [NotifiedDate] THEN LastValue ELSE '' END) AS
[NotifiedDate]
CREATE VIEW DBO.auditEVENT_Deleted
AS
SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM (SELECT PrimaryKey
, Max(Case ColumnName WHEN [ArrivedDate] THEN LastValue ELSE '' END) AS
[ArrivedDate]
CREATE VIEW DBO.auditEVENT_Deleted
AS
SELECT Col.*, CM.Created, CM.Modified, DD.Deleted FROM (SELECT PrimaryKey
, Max(Case ColumnName WHEN [StartedDate] THEN LastValue ELSE '' END) AS
[StartedDate]

Etc.

How can I achieve the result I get with the print statements when using exec
SQL?

---------------- code ---------------------

SET NOCOUNT ON
DECLARE
@SQL1 NVARCHAR (1000),
@SQL2 NVARCHAR (4000),
@column SYSNAME

SET @SQL1 = 'CREATE VIEW ' + 'DBO' + '.audit' + 'EVENT' + '_Deleted' +
Char(13) + Char(10)
+ 'AS ' + Char(13) + Char(10) + 'SELECT Col.*, CM.Created,
CM.Modified, DD.Deleted FROM '
+ ' (SELECT PrimaryKey' + Char(13) + Char(10) + ','

PRINT @SQL1

declare @columnvalues table
(
id int,
string SYSNAME
)

insert into @columnvalues

select distinct c.colid, c.name from sysobjects as t
join syscolumns as c
on t.id = c.id
join systypes as ty
on ty.usertype = c.usertype
join systypes st
on ty.type= st.type
where t.name = 'Event'
AND c.name NOT IN ('created', 'modified','RowVersion')
AND c.iscomputed = 0
AND st.name IN ('tinyint', 'smallint', 'int', 'money', 'smallmoney',
'decimal', 'bigint', 'datetime',
'smalldateteime', 'numeric', 'varchar', 'nvarchar', 'char', 'nchar',
'bit')

order by c.colid

DECLARE CURS CURSOR STATIC LOCAL FOR
select string from @columnvalues

OPEN CURS

WHILE 1 = 1
BEGIN
FETCH CURS INTO @column
IF @@FETCH_STATUS <> 0
BREAK

SELECT @SQL2 =
' Max(Case ColumnName WHEN ' + quotename(@column) + ' THEN LastValue ELSE
'''' END) AS ' + quotename(@column)

PRINT @SQL2

END
DEALLOCATE CURS

SET NOCOUNT OFF

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 10:59:22

Not sure what you are trying to do
Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources