Creating a sql stored procedure which contains temporary tables 2

[ghbeers]

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

 

[fredericofonseca]

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

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

;

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ghbeers]

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.

 

[ghbeers]

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.

 

[fredericofonseca]

better to post what you have tried and the error you got.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ghbeers]

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

 

[Andrzejek]

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 [blue]Great Post[/blue] 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.