exec @sql thinks @sql is a stored procedure, need it to execute as a query

Hi All

Using the below SQL 2008R2 code and a good #main table where column 'name' = table names, I'm trying to execute SQL statements for each #main.name.  

When I run the below code, it returns the error in the screen shot where it thinks @sql is a stored procedure name, and not a straight query.   What's the fix?

Declare @sql varchar(1000), @name varchar(1000) 

CREATE TABLE #id (id nvarchar(18) COLLATE SQL_Latin1_General_CP1_CI_AS) 

DECLARE cur_del CURSOR FOR 
SELECT name FROM #main

OPEN cur_del
FETCH NEXT FROM cur_del into @name

WHILE @@FETCH_STATUS = 0
   begin
   
   SET @sql = 'DELETE FROM ' + @name + ' WHERE id in (SELECT TOP 2 id FROM ' + @name + ')'
   exec (@sql)
   
   FETCH NEXT FROM cur_del INTO @name      -- Get the next vendor.
   
   end
   
CLOSE cur_del;
DEALLOCATE cur_del;

Open in new window

Error message
Thanks in advance.
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Don't know if this is the problem, but I can't see a declare statement for @SQL.

Maybe:

 DECLARE @sql VARCHAR(max)

Open in new window

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
According to https://www.drupal.org/node/141051, SQL is a reserved word, so you might want to change it.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
That was up top, didn't copy-paste it into the question.  Added to the top.

Declare @sql varchar(1000), @name varchar(1000)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like you can't use a variable or it would expect a stored procedure.

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This will work (no variables in the string):
 exec ('TRUNCATE TABLE #id')

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
>Looks like you can't use a variable or it would expect a stored procedure.
Didn't like that either, as I changed the code to below and it returns an 'Incorrect syntax near 'DELETE FROM ' message.
exec 'DELETE FROM ' + @name + ' WHERE id in (SELECT TOP 2 id FROM ' + @name + ')'

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No variables at all. The @name need to get out as well.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
That's not going to meet my needs.  

I case anyone's wondering, I have an SSIS package with 20+ tables where I'm handling inserts and updates.  This is a test script so that I run the package once, delete 2 rows from each target table, update 3 rows from each target table, run it again, and verify that the next package run had 2 inserts and 3 updates.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Wait, by the error a space was missing between FROM keyword and table name. Did you check that?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like I didn't see well. FROM is part of the table name, since there's another FROM before.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Figured it out.  nvarchar instead of varchar, and exec sp_executesql

Declare @sql nvarchar(1000), @name varchar(1000) 

SELECT @sql = N'DELETE FROM ' + @name + ' WHERE id in (SELECT TOP 2 id FROM ' + @name + ')'
exec sp_executesql @SQL

Open in new window


>No variables at all. The @name need to get out as well.
Correct, exec @sql implies an object name, and exec 'xxx' implies 'xxx' is a query into itself, and not concatenated like 'xxx' + @name.

I'll split points across everyone that helped.

Thanks.
Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.