EXEC Error In Dynamic Qry - 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)

EXEC Error In Dynamic Qry

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-14 : 00:39:04

Dear All,

In my SP,I have a loop Like

While @count<= @Cnt
Begin
Select @Catg_Code = Category From @Catg
Where Sno = @Count
Select @Qry = @Qry+ ' Select ' + ''''+@Catg_Code +''''+ ' Union all '
Select @Count = @Count+1
End

Select @Qry = SUBSTRING ( @Qry , 1 , len(@Qry)-9 )
Exec @Qry

When I execute this loop I have Get The following error msg.

Server: Msg 203, Level 16, State 2, Procedure Pay_Get_Category_rpt, Line 46
The name ' Select 'CKDR ' Union all Select 'DWRK ' Union all Select 'MNGT ' Union all Select 'MWRK ' Union all Select 'STAFF' Union all Select 'TRMEC' Union all Select 'TRTLR' ' is not a valid identifier.

Any one could u please tell me How can i rectify this error..?
Thanks and regards Krishnakumar.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-14 : 00:41:17

But in Query Analyser The Staement
"Select 'CKDR ' Union all Select 'DWRK ' Union all Select 'MNGT ' Union all Select 'MWRK ' Union all Select 'STAFF' Union all Select 'TRMEC' Union all Select 'TRTLR' "

Is Working Fine

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 00:56:14

[code]Exec (@Qry)[/code]

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-09-14 : 00:57:07

Thanks Khtan

It's Working

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 02:08:16

How about removing Loop ??


Select @Qry = @Qry + 'Select  '  + '''' + Category + '''' + ' Union All ' From @Catg

Select @Qry = LEft(@Qry,len(@Qry)-len(' Union All '))

Exec(@Qry)

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources