Creating an SP that will return a temp table to SP

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

SQL Server 2000 Forums

SQL Server Development (2000)

Creating an SP that will return a temp table to SP

Author

Topic

daniel.newman@bis-web.net
Yak Posting Veteran

71 Posts

Posted - 2001-10-04 : 11:39:55

I have this one stored procedure, but just discovered I need to reuse the code in another SP. Instead, I wondered if I could use the general stored procedure inside another.

This is what I have so far:

CREATE TABLE #UsersChoices
(UserEmail varchar(255) not null,
whShort varchar(6) null,
locID int null,
typeID int null)

INSERT INTO #UsersChoices
SELECT IA.UserEmail, IWh.whShort, IL.locID, IJt.typeID
FROM Interest_Address IA
INNER JOIN Interest_WorkingHours IWh ON IA.UserEmail = IWh.UserEmail
INNER JOIN Interest_Location IL ON IA.UserEmail = IL.UserEmail
INNER JOIN Interest_JobTypes IJt ON IA.UserEmail = IJt.UserEmail
WHERE DATEDIFF(day, GetDate(), IA.Reminder) > 0 --only use those people who haven't received their reminder yet.

SELECT UC.UserEmail, JV.*, JD.*
FROM JobVacancies JV
INNER JOIN #UsersChoices UC ON JV.whShort = ISNULL(UC.whShort, JV.whShort)
AND JV.locID = ISNULL(UC.locID, JV.locID)
AND JV.typeID = ISNULL(UC.typeID, JV.typeID)
INNER JOIN JobDetails JD ON JV.RefNo = JD.RefNo
WHERE DATEDIFF(day, JV.StartDate, GetDate()) = 0 --only use todays jobs!
ORDER BY UC.UserEmail

DROP TABLE #UsersChoices

But I would like to have the first 12 lines inside another SP, so I could call it from many other SP's.

Could I do something like:

CREATE TABLE #UsersChoices
(UserEmail varchar(255) not null,
whShort varchar(6) null,
locID int null,
typeID int null)

INSERT INTO #UsersChoices
SELECT IA.UserEmail, IWh.whShort, IL.locID, IJt.typeID
FROM Interest_Address IA
INNER JOIN Interest_WorkingHours IWh ON IA.UserEmail = IWh.UserEmail
INNER JOIN Interest_Location IL ON IA.UserEmail = IL.UserEmail
INNER JOIN Interest_JobTypes IJt ON IA.UserEmail = IJt.UserEmail
WHERE DATEDIFF(day, GetDate(), IA.Reminder) > 0 --only use those people who haven't received their reminder yet.

SELECT * FROM #UsersChoices

And this would return the temp table to some other SP?

DECLARE @tempTable

SELECT @tempTable = sp_GeneralTempTable

Something like that would be great. I know how to do this in VB, but not in SQL, so thanks for your help.

Daniel.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources