• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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
0
Jim Horn
Asked:
Jim Horn
  • 5
  • 4
  • 2
8 Solutions
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now