sp_executesql - using table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

sp_executesql - using table variable

Author

Topic

muniappan
Starting Member

7 Posts

Posted - 2008-01-18 : 16:28:00

In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?

ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)
AS
DECLARE @SQL nvarchar(1000)

-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber1 = @ItemNumSKU
END

-- Creating table variable to have values from 1 to 12
DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END

--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'

SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'

EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-18 : 16:40:39

You can't use table variable inside a dynamic sql. Use temporary table instead.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

muniappan
Starting Member

7 Posts

Posted - 2008-01-18 : 16:51:08

I modified the SP to use temp table then also I am getting error 'Invalid column name '#MonthSales''. The mofied SP is,

ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)
AS
DECLARE @SQL nvarchar(1000)

-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber1 = @ItemNumSKU
END

-- Creating table variable to have values from 1 to 12
CREATE TABLE #MonthSales (MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO #MonthSales VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END

--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'

SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + #MonthSales + ' MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'

EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-18 : 17:40:26

refer: http://forums.asp.net/t/1207691.aspx

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

muniappan
Starting Member

7 Posts

Posted - 2008-01-18 : 17:44:55

Thank you. It is working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources