SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 set @var = exec @sql
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/06/2006 :  09:20:40  Show Profile
Hello It's possible to assign at variable a value returned from exec @sql???


es @Heat = exec (@sql)

Thank's

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/06/2006 :  09:30:36  Show Profile  Send madhivanan a Yahoo! Message
Refer this

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


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sanjnep
Posting Yak Master

USA
191 Posts

Posted - 09/06/2006 :  09:38:20  Show Profile  Visit sanjnep's Homepage
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/06/2006 :  10:23:19  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/06/2006 :  11:01:43  Show Profile
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/06/2006 :  12:17:34  Show Profile
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
Go to Top of Page

markd89
Starting Member

8 Posts

Posted - 02/22/2007 :  20:12:47  Show Profile
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

Edited by - markd89 on 02/22/2007 20:13:41
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/22/2007 :  21:09:16  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

markd89
Starting Member

8 Posts

Posted - 02/22/2007 :  21:12:52  Show Profile
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/22/2007 :  21:31:05  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

markd89
Starting Member

8 Posts

Posted - 02/22/2007 :  21:44:07  Show Profile
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


Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/22/2007 :  22:25:32  Show Profile  Visit jezemine's Homepage
read this: http://www.sommarskog.se/dynamic_sql.html


www.elsasoft.org
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/22/2007 :  22:38:29  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Edited by - harsh_athalye on 02/22/2007 22:39:00
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/23/2007 :  00:58:02  Show Profile  Visit jezemine's Homepage
also you are missing a crucial underscore between SP and EXECUTESQL


www.elsasoft.org
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/23/2007 :  01:10:02  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

markd89
Starting Member

8 Posts

Posted - 02/23/2007 :  03:15:20  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000