Issue in Dynamic SQL - 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

General SQL Server Forums

New to SQL Server Programming

Issue in Dynamic SQL

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-02-24 : 05:05:25

I have written this SQl and it is giving me error.
Can anybody pealse help me with it. I am so so bad in dynamic SQl thing.

here is my SQL

DECLARE @sm VARCHAR(10)
DECLARE @sqltext NVARCHAR(MAX)
SET @sm=2082

SET @sqltext='
DECLARE @list VARCHAR(400)
SELECT @list=SUBSTRING((SELECT DISTINCT '',[''+Supervisor+'']'' FROM GORSTRUNEW WHERE sm='+@sm+' FOR XML PATH('''')),2,1000)
SELECT @list

SELECT * FROM
(SELECT SM,Supervisor,Counseors, ROW_NUMBER()OVER( PARTITION BY SM,Supervisor ORDER BY counseors) as RN FROM GORSTRUNEW
WHERE SM='+@sm+'
) P
PIVOT
(
MIN(Counseors) FOR Supervisor IN (@list)
) AS PVT
ORDER BY SM
'

PRINT @sqltext
EXEC (@sqltext)

------------------------------------------------
The answer is always no till than you don't ask.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-24 : 05:21:19

What does PRINT @sqltext return?

N 56°04'39.26"
E 12°55'05.63"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-02-24 : 05:21:48

Done with this thanks.

------------------------------------------------
The answer is always no till than you don't ask.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-02-24 : 05:30:30

@peso sir.. no it was giving error near @list.. i have changed this only and it is working now.

DECLARE @sm VARCHAR(10)
DECLARE @sqltext NVARCHAR(MAX)
SET @sm=2082
DECLARE @list VARCHAR(400)
SELECT @list=SUBSTRING((SELECT DISTINCT ',['+Supervisor+']' FROM GORSTRUNEW WHERE sm=@sm FOR XML PATH('')),2,1000)

SET @sqltext='
SELECT SM,'+@list+' FROM
(SELECT SM,Supervisor,Counseors, ROW_NUMBER()OVER( PARTITION BY SM,Supervisor ORDER BY counseors) as RN FROM GORSTRUNEW
WHERE SM='+@sm+'
) P
PIVOT
(
MIN(Counseors) FOR Supervisor IN ('+@list+')
) AS PVT
ORDER BY SM
'

PRINT @sqltext
EXEC (@sqltext)

------------------------------------------------
The answer is always no till than you don't ask.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources