May 4, 2005 at 2:53 pm
I need to create a global #TemporaryTable that I use in INNER JOINs. The Dynamic SQL i create is correct and returns records but the #TT does not get populated?????
There was one article i read in this forum but I am already creating a global #TT
Here is a snipit of the code where the table is created and populated. Any help will be appreciated greatly
CREATE TABLE #doctorIDTable(DoctorIDs INT NOT NULL)
DECLARE @sSQL NVARCHAR(4000)
DECLARE @sWhereClause nvarchar(400)
--PROVINCE WHERE CLAUSE
DECLARE @sProvinceClause VARCHAR(100)
IF @iProvinceID <> 0
SET @sProvinceClause = '(Assessments.ProvinceID = ' + CAST(@iProvinceID AS VARCHAR(3)) + ')'
ELSE
SET @sProvinceClause = ''
--PATIENT FILTER CLAUSE
DECLARE @sPatientClause VARCHAR(100)
IF @iPatientFilter = 0
SET @sPatientClause = 'Assessments.QuestionID >= 50'
ELSE
SET @sPatientClause = ''
IF @iProvinceID <> 0 or @iPatientFilter <> 0
SET @sWhereClause = 'WHERE '
IF @iProvinceID <> 0
SET @sWhereClause = @sWhereClause + @sProvinceClause
IF @iPatientFilter = 0
IF @iProvinceID <> 0
SET @sWhereClause = @sWhereClause + ' AND ' + @sPatientClause
ELSE
SET @sWhereClause = @sWhereClause + @sPatientClause
SET @sSQL = N'INSERT INTO #doctorIDTable (doctorIDs) (SELECT DISTINCT(Demographics.DoctorID) as DoctorID FROM Demographics INNER JOIN #integers ON Demographics.AnswerID = #integers.int INNER JOIN Assessments ON Demographics.DoctorID = Assessments.DoctorID ' + @sWhereClause + ') '
EXEC (@sSQL)
PRINT @sSQL
SELECT * FROM #doctorIDTable
May 4, 2005 at 3:14 pm
OK, go stand in the corner. Then smack yourself a few times across the face. Then go back to programming 101 and learn how to make use of BEGIN and END in the context of IF statements.
Then go look up what happens in SQL when you have nulls in strings.
That may not be the problem, but it's likely
May 4, 2005 at 3:28 pm
Ok the Begin and Ends don't matter...the script prints to text fine and executes fine. There is not problem there.
Thus I will not stand in the corner and smack myself a few times
Got a good laugh though
May 4, 2005 at 4:04 pm
If you want a global temporary table then use CREATE TABLE ## rather than CREATE TABLE #.
Be careful though. When the creating connection logs out the table will vanish and also other connections will be able to see the table.
May 5, 2005 at 3:35 am
Hi Mark,
Your Dynamic SQL looks fine in the main.
Only problem I can see is:-
It handles @iProvinceID > 0 AND @iPatientFilter >= 0 perfectly.
Any other result will cause it to fail.
If @iProvinceID = 0 AND @iPatientFilter = 0 the D Sql will fail completely as @sWhereClause has been declared but never initialised.
If @iProvinceID = 0 AND @iPatientFilter > 0 you will get "INNER JOIN Assessments ON Demographics.DoctorID = Assessments.DoctorID WHERE )" as @sWhereClause is initialised to ' WHERE ' but @sPatientClause is set to '' in the ELSE section of the @iPatientFilter check.
So - If you anticipate anything other than @iProvinceID > 0 AND @iPatientFilter >= 0, you will need to re-think your code......
If this logic is fine, could you let me know where #integers is created and populated.
Have fun
Steve
We need men who can dream of things that never were.
May 5, 2005 at 7:56 am
Thanks for your replies
Steve you helped me see some mistakes and I have since corrected them. Also I needed to set @sWhereClause = '' because if left to NULL when added to the @sSQL it would cause that NVARCHAR to become NULL
Everything looks ok now and it seems to be functioning properly
Thanks again everyone!!
May 5, 2005 at 9:02 am
Excellent - here to help matey
Have fun
Steve
We need men who can dream of things that never were.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply