Get dynamic SQL output - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Get dynamic SQL output

Author

Topic

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-23 : 10:19:10

I have scalar function that returns integer which have to be dynamicly executed. I need its return value to work with. I tried something like this but it doesn't work:

DECLARE @tmp INT
EXEC ('set @tmp = dbo.myFunc(...)')

How can I get this value ?

Currently I have following solution:


  CREATE TABLE ##tTAB(Value INT)

  SET @STR = 'INSERT INTO ##tTAB VALUES (dbo.myFunc(''100379736''))'
	   
	   	  
  Exec(@str) 
  set @tmp = (select * from ##tTAB)

Is there anything that allows me to use shorter code like the one I mentioned first ?

www.r-moth.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-23 : 10:39:23

You need to use sp_executesql to make use of output variables:


set nocount on
use pubs
go
create function dbo.fn_junk(@v varchar(10)) returns int as
begin
	return 1
end

go

--EDIT:
--this was just to test my function
--select dbo.fn_junk('tg')

declare @tmp int
	,@cmd nvarchar(500)
	,@params nvarchar(500)

select	@cmd = 'select @tmp = dbo.fn_junk(''tg'')'
	,@params = '@tmp int output'

exec sp_executesql @cmd, @params, @tmp=@tmp output
select @tmp [@tmp]
go

drop function dbo.fn_junk

Be One with the Optimizer
TG

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-24 : 04:33:27

Thx TG

That should do the job.

This @tmp = @tmp output line is awesome. It is good I asked you since this is not documented in sp_executesql help

www.r-moth.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources