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?
Thanks in advance.
Jim
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;
Thanks in advance.
Jim
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>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.
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 + ')'SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Declare @sql varchar(1000), @name varchar(1000)