Stored procedure temp tables 1

[robsuttonjr]

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

 

[indrahig]

if it's just within stored proc, why don't use #TempTable?

 

[robsuttonjr]

When I try to use #TempTable it says it doesn't exist after I execute the sql string. I am under the impression that #temptables are dropped after the first EXEC(xxx) is encountered.

The more I learn the more I find I know nothing.

Rob

 

[SQLSister]

I've not tried it in a case like this but would a table variable work? I know they have restrictions a temp table doesn;t but not sure if this owuld be one and of course I'm not where I can look it up.

Questions about posting. See faq183-874

 

[robsuttonjr]

Thanks for the input. I will check out what a table variable is to see if this would work. I am not really versed in all the options and usually end up making something work. In this case using random temptable would work since I am dropping them immediately but I just don't like it. I would just rather have some temp something I can throw data into that goes away and has no chance of interfering with other executing the stored procedure simul and multi times. Anyway. Thanks

The more I learn the more I find I know nothing.

Rob

 

[indrahig]

I guess, EXEC(@SqlStr2) is used because you are using table with random number on the back of table name, to avoid same table table name every time that stored proc is executed.

If you use #TempTable, you don't need to add random number into that table name. The name of that temporary table is just #TempTable.

And you don't have to use EXEC(@SqlStr2) anymore
just
SELECT * INTO #TempTable FROM ...

 

[robsuttonjr]

The EXEC(@Sqlstr2) is needed because I am combining variables with an OPENQUERY. I am currently working on inserting the open query into a TEMB VARIABLE as recommended by SQL SISTER. But I am running into that bug which says I need to set ansi_nulls/warnings bunk. But I am persistent as a slug and with enough time I may get it. Thanks to all for the tips.

The more I learn the more I find I know nothing.

Rob

 

[robsuttonjr]

Perhaps it was already assumed I knew this but when using EXEC(@sqlstr) to execute select statements you need to imbed all of the code in ONE @sqlstr in which you are using either TABLE VARIABLES or #TempTables. I finally figured this out and it became clear as day. See below my final stored proc for my resolution using TABLE VARIABLES with very minimal overhead and very quick. I know the single quotes look funny but it works.



CREATE PROCEDURE sp_Query_Forecast21b

(@ItemId VARCHAR(20))

as

DECLARE @SqlStr1 VARCHAR(2000)

DECLARE @TableVar1 TABLE (Offer_id VARCHAR(10), Item_id VARCHAR(20), Description VARCHAR(50), Units INT, Revenue DECIMAL(8,2), F21_Type VARCHAR(10))

SELECT @SqlStr1 =
'DECLARE @TableVar1 TABLE (Offer_id VARCHAR(10), Item_id VARCHAR(20), Description VARCHAR(50), Units INT, Revenue DECIMAL(8,2), F21_Type VARCHAR(10)) ' +
'DECLARE @TableVar2 TABLE (Offer_id VARCHAR(10), Item_id VARCHAR(20), Description VARCHAR(50), Units INT, Revenue DECIMAL(8,2), F21_Type VARCHAR(10)) ' +
'DECLARE @TableVar3 TABLE (Offer_id VARCHAR(10), Item_id VARCHAR(20), Description VARCHAR(50), Units INT, Revenue DECIMAL(8,2), F21_Type VARCHAR(10)) ' +
'INSERT @TableVar1 SELECT * 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.Units > 0 AND ffct_offer_product_sku.Revenue > 0 AND ' +
'ffct_offer_product_sku.Offer_product_id = '''''
+@ItemId+
''''' ORDER BY Offer_id, Forecast_version_type, Offer_product_id'' )' +
' INSERT @TableVar2 SELECT * FROM @TableVar1 ' +
' INSERT @TableVar3 SELECT * FROM @TableVar1 ' +
' SELECT a.Offer_id, b.Units AS Work_Units, b.Revenue as Work_Revenue, a.Units AS Actl_Units, a.Revenue AS Actl_Revenue, LEFT(a.Item_id,5) AS Item_id, RIGHT(a.Item_id,14) AS Sku_id ' +
'FROM @TableVar2 a '+
' LEFT OUTER JOIN @TableVar3 b '+
' ON RTRIM(a.offer_id)+RTRIM(a.Item_id)=RTRIM(b.Offer_id)+RTRIM(b.Item_id) '

EXEC(@SqlStr1)


GO

The more I learn the more I find I know nothing.

Rob