set @var = exec @sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

set @var = exec @sql

Author

Topic

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-06 : 09:20:40

Hello It's possible to assign at variable a value returned from exec @sql???

es @Heat = exec (@sql)

Thank's

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-06 : 09:30:36

Refer this

http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-06 : 09:38:20

I think you need to do some thing like this. Declaring the variable inside the dynamic sql is not available outside the batch.

go
create table #temp (Heat varchar(30))
declare @sql varchar(100)
declare @Heat varchar(30)
select @sql = 'insert into #temp select top 1 name from sysobjects'
exec (@sql)
select @Heat = Heat from #temp
select @Heat
drop table #temp

Sanjeev Shrestha
12/17/1971

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-06 : 10:23:19

make use of sp_executesql:

USE NORTHWIND
DECLARE @A VARCHAR(100)
EXEC SP_EXECUTESQL N'SELECT @A = PRODUCTNAME FROM PRODUCTS WHERE PRODUCTID = 1', N'@A VARCHAR(100) OUTPUT', @A OUTPUT
PRINT @A

Harsh Athalye
India.
"Nothing is Impossible"

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-06 : 11:01:43

quote:
Originally posted by sanjnep

I think you need to do some thing like this. Declaring the variable inside the dynamic sql is not available outside the batch.

go
create table #temp (Heat varchar(30))
declare @sql varchar(100)
declare @Heat varchar(30)
select @sql = 'insert into #temp select top 1 name from sysobjects'
exec (@sql)
select @Heat = Heat from #temp
select @Heat
drop table #temp

Sanjeev Shrestha
12/17/1971

That won't work. Did you test it? #temp will be in a different scope.

Can you tell us how @sql is built and why it needs to be dynamic?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-06 : 12:17:34

quote:
Originally posted by X002548

quote:
Originally posted by sanjnep

I think you need to do some thing like this. Declaring the variable inside the dynamic sql is not available outside the batch.

go
create table #temp (Heat varchar(30))
declare @sql varchar(100)
declare @Heat varchar(30)
select @sql = 'insert into #temp select top 1 name from sysobjects'
exec (@sql)
select @Heat = Heat from #temp
select @Heat
drop table #temp

Sanjeev Shrestha
12/17/1971

That won't work. Did you test it? #temp will be in a different scope.

Can you tell us how @sql is built and why it needs to be dynamic?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

It worked when I tested it.

The #temp table is created before the EXEC so it is in scope.

CODO ERGO SUM

markd89
Starting Member

8 Posts

Posted - 2007-02-22 : 20:12:47

Hi all,

I searched and found this thread as I am also trying to assign a variable to the output of my exec.

I have been using exec for some time with success to build dynamic queries and execute them. For example, here I build a query and then execute it. Even the field name I am checking (@c2field) is dynamically popped into the query.

.................................
..leaving out declarations...

select @logrun='select cast('+@c2field+' as float) from contact2 where userdef12='+''''+cast(@contractnumber as varchar(6))+'''and isnumeric(' +@c2field+')=1'
exec(@logrun)
.................................

Now I need to assign the result of the query to a variable @existingamount which is a float. I read Nigel's link and tried several variations without any luck.

The following comlains about incorrect syntax near '+'. It seems to not like the concatenation needed for me to dynamically specify the field (@c2field).

exec sp_executesql N'
select cast('+@c2field+' as float) from contact2 where userdef12='+''''+cast(@contractnumber as varchar(6))+'''and isnumeric(' +@c2field+')=1',N'@existingamount float output',@existingamount output

Any help much appreciated!

Mark

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 21:09:16

Don't build your query inside sp_executesql call. Build it in some nvarchar variable and use that variable in the sp_executesql call.

BTW, where are you assigning to @existingamount variable? It won't get assigned just by declaring it as OUTPUT. You have to assign the result to this variable.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

markd89
Starting Member

8 Posts

Posted - 2007-02-22 : 21:12:52

quote:
Originally posted by harsh_athalye

Don't build your query inside sp_executesql call. Build it in some nvarchar variable and use that variable in the sp_executesql call.

BTW, where are you assigning to @existingamount variable? It won't get assigned just by declaring it as OUTPUT. You have to assign the result to this variable.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

OK, thanks, so I have the statement to execute here in the variable @logrun

select @logrun='select cast('+@c2field+' as float) from contact2 where userdef12='+''''+cast(@contractnumber as varchar(6))+'''and isnumeric(' +@c2field+')=1'

How would I then use that within sp_executesql to assign the result to @existingamount?

Thanks again,
Mark

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 21:31:05

Please see my first reply on this thread on 09/06/2006 : 10:23:19. I shown there how can you assign to output variable using sp_executesql.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

markd89
Starting Member

8 Posts

Posted - 2007-02-22 : 21:44:07

quote:
Originally posted by harsh_athalye

Please see my first reply on this thread on 09/06/2006 : 10:23:19. I shown there how can you assign to output variable using sp_executesql.

Right, I saw that and (somewhat) understand. So then the question is how to combine assigning the output variable with the query I have built inside my @logrun variable. This seems to be where I am having trouble.

You had:

EXEC SP_EXECUTESQL N'SELECT @A = PRODUCTNAME FROM PRODUCTS WHERE PRODUCTID = 1', N'@A VARCHAR(100) OUTPUT', @A OUTPUT

So I'm thinking I would have something like:

---build query
select @logrun='select cast('+@c2field+' as float) from contact2 where userdef12='+''''+cast(@contractnumber as varchar(6))+'''and isnumeric(' +@c2field+')=1'

---run query and assign output to @existingamount
exec SP EXECUTESQL N'@LOGRUN',N'@existingamount float output',@existingamount output

I still think I am missing something in the syntax. Am I getting closer?

Thanks for all the help
--Mark

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-22 : 22:25:32

read this: http://www.sommarskog.se/dynamic_sql.html

www.elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 22:38:29

[code]select @logrun= N'select @existingamount = cast('+@c2field+' as float)
from contact2
where userdef12='+''''+cast(@contractnumber as varchar(6))+''' and isnumeric(' +@c2field+')=1'

exec SP EXECUTESQL @logrun, N'@existingamount float output', @existingamount output[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-23 : 00:58:02

also you are missing a crucial underscore between SP and EXECUTESQL

www.elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-23 : 01:10:02

quote:
Originally posted by jezemine

also you are missing a crucial underscore between SP and EXECUTESQL

www.elsasoft.org

Goddamn, copy & paste !!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

markd89
Starting Member

8 Posts

Posted - 2007-02-23 : 03:15:20

Much thanks to Harsh and Jezemine!

Harsh, I'm starting to understand this after reading your code over and over ;-)

Jezemine, thanks for sending the reference. It looks very helpful.

Take care,
Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources