Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with Sp_executeSql table variable output Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 8:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1503609
Posted Thursday, October 10, 2013 8:20 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 705, Visits: 2,191
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
Post #1503611
Posted Thursday, October 10, 2013 8:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1503620
Posted Thursday, October 10, 2013 12:38 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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
Post #1503742
Posted Thursday, October 10, 2013 12:40 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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
Post #1503743
Posted Friday, October 11, 2013 12:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1503875
Posted Friday, October 11, 2013 12:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 278, Visits: 419
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
Post #1503887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse