October 6, 2008 at 9:35 am
I need to do something equivalent to:
IF @PropType = 'Student'
BEGIN
SELECT ISNULL(d.AGE, -1) AS Age
INTO #AgeList
FROM TENANT AS t
INNER JOIN UNIT AS u ON u.HMY = t.HUNIT
LEFT OUTER JOIN LEASEBUT5 AS d ON t.HMYPERSON = d.HCODE
...
END
ELSE
BEGIN
SELECT ISNULL(d.AGE, -1) AS Age
INTO #AgeList
FROM TENANT AS t
INNER JOIN UNIT AS u ON u.HMY = t.HUNIT
LEFT OUTER JOIN LEASEBUT1 AS d ON t.HMYPERSON = d.HCODE
...
END
Note the difference between the two is the LEASEBUT table. Our student properties are in LEASEBUT5, conventional are in LEASEBUT1. The remainder of both statements is identical. I’m trying to load this into a temp table since this exact same data is referenced multiple times in the associated script.
The problem I’m running into here is it is complaining that #AgeList is already created when it runs into the ELSE part of the statement. Am I resigned to doing this as a dynamic script so I can change the table name based on the property type?
October 6, 2008 at 9:50 am
No need for dynamic SQL at all.
CREATE TABLE #AgeList (
Age tinyint
)
IF @PropType = 'Student'
BEGIN
INSERT INTO #AgeList (Age)
SELECT ISNULL(d.AGE, -1) AS Age
FROM TENANT AS t
INNER JOIN UNIT AS u ON u.HMY = t.HUNIT
LEFT OUTER JOIN LEASEBUT5 AS d ON t.HMYPERSON = d.HCODE
...
END
ELSE
BEGIN
INSERT INTO #AgeList (Age)
SELECT ISNULL(d.AGE, -1) AS Age
FROM TENANT AS t
INNER JOIN UNIT AS u ON u.HMY = t.HUNIT
LEFT OUTER JOIN LEASEBUT1 AS d ON t.HMYPERSON = d.HCODE
...
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2008 at 9:55 am
Will creating the temp table before the IF statement prevent the table from being automatically dropped at the end of the connection or will it still automatically drop?
Our application scripting engine allows use of temp tables but relies on SQL to automatically drop them at the end of the batch. I can't put a DROP TABLE statement at the end.
October 6, 2008 at 10:15 am
The temp table will be automatically dropped when the procedure that created it ends or the connection that created it closes. It's exactly the same whether you explicitly create it or create with a select ... into
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply