Problem in printing the contents of a dynamic quer

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 2005 Forums

Transact-SQL (2005)

Problem in printing the contents of a dynamic quer

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2010-06-25 : 12:06:01

Hi,
I am trying to figure out a dynamic sql query content.
The code is as follows:
DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT t2.tID
, t1.ColName
, t2.Txt
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

PRINT @query

I need to know the contents of @query before executing the @query.

However the PRINT does not give me anything.

I would appreciate any help on this. Thanks.

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 13:25:20

quote:
Originally posted by gamaz

Hi,
I am trying to figure out a dynamic sql query content.
The code is as follows:
DECLARE @cols NVARCHAR(2000)=''
DECLARE @query NVARCHAR(4000)=''
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT t2.tID
, t1.ColName
, t2.Txt
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

PRINT @query

I need to know the contents of @query before executing the @query.

However the PRINT does not give me anything.

I would appreciate any help on this. Thanks.

Check the red part marked above.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-25 : 14:11:51

The @query is null because @cols is null because you never assigned a value to @cols.

CODO ERGO SUM

gamaz
Posting Yak Master

104 Posts

Posted - 2010-06-25 : 14:23:39

Thanks for the help Idera. I appreciate it.

Sachin.Nand

2937 Posts

Posted - 2010-06-26 : 08:06:03

quote:
Originally posted by gamaz

Thanks for the help Idera. I appreciate it.

Welcome

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources