robsuttonjr
MIS
I am trying to find the best way to store temporary query information that is only used within a stored procedure. However since multiple people can possible execute the same stored procedure I don't want a chance that the temp data would exist which would cause an error in the 'select * into..' command. Does anyone see a better way to do the following stored procedure?
CREATE PROCEDURE sp_Query_Forecast21
(@ItemId VARCHAR(20))
as
DECLARE @RandomNumber VARCHAR(100)
SET @RandomNumber = SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)+SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)+SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)+SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)
DECLARE @SqlStr1 VARCHAR(7000)
DECLARE @SqlStr2 VARCHAR(7000)
DECLARE @SqlStr3 VARCHAR(7000)
SELECT @SqlStr1 = 'SET ANSI_NULLS ON SET ANSI_WARNINGS ON SELECT * INTO ##TempTable_'+@RandomNumber+' FROM OPENQUERY(forecast21, ''SELECT ffct_offer_product_sku.offer_id, ffct_offer_product_sku.Sku AS Item_id, ffct_offer_product_sku.Description, ' +
'ffct_offer_product_sku.Units, ffct_offer_product_sku.Revenue, ffct_offer_version.Forecast_version_type AS F21_Type ' +
'FROM ffct_offer_product_sku, ffct_offer_version ' +
'WHERE ffct_offer_product_sku.offer_id (+)= ffct_offer_version.offer_id AND ffct_offer_product_sku.version_no (+)= ffct_offer_version.Version_no AND '+
'(ffct_offer_version.Forecast_version_type = ''''WORK'''' or ffct_offer_version.Forecast_version_type = ''''ACTL'''') AND ' +
'ffct_offer_product_sku.Offer_product_id = '''''
+@ItemId+
''''' ORDER BY Offer_id, Forecast_version_type, Offer_product_id'' )'
EXEC(@SqlStr1)
SELECT @SqlStr2 = 'SELECT * FROM ##TempTable_'+@RandomNumber
EXEC(@SqlStr2)
SELECT @SqlStr3 = 'DROP TABLE ##TempTable_'+@RandomNumber
EXEC(@SqlStr3)
GO
The more I learn the more I find I know nothing.
Rob
CREATE PROCEDURE sp_Query_Forecast21
(@ItemId VARCHAR(20))
as
DECLARE @RandomNumber VARCHAR(100)
SET @RandomNumber = SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)+SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)+SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)+SUBSTRING(CONVERT(VARCHAR,RAND()),3,100)
DECLARE @SqlStr1 VARCHAR(7000)
DECLARE @SqlStr2 VARCHAR(7000)
DECLARE @SqlStr3 VARCHAR(7000)
SELECT @SqlStr1 = 'SET ANSI_NULLS ON SET ANSI_WARNINGS ON SELECT * INTO ##TempTable_'+@RandomNumber+' FROM OPENQUERY(forecast21, ''SELECT ffct_offer_product_sku.offer_id, ffct_offer_product_sku.Sku AS Item_id, ffct_offer_product_sku.Description, ' +
'ffct_offer_product_sku.Units, ffct_offer_product_sku.Revenue, ffct_offer_version.Forecast_version_type AS F21_Type ' +
'FROM ffct_offer_product_sku, ffct_offer_version ' +
'WHERE ffct_offer_product_sku.offer_id (+)= ffct_offer_version.offer_id AND ffct_offer_product_sku.version_no (+)= ffct_offer_version.Version_no AND '+
'(ffct_offer_version.Forecast_version_type = ''''WORK'''' or ffct_offer_version.Forecast_version_type = ''''ACTL'''') AND ' +
'ffct_offer_product_sku.Offer_product_id = '''''
+@ItemId+
''''' ORDER BY Offer_id, Forecast_version_type, Offer_product_id'' )'
EXEC(@SqlStr1)
SELECT @SqlStr2 = 'SELECT * FROM ##TempTable_'+@RandomNumber
EXEC(@SqlStr2)
SELECT @SqlStr3 = 'DROP TABLE ##TempTable_'+@RandomNumber
EXEC(@SqlStr3)
GO
The more I learn the more I find I know nothing.
Rob