to save a resultset in store procedure

[sanford101]

I have several places where I used the subquery like this
in my store procedure

SELECT *
FROM MASTER_NEW
WHERE ProductCode NOT IN
(SELECT ProductCode FROM MAPSDATA)



I am going to use the subquery

FROM MASTER_NEW
WHERE ProductCode NOT IN
(SELECT ProductCode FROM MAPSDATA)



in serveral other places in the stored procedure, like

select count(*)
FROM MASTER_NEW
WHERE ProductCode NOT IN
(SELECT ProductCode FROM MAPSDATA)


Is there any ways that I can save the set once and use over and over again to save
some time?

Thanks a lot. Any tips will greatly appreciated

[Buzby]

You can use CREATE TABLE to create a temp table at the beginning of your Stored Procedure - a temp table always starts with a # sign;

CREATE TABLE #TEMPTABLE

(see the t_SQL Help for more details on creating temp tables)
You can then fill this table with the data in your subquery using

INSERT INTO #TEMPTABLE SELECT... (again look in the help for the full syntax)

Then you can use this #TEMPTABLE in any of your queries in that stored procedure. You should DROP the table at the end of the procedure (although it should disappear on it's own if you logoff SQL)

Hope this helps.