-
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
-
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.