set to local var the return string on a exec(@sql) dynamic sql execute call?

Question

Post reply

set to local var the return string on a exec(@sql) dynamic sql execute call?

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
June 10, 2010 at 8:14 am

#223782

declare @adv_SDate datetime
declare @adv_EDate datetime
declare @tran_SDate datetime
declare @tran_EDate datetime
declare @reportDate varchar(10)
declare @sql varchar(4000)
declare @maxRN int
declare @sqlstart varchar(4000)
declare @sqlFinal varchar(max)
declare @sqlMid varchar(max)
declare @sqlEnd varchar(4000)
declare @tablename varchar(100)
declare @monthName varchar(100)
declare @YYYY varchar(100)
set @sql= ''
set @adv_SDate = CAST(CONVERT(VARCHAR(10), Dateadd(MONTH, -1, Getdate() - DAY( Getdate()) + 1), 101) AS DATETIME) + 1
set @adv_EDate = dateadd(ss,-1,CAST(CONVERT(VARCHAR(10), Getdate() - DAY( Getdate()) + 1, 101) AS DATETIME)) + 1
set @tran_SDate = @adv_SDate -1
set @tran_EDate = @adv_eDate -1
set @reportDate = convert(varchar(10),@adv_EDate -1,101)
set @monthName = DATENAME(MM, @adv_SDate)
set @YYYY = cast( YEAR(@adv_SDate) as varchar(4))
set @tablename = 'tempdb.dbo.TestViewFlagPivot'+ @monthName + @YYYY
set @maxRN = 10
set @sqlMid = '
declare @cnt int
declare @sqlInner varchar(max)
set @cnt = 1
set @sqlInner = ''''
while @cnt <= '+CAST(@maxRN as varchar)+'
begin
set @sqlInner = @sqlInner + ''
MAX(CASE a.rn WHEN 1 THEN a.tr ELSE 0END) AS c''+cast(@cnt as varchar)+'',
MAX(CASE a.rn WHEN 1 THEN a.namELSE '''''''' END) AS n''+cast(@cnt as varchar)+'',
MAX(CASE a.rn WHEN 1 THEN a.rate ELSE 0.00 END) AS r''+cast(@cnt as varchar)+'',
MAX(CASE a.rn WHEN 1 THEN a.chargesELSE 0.00 END) AS t''+cast(@cnt as varchar)+'',
''
set @cnt = @cnt + 1
end
select @sqlInner '
print @sqlMid
--???????????????????????????????????????????????????????????????????????????
--set @sql = execute(@sqlMid) --is there a way to set the return string or
--output from the exec of @sqlMid to a local variable?
--???????????????????????????????????????????????????????????????????????????

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 1

this is a much better and simpler example...

declare @sql varchar(1000)

declare @testInt int

set @testInt =0

set @sql = 'select 1'

exec (@sql)

--how can i set @testInt = to what the exec(@sql) returns?

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

got it...below is the solution

declare @sql nvarchar(1000)

declare @testInt int

declare @ParmDefinition nvarchar(100)

--set @testInt =0

set @sql = 'select @testInt =1'

set @ParmDefinition = '@testInt int out'

exec sp_executesql @sql,@ParmDefinition,@testInt out

select @testInt