bicky1980 (1/2/2013)
I am currently attempting to do this in the following manner:
declare @sql nvarchar(1000),@totalrecs int
set @sql='insert into source select * from destination where age>49
select @totalrecs=count(*) from destination
set @totalrecs=2000-@totalrecs
insert into destination
select top '+CONVERT(NVARCHAR(10),@TOTALRECS)+' * from source
where age<50'
Print @sql
Exec(@sql)
Nothing is returned to the screen (from the print command) and the query is not executed - Anybody have an idea why and maybe suggest a better way to perform this
Thanks
One other item of note, @totalrecs is not going to be available as a variable inside your dynamic SQL. A batch executed using EXEC (or sys.sp_executesql) is in a different scope when it comes to variables than the scope from which it is called. You can see what I mean by running this:
DECLARE @sql NVARCHAR(MAX),
@totalrecs INT;
SET @sql = 'SELECT @totalrecs AS [@totalrecs];';
PRINT @sql;
EXEC(@sql);
You'll receive this output:
SELECT @totalrecs AS [@totalrecs];
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@totalrecs".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato