Dynamic SQL Syntax Error - 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 2005 Forums

Transact-SQL (2005)

Dynamic SQL Syntax Error

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-02-24 : 18:40:57

Hi guys, hoping someone can help me with this. The following code:

WHILE EXISTS(SELECT ViewName from #MAKE)
BEGIN
SELECT  @viewname = ViewName, @tablename = 'tb_' + RIGHT(ViewName, (LEN(ViewName)-3)
FROM #MAKE

generates the error, "Incorrect syntax near the keyword FROM."

All variables are declared, and it works without the "@tablename" part - so how do I get it to set @tablename?

Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-24 : 19:15:41

You need to use dynamic SQL for that. So you'll need to build the entire query into a variable and then EXEC that variable. Here's a simple example:

DECLARE @s varchar(50), @t varchar(10)

SET @t = 'SomeTable'

SET @s = 'SELECT * FROM ' + @t

EXEC(@s)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Sachin.Nand

2937 Posts

Posted - 2010-02-24 : 21:53:25

try this


declare @strsql varchar(500)
WHILE EXISTS(SELECT ViewName from #MAKE)
BEGIN
set @strsql ='SELECT' + @viewname +'= ViewName,'+ @tablename = 'tb_ + RIGHT(ViewName, (LEN(ViewName)-3) FROM #MAKE'
exec sp_executesql @strsql

PBUH

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 01:28:38

The error message is because you are missing a parenthesis

RIGHT(ViewName, (LEN(ViewName)-3))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:32:59

you keep on deleting records from #MAKE inside loop? or else when does loop end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-02-25 : 09:35:21

Thanks to all for the in-depth analysis, but Kristen, you were right - I added the final parenthesis and away it went. D'oh!

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-02-25 : 09:36:20

visakh16 - yes, I delete the records. This is only a snippet of the part that isn't working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:39:57

quote:
Originally posted by JeffK627

visakh16 - yes, I delete the records. This is only a snippet of the part that isn't working.

Ok. I was a bit perplexed seeing your loop condition. Have you thought about set based solution before going for the loop?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources