June 10, 2010 at 8:14 am
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 0 END) AS c''+cast(@cnt as varchar)+'',
MAX(CASE a.rn WHEN 1 THEN a.nam ELSE '''''''' 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.charges ELSE 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?
--???????????????????????????????????????????????????????????????????????????
June 10, 2010 at 8:16 am
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?
June 10, 2010 at 8:48 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply