Solved

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

Posted on 2014-11-11
11
234 Views
Last Modified: 2014-11-11
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
0
Comment
Question by:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 150 total points
ID: 40434980
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 150 total points
ID: 40434982
According to https://www.drupal.org/node/141051, SQL is a reserved word, so you might want to change it.
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 40434986
That was up top, didn't copy-paste it into the question.  Added to the top.

Declare @sql varchar(1000), @name varchar(1000)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 350 total points
ID: 40435002
Looks like you can't use a variable or it would expect a stored procedure.

Open in new window

0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 350 total points
ID: 40435005
This will work (no variables in the string):
 exec ('TRUNCATE TABLE #id')

Open in new window

0
 
LVL 65

Author Comment

by:Jim Horn
ID: 40435009
>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
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 350 total points
ID: 40435013
No variables at all. The @name need to get out as well.
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 40435019
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
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 350 total points
ID: 40435024
Wait, by the error a space was missing between FROM keyword and table name. Did you check that?
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 350 total points
ID: 40435030
Looks like I didn't see well. FROM is part of the table name, since there's another FROM before.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 0 total points
ID: 40435035
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

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question