gaining acces in sql to returnvalue of sp_executesql - is it possible and how?
-
Below you find a script i have created which dynamiccaly puts a sql-query together dependant on some predefined values.
IT works just fine, but I cant figure out how to acces the content of the return value of the following sentence EXEC sp_executesql @together
I need to acces inline the value....... is it possible and how?
Declare @StartStatement nvarchar(1500)
Declare @statementAfd nvarchar(400)
Declare @statementBaerer nvarchar(400)
Declare @statementFormaal nvarchar(400)
Declare @SlutStatement nvarchar(500)
Declare @together nvarchar(3500)
declare @transdateFrom varchar(6)
declare @transdateTo varchar(6)
declare @ledgeraccount varchar(8)
declare @afd varchar(12)
declare @baerer varchar(12)
declare @formaal varchar(12)
declare @regnskab varchar(30)
set @transdateFrom ='200301'
set @transdateTo = '200309'
Set @afd ='MrLovaLova'
set @baerer ='MrLovaLova'
set @formaal = 'MrLovaLova'
set @ledgeraccount = '40111'
set @regnskab ='DMO'
if (@afd = 'MrLovaLova')
begin
set @statementAfd = ''
end
if (@afd <> 'MrLovaLova')
begin
set @statementAfd = 'AND (LEDGERTRANS.DIMENSION =''' + @afd + ''')'
end
if (@baerer = 'MrLovaLova')
begin
set @statementBaerer = ''
end
if (@baerer <> 'MrLovaLova')
begin
set @statementBaerer = 'AND(Ledgertrans.dimension2_ = '''+@baerer+''')'
end
if (@formaal = 'MrLovaLova')
begin
set @statementFormaal = ''
end
if (@formaal <> 'MrLovaLova')
begin
set @statementFormaal = 'and (Ledgertrans.dimension3_ = '''+@formaal+''')'
end
Set @together =''
--sæt start
Set @StartStatement = N'SELECT
SUM(LEDGERTRANS.AMOUNTMST)
FROM
LEDGERTABLE
INNER JOIN
LEDGERTRANS
ON
LEDGERTABLE.ACCOUNTNUM = LEDGERTRANS.ACCOUNTNUM
and
ledgertable.dataareaid = ledgertrans.dataareaid
WHERE
(LEDGERTRANS.Accountnum = cast('''+@ledgeraccount+'''as int))
AND
( CONVERT(varchar(6), LEDGERTRANS.TRANSDATE, 112) between '''+@transdateFrom+''' and '''+@transdateTo+''')
and
(Ledgertrans.Periodcode = 1)
and
(ledgertrans.dataareaid = '''+@regnskab+''')
'
Set @samlet = @StartStatement + @statementAfd + @statementBaerer + @statementFormaal + @SlutStatement
Declare @test-2 float
EXEC sp_executesql @together
--return @test-2
--print @samlet
--print @test-2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Hi,
You'll need to add a parameter definition to the sp_executesql, as well as the variable that should be assigned the return value. A small example:
declare @stmt nvarchar(4000), @result int
select @stmt = N'select @sum = (4 + 20)'
exec sp_executesql @stmt, N'@sum int out', @sum=@result out
select @result as Result
In your query, that would mean adding a return variable to the beginning of the statement:
Set @StartStatement = N'SELECT @the_sum =
SUM(LEDGERTRANS.AMOUNTMST)
....
When calling sp_executesql, add the parameter definition as well as the variable:
exec sp_executesql @StartStatement, N'@the_sum float out', @test_float = @the_sum out
select @test_float
I hope this helps. More accurate descriptions of how to use parameters with sp_executesql can be found in BOL.
I got a bit confused as to which statement you wanted to execute - from the example you provided it seems like you're executing an empty string (@together)? I guess the @samlet-statement (danish, right?) should be replaced by a @together-assignment.
-
thanks a million times.
you are correct about putting a together value. i will try i tfirst thing at work tomorrow. I cant wait to see if it working.
you are the man
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply