INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Creating a sql stored procedure which contains temporary tables

Creating a sql stored procedure which contains temporary tables

(OP)
I've never created a stored procedure with temp tables in the past. I need to do this now because my query contains a temp table and must be in a stored procedure. Please help. Query is below. I have another query I also need to place in a stored procedure that has multiple temp tables. I don't want to do this until I know how to handle the temp tables. I will eventually use the stored procedure in the Infomaker reporting tool which will not accept my temp tables in the syntax. Thank you.

DECLARE @LASTATTEND TABLE
(
ID_NUM INT,
LAST_ATTEND_DTE DATE
)
INSERT INTO @LASTATTEND
SELECT ID_NUM,
MAX(LAST_DTE_OF_ATTEND)
FROM STUDENT_CRS_HIST
GROUP BY ID_NUM

SELECT C.ID_NUM,
N.LAST_NAME,
N.FIRST_NAME,
N.EMAIL_ADDRESS,
C.YR_CDE,
C.TRM_CDE,
C.PROG_CDE,
C.STAGE,
CAST (C.HIST_STAGE_DTE AS DATE) 'HIST_STAGE_DTE',
L.LAST_ATTEND_DTE,
C.CUR_CANDIDACY,
C.DIV_CDE,
C.LOCA_CDE,
C.CANDIDACY_TYPE

FROM CANDIDACY C, NAME_MASTER N, @LASTATTEND L

WHERE (C.YR_CDE = 2017 OR C.HIST_STAGE_DTE >= '01/01/17')
AND C.STAGE IN ('NTACC', 'ACCPT')
AND C.PROG_CDE = 'UND'
AND C.ID_NUM = N.ID_NUM
AND C.ID_NUM = L.ID_NUM

ORDER BY C.YR_CDE, C.TRM_CDE, N.LAST_NAME, N.FIRST_NAME



RE: Creating a sql stored procedure which contains temporary tables

for starters - what you have here is a declared table, not a temp table

Declared tables should be avoided unless the number of records on it is going to be quite small, in the order of 500 or less.

Second - it would be advisable to get used to the newer (with many years) syntax of joins - the one you use will eventually get removed


finally - on this particular case you do not need to use a declared table - see second example below

But your code looks correct - did it fail or gave you any error?




code rewritten using a temp table and new join style

CODE

if object_id('tempdb..#LASTATTEND') is not null
   drop table #LASTATTEND
create table #LASTATTEND
(ID_NUM INT
, LAST_ATTEND_DTE DATE
)
;

INSERT INTO #LASTATTEND
SELECT ID_NUM
     , MAX(LAST_DTE_OF_ATTEND)
FROM STUDENT_CRS_HIST
GROUP BY ID_NUM
;

SELECT C.ID_NUM
     , N.LAST_NAME
     , N.FIRST_NAME
     , N.EMAIL_ADDRESS
     , C.YR_CDE
     , C.TRM_CDE
     , C.PROG_CDE
     , C.STAGE
     , CAST (C.HIST_STAGE_DTE AS DATE) as 'HIST_STAGE_DTE'
     , L.LAST_ATTEND_DTE
     , C.CUR_CANDIDACY
     , C.DIV_CDE
     , C.LOCA_CDE
     , C.CANDIDACY_TYPE

FROM CANDIDACY C
inner join NAME_MASTER N
   on C.ID_NUM = N.ID_NUM
inner join #LASTATTEND L
   on C.ID_NUM = L.ID_NUM

WHERE (C.YR_CDE = 2017 OR C.HIST_STAGE_DTE >= '01/01/17') -- you should for a portability and clarity use convert and date style to define the date
AND C.STAGE IN ('NTACC', 'ACCPT')
AND C.PROG_CDE = 'UND'

ORDER BY C.YR_CDE
       , C.TRM_CDE
       , N.LAST_NAME
       , N.FIRST_NAME
; 

code rewritten using a inline table and a optional top 1 ... order by

CODE

;

SELECT C.ID_NUM
     , N.LAST_NAME
     , N.FIRST_NAME
     , N.EMAIL_ADDRESS
     , C.YR_CDE
     , C.TRM_CDE
     , C.PROG_CDE
     , C.STAGE
     , CAST (C.HIST_STAGE_DTE AS DATE) as 'HIST_STAGE_DTE'
     , L.LAST_ATTEND_DTE
     , C.CUR_CANDIDACY
     , C.DIV_CDE
     , C.LOCA_CDE
     , C.CANDIDACY_TYPE

FROM CANDIDACY C
inner join NAME_MASTER N
   on C.ID_NUM = N.ID_NUM
inner join (SELECT ID_NUM
                 , MAX(LAST_DTE_OF_ATTEND) as LAST_ATTEND_DTE
            FROM STUDENT_CRS_HIST
            GROUP BY ID_NUM
           ) L
   on C.ID_NUM = L.ID_NUM
/* -- alternative way of doing this particular select - performance may vary from the above group by
cross join (SELECT top 1 LAST_DTE_OF_ATTEND as LAST_ATTEND_DTE
            FROM STUDENT_CRS_HIST l2
            where C.ID_NUM = L2.ID_NUM
            order by LAST_DTE_OF_ATTEND desc
           ) L

*/


WHERE (C.YR_CDE = 2017 OR C.HIST_STAGE_DTE >= '01/01/17') -- you should for a portability and clarity use convert and date style to define the date
AND C.STAGE IN ('NTACC', 'ACCPT')
AND C.PROG_CDE = 'UND'

ORDER BY C.YR_CDE
       , C.TRM_CDE
       , N.LAST_NAME
       , N.FIRST_NAME
; 

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Creating a sql stored procedure which contains temporary tables

(OP)
Thank you Frederico,

I will modify the way I am declaring my table. Thank you for clarifying. I do realize I was not using good structure in joining my tables since this was a quickly prepared example and I know I would get all my data keeping it simple. But I do appreciate your clarifications very much. I will give your response a try. Thank you so much.

RE: Creating a sql stored procedure which contains temporary tables

(OP)
I'm getting some errors taking your first option into the Create Procedure structure's logic. Am I possibly not inserting the code correctly into the Create Procedure structure. Can you please show me how your code would fit into the CREATE PROCEDURE, AS BEGIN, END structure? Thank you.

RE: Creating a sql stored procedure which contains temporary tables

(OP)
I got it to work without errors. Thank you so much for your help. Very much appreciated.

RE: Creating a sql stored procedure which contains temporary tables

To show appreciation for the help received, and help others who may be looking at this post for the help with their issue, click on Great Post link in the most helpful post to award a star to whoever helped you.

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close