Issue with Sp_executeSql table variable output

Question

Issue with Sp_executeSql table variable output

Bhuvnesh

SSC Guru

Points: 59351
More actions
October 10, 2013 at 8:15 am

#281127

My requirement is i need to populate @tblAmount dynamically.
Here is the code
DECLARE @AdjustmentBatch_ID INT
DECLARE @Platform VARCHAR(20), @Type INT, @lStr NVARCHAR(1000),@TableName VARCHAR(20),@ColName VARCHAR(20)
,@parameters NVARCHAR(30)
SELECT @Platform = '',@lStr = '',@TableName = '',@ColName = '', @AdjustmentBatch_ID = 15
--DECLARE @tblAmount TABLE(LegalEntity VARCHAR(10), Amount MONEY )
--BEGIN TRY
SELECT @Type = type,@Platform = Platform FROM dbo.Adjustment_Batch ab(NOLOCK)
WHERE ab.AdjustmentBatch_ID = @AdjustmentBatch_ID AND ab.Status = 1
SELECT @TableName = CASE
WHEN @Type = 1 THEN 'GLEntry'
WHEN @Type = 2 THEN 'GrossPremium'
END,
@ColName = CASE
WHEN @Type = 1 THEN 'BaseAmount'
WHEN @Type = 2 THEN 'GrossWrittenPremium'
END
SELECT @lStr = N'DECLARE @tblAmount TABLE(LegalEntity VARCHAR(10), Amount MONEY );INSERT INTO @tblAmount (LegalEntity, Amount)'+
'SELECT LegalEntity,SUM(CAST('+ @ColName +' as MONEY)) AS Amount FROM dbo.' + @TableName + ' t(NOLOCK)'+
'WHERE t.AdjustmentBatch_ID = ' + CAST(@AdjustmentBatch_ID AS VARCHAR(20)) + ' GROUP BY t.LegalEntity '
PRINT @lStr
EXEC SP_executeSql @lstr
,@parameters = N'@tblAmount(LegalEntity VARCHAR(10), Amount MONEY ) TABLE OUTPUT'
,@tblAmount
Error :
Msg 137, Level 15, State 2, Line 45
Must declare the scalar variable "@tblAmount".
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 7 posts - 1 through 6 (of 6 total)

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

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

Hi,

The last line of your query references @tblAmount but you haven't declared it and given it a type. That's why it's failing.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Now i have modified this.

Please see . its still failing

DECLARE @AdjustmentBatch_ID INT

DECLARE @Platform VARCHAR(20), @Type INT, @lStr NVARCHAR(1000),@TableName VARCHAR(20),@ColName VARCHAR(20)

,@parameters NVARCHAR(30)

SELECT @Platform = '',@lStr = '',@TableName = '',@ColName = '', @AdjustmentBatch_ID = 15

DECLARE @tblAmountOut TABLE(LegalEntity VARCHAR(10), Amount MONEY )

--BEGIN TRY

SELECT @Type = type,@Platform = Platform FROM dbo.Adjustment_Batch ab(NOLOCK)

WHERE ab.AdjustmentBatch_ID = @AdjustmentBatch_ID AND ab.Status = 1

SELECT @TableName = CASE

WHEN @Type = 1 THEN 'GLEntry'

WHEN @Type = 2 THEN 'GrossPremium'

WHEN @Type = 3 THEN 'GrossClaim'

WHEN @Type = 4 THEN 'CededPremium'

WHEN @Type = 5 THEN 'CededClaim'

END,

@ColName = CASE

WHEN @Type = 1 THEN 'BaseAmount'

WHEN @Type = 2 THEN 'GrossWrittenPremium'

WHEN @Type = 3 THEN 'IndemnityPaidLoss'

WHEN @Type = 4 THEN 'CededWrittenPremium'

WHEN @Type = 5 THEN 'ReinsuaranceIndemnityPaidLoss'

END

SELECT @lStr = N'DECLARE @tblAmount TABLE(LegalEntity VARCHAR(10), Amount MONEY );INSERT INTO @tblAmount (LegalEntity, Amount)'+

'SELECT LegalEntity,SUM(CAST('+ @ColName +' as MONEY)) AS Amount FROM dbo.' + @TableName + ' t(NOLOCK)'+

'WHERE t.AdjustmentBatch_ID = ' + CAST(@AdjustmentBatch_ID AS VARCHAR(20)) + ' GROUP BY t.LegalEntity '

PRINT @lStr

EXEC SP_executeSql @lstr

,@parameters = N'@tblAmountOut(LegalEntity VARCHAR(10), Amount MONEY ) TABLE OUTPUT '

,@tblAmountOut = @tblAmount OUTPUT

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 3

Are you getting the same error? Which table is it complaining about? @tblAmount?

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 4

I don't use SP_executeSQL often... but I'm guessing that if you declare the @tblAmount table in the @parameters variable instead of the SQL string (@lStr) it may work.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

can any body help me to populate table variable using dynamic query a i posted above. i had hard time to resolve this .

the above dynamic sql get tablename based on type id THEN we need to fetch some data from select query and populate table variable @tblAmount

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

saravanan.a 88885 SSC Eights! Points: 993 More actions · Answer 6

try to replace the following part in your coding i hope it will work for you .

SELECT @lStr = N'DECLARE @tblAmount TABLE(LegalEntity VARCHAR(10), Amount MONEY );INSERT INTO @tblAmount (LegalEntity, Amount)'+

'SELECT LegalEntity,SUM(CAST('+ @ColName +' as MONEY)) AS Amount FROM dbo.' + @TableName + ' t(NOLOCK)'+

'WHERE t.AdjustmentBatch_ID = ' + CAST(@AdjustmentBatch_ID AS VARCHAR(20)) + '

GROUP BY t.LegalEntity select * from @tblAmount'

PRINT @lStr

EXEC SP_executeSql @lstr