exec statement to and assign it to a variable

[FALCONSEYE]

i have the following

declare @myResult int

set @myResult =
exec ('Select Sum(numShipped) as numshipped from ' + @txtTableName

+ ' Where Shipdate = (Select max(shipdate) from myTable)')

i also tried

exec ('Select Sum(numShipped) as numshipped from ' + @txtTableName

+ ' Where Shipdate = (Select max(shipdate) from myTable)') as @myResult

for some reason, the exec statement runs fine by itself. when i try to assign the result of the exec statement to a variable sql throws an error. does anyone know how to set a variable from an exec statement?
thanks for the help in advance..

http://www.jetansolutions.com

 

[SQLDenis]

does this work?

DECLARE @txtTableName VARCHAR(100),
@myResult INT,
@chvSQL NVARCHAR(100)

SELECT @txtTableName = 'SomeTableName'
SELECT @chvSQL = N'SELECT @intTableCount = Sum(numShipped) as numshipped from ' + @txtTableName + ' Where Shipdate = (Select max(shipdate) from myTable)' 

EXEC sp_executesql @chvSQL, N'@myResult INT OUTPUT', @myResult OUTPUT

SELECT @myResult
GO

2 ways
1 with exec and temp table
2 with sp_executesql

are explained here

http://sqlservercode.blogspot.com/2006/03/use-spexecutesql-or-exec-to-get-count.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[FALCONSEYE]

thanks denis but i am getting
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'm'.

(1 row(s) affected)

http://www.jetansolutions.com

 

[SQLDenis]

this runs in pubs

DECLARE @txtTableName VARCHAR(100),
@myResult INT,
@chvSQL NVARCHAR(100)

SELECT @txtTableName = 'authors'
SELECT @chvSQL = N'SELECT @myResult = count(contract)  from ' + @txtTableName + ' Where state = (Select min(state) from publishers)' 

EXEC sp_executesql @chvSQL, N'@myResult INT OUTPUT', @myResult OUTPUT

SELECT @myResult
GO

in your code we need to make 2 changes
try this

DECLARE @txtTableName VARCHAR(100),
@myResult INT,
@chvSQL NVARCHAR(100)

SELECT @txtTableName = 'SomeTableName'
SELECT @chvSQL = N'SELECT @myResult= Sum(numShipped) from ' + @txtTableName + ' Where Shipdate = (Select max(shipdate) from myTable)' 

EXEC sp_executesql @chvSQL, N'@myResult INT OUTPUT', @myResult OUTPUT

SELECT @myResult
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[FALCONSEYE]

thanks denis, but still no luck. i keep getting

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'from'.

(1 row(s) affected)

http://www.jetansolutions.com

 

[vbkris]

thats strange, it worked for me too. can we have your implementation code???

Known is handfull, Unknown is worldfull

 

[gmmastros]

I think that @chvSQL isn't big enough. Try...

@chvSQL NVARCHAR(100[!]0[/!])



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[FALCONSEYE]

changing the @chvSql length to 1000 did the trick. thanks...

http://www.jetansolutions.com