May 18, 2006 at 9:08 am
How can you assign a value to a variable when using an Execute statement to run your query?
See below:
DECLARE @x INT
DECLARE @strSQL VARCHAR (2000)
DECLARE @dbname VARCHAR (50)
SET @dbname = 'A40001_1'
SET @strSQL = 'SELECT MAX(Key_ID) FROM ' + @dbname + '.dbo.TableABC'
EXECUTE @x = @strSQL <<< Thus statement doesn't work, but how can you get the result of the sql stmt into this variable.
May 18, 2006 at 9:34 am
use sp_executesql instead
DECLARE @x INT
DECLARE @strSQL VARCHAR (2000)
DECLARE @dbname VARCHAR (50)
SET @dbname = 'A40001_1'
SET @strSQL = 'SELECT @x = MAX(Key_ID) FROM ' + @dbname + '.dbo.TableABC'
EXECUTE sp_executesql @strSQL, N'@x int OUTPUT', @x OUTPUT
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2006 at 10:51 am
That's great! Can that be used to return 2 values? If so, how's the syntax for that?
DECLARE @x INT
DECLARE @y INT
DECLARE @strSQL VARCHAR (2000)
DECLARE @dbname VARCHAR (50)
SET @dbname = 'A40001_1'
SET @strSQL = 'SELECT @x = Key_ID, @y = field1 FROM ' + @dbname + '.dbo.TableABC' << assume this returns one record
EXECUTE sp_executesql @strSQL, N'@x int OUTPUT', @x OUTPUT << can this assign both @x and @y?
May 18, 2006 at 11:02 am
Yes it can have any number of input and output paramters
EXECUTE sp_executesql @strSQL, N'@x int OUTPUT,@y int OUTPUT', @x OUTPUT, @y OUTPUT
Far away is close at hand in the images of elsewhere.
Anon.
May 19, 2006 at 10:38 am
That's great. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply