Question about "Execute"
-
I have a parameter @sExecute that contains Dynamic SELECT query like:
Set @sExecute = 'SELECT * FROM MyTable WHERE User='+@User+'...'
EXECUTE (@sExecute)
How do I check if the parameter retruns me no results from the table "MyTable"?
Thanks
Sharon
-
Two ways.
1.
Create a temp table. Then insert the results of your exec statement.
EG.
declare @results table(x int, y int)
insert into @results(x, y)
exec('select col1, col2 from myTable where 1=2')
Silly example but you get the idea
2.
Probably better. Look up sp_executesql. You effective create a mini stored proc with input and output variables. This is safer because it helps insulate you from SQL injection attacks and you won't have trouble with quotes when concatenating strings, etc.
sp_executesql has plenty of examples in SQL Books Online. The syntax may look a little funky, but it is much better than exec once you get used to it.
-
Something like this might help...
DECLARE @sql NVARCHAR(1000)
DECLARE @outsidecount INT
SET @sql = N'SELECT @insidecount = COUNT(1) FROM TableA'
EXECUTE sp_executesql @sql, N'@insidecount INT OUTPUT', @outsidecount OUTPUT
SELECT @outsidecount
The value for @insidecount is assigned to @outsidecount, you can then evaluate @outsidecount
-
sorry I forgot to mention...I use this this on SQL Server 2000, I'm not if it will work on 2005
-
sp_executesql is certainly the cleaner way to go - it will work on SQL 2000 & 2005.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply