Create Proc won't recognize declared variables

AlienHost · Nov 7, 2008

Hello, I hope you can help me as I'm new to using SQL. I'm trying to create a procedure, but when I test it in Query Analyzer, it doesn't recognize the variables that will be passed in as having been declared. Is there something wrong with my syntax? I've looked at several things and have added "Set nocount on" and "with recompile" thinking that might be the problem, but no luck. It isn't recognizing the @TherapistName or the @BeginDate, @EndDate, or @MM. Do these go out of scope or something? It also says the temp table exists in the db, but this code hasn't been run yet, so I don't know how it could.

Here's the code:

Code:

CREATE PROCEDURE dbo.usp_ProgressNotes
		 @TherapistName VARCHAR(60),@ProgramOrCampus VARCHAR(60), @BeginDate DATETIME, @EndDate DATETIME, @ServiceCodeGroup VARCHAR(30)
 
WITH RECOMPILE

 AS

BEGIN

DECLARE @Campus CHAR(3)
,@Len INT
,@MM VARCHAR(2)
,@DD VARCHAR(2)
,@YY  VARCHAR(4)
,@Date VARCHAR(18)

/**Get rid of temp table if exists before running**/

IF object_id('tempdb..#TmpProgressNotes') IS NOT NULL
BEGIN
   DROP TABLE #TmpProgressNotes
END

GO
SET NOCOUNT ON
/**Force 12AM on begin date***/
SET @MM = DATEPART(mm,@BeginDate)
SET @DD = DATEPART(dd,@BeginDate)
SET @YY = DATEPART(yy,@BeginDate)
SET @Date = @MM + '/' + @DD + '/' + @YY + ' 00:00:00'
SET @BeginDate = @Date

/**Force midnight on End date***/
SET @MM = DATEPART(mm,@EndDate)
SET @DD = DATEPART(dd,@EndDate)
SET @YY = DATEPART(yy,@EndDate)
SET @Date = @MM + '/' + @DD + '/' + @YY + ' 23:59:59'
SET @EndDate = @Date



/**PROGRESS NOTE***/
SELECT
v.CampusInNote AS Campus
,'PNGen' AS NoteType
,v.DocID
,v.TFSclientnumber AS TFSClientNumber
,v.TFSProgramInNote
,v.CPTCd
,v.ServiceDt AS ServiceDt
,DatePart(ww,v.ServiceDt) AS ServiceWeek
,'FundingSource' = ISNULL(v.FundingSource,'Not Entered In Progress Note')
,v.ProblemDesc AS ProblemDesc
,v.Detail
,v.ServiceCd AS ServiceCd
,v.ServiceUnits AS ServiceUnits
,v.ServiceMinutes AS ServiceMinutes
,v.ServiceTm AS ServiceTm
,v.ServiceProvider
,v.ServiceDesc
,'Held' AS SessionStatus
,v.DocLastSavedNm AS TherapistOrProvider
,FS.NameCalcInit
,FS.MedicaidNumber AS MedicaidNbr
,FS.ClientDOB AS DOB
,v.DocCreatedDt
,v.DocStatusCd
,v.DocSignedNm
,v.DocCreatorNm
,v.DocLastSavedDt
,v.MHODesc
,v.RateType
,v.MinutesRequired
,v.BillingUnitRate
,v.SupervisorUnitRate
,v.ServiceCodeGrpDesc
,v.ServiceLocation
,v.ServiceLocationBillingCd
,NULL AS PhysComments
,NULL AS InfectionControl
,v.NonServiceMinutes
INTO #TmpProgressNotes
FROM v_ProgressNotesGeneral v
INNER JOIN FD__TFS_FACE_SHEET FS ON v.TFSClientNumber = FS.TFSClientNumber
WHERE ServiceDt > = @BeginDate AND ServiceDt < = @EndDate

UNION
/**PROGRESS NOTE HEALTH SERVICES***/
Select
v.CampusInNote AS Campus
,'PNHS'
,v.DocID
,v.TFSclientnumber
,v.TFSProgramInNote
,v.CPTCd
,v.ServiceDt
,DatePart(ww,v.ServiceDt) AS ServiceWeek
,'FundingSource' = ISNULL(v.FundingSource,''Not Entered In Progress Note'')
,NULL
,v.Detail
,v.ServiceCd
,v.ServiceUnits
,v.ServiceMinutes
,NULL
,v.ServiceProvider
,v.ServiceDesc
,'Held' AS SessionStatus
,v.DocLastSavedNm AS TherapistOrProvider
,FS.NameCalcInit
,FS.MedicaidNumber AS MedicaidNbr
,FS.ClientDOB AS DOB
,v.DocCreatedDt
,v.DocStatusCd
,v.DocSignedNm
,v.DocCreatorNm
,v.DocLastSavedDt
,v.MHODesc
,v.RateType
,v.MinutesRequired
,v.BillingUnitRate
,v.SupervisorUnitRate
,v.ServiceCodeGrpDesc
,v.ServiceLocation
,v.ServiceLocationBillingCd
,v.PhysComments
,v.InfectionControl
,v.NonServiceMinutes
INTO #TmpProgressNotes
FROM v_ProgressNotesHS v
INNER JOIN FD__TFS_FACE_SHEET FS  ON v.TFSClientNumber = FS.TFSClientNumber
WHERE ServiceDt > = @BeginDate AND ServiceDt < = @EndDate

UNION
/**PROGRESS NOTE OP***/
Select
v.CampusInNote AS Campus
,'PNOP'
,v.DocID
,v.TFSclientnumber
,v.TFSProgramInNote
,v.CPTCd
,v.ServiceDt
,DatePart(ww,v.ServiceDt) AS ServiceWeek
,'FundingSource' = ISNULL(v.FundingSource,'Not Entered In Progress Note')
,NULL
,v.Detail
,v.ServiceCd
,v.ServiceUnits
,v.ServiceMinutes
,v.ServiceTm
,NULL
,v.ServiceDesc
,v.SessionStatus
,v.ServiceProvider AS TherapistOrProvider
,FS.NameCalcInit
,FS.MedicaidNumber AS MedicaidNbr
,FS.ClientDOB AS DOB
,v.DocCreatedDt
,v.DocStatusCd
,v.DocSignedNm
,v.DocCreatorNm
,v.DocLastSavedDt
,v.MHODesc
,v.RateType
,v.MinutesRequired
,v.BillingUnitRate
,v.SupervisorUnitRate
,v.ServiceCodeGrpDesc
,v.ServiceLocation
,v.ServiceLocationBillingCd
,NULL AS PhysComments
,NULL AS  InfectionControl
,v.NonServiceMinutes
INTO #TmpProgressNotes
FROM v_ProgressNotesOP v
INNER JOIN FD__TFS_FACE_SHEET FS  ON v.TFSClientNumber = FS.TFSClientNumber
WHERE ServiceDt > = @BeginDate AND ServiceDt < = @EndDate

/** Retrieve values from temp table**/

Select * from #TmpProgressNotes
WHERE v.DocCreatorNm = @TherapistName
AND vl.TFSProgramInNote = ISNULL(@ProgramOrCampus,'')
AND  v.ServiceCodeGrpDesc=ISNULL(@ServiceCodeGroup,'')		

END

jbenson001 · Nov 7, 2008

what is the exact error and line number?.. How are you calling the proc and with what values?

gmmastros · Nov 7, 2008

Get rid of the GO

Code:

IF object_id('tempdb..#TmpProgressNotes') IS NOT NULL
BEGIN
   DROP TABLE #TmpProgressNotes
END

[!][s]GO[/s][/!]
SET NOCOUNT ON
/**Force 12AM on begin date***/

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

AlienHost · Nov 7, 2008

Thanks JBenson and George. I'll get rid of the "GO" and try it. I never get to call the procedure because it isn't built yet. Just running the code checker through Query Analyzer nets me errors. Here is the error list:

Code:

CREATE PROCEDURE dbo.usp_ProgressNotes
		 @TherapistName VARCHAR(60),@ProgramOrCampus VARCHAR(60), @BeginDate DATETIME, @EndDate DATETIME, @ServiceCodeGroup VARCHAR(30)
 
-----------------------------*/
Server: Msg 170, Level 15, State 1, Procedure usp_ProgressNotes, Line 25
Line 25: Incorrect syntax near 'END'.
Server: Msg 2714, Level 16, State 1, Line 151
There is already an object named '#TmpProgressNotes' in the database.
Server: Msg 137, Level 15, State 1, Line 3
Must declare the variable '@BeginDate'.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@BeginDate'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@BeginDate'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@MM'.
Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@Date'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@EndDate'.
Server: Msg 137, Level 15, State 1, Line 11
Must declare the variable '@EndDate'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@EndDate'.
Server: Msg 137, Level 15, State 1, Line 13
Must declare the variable '@MM'.
Server: Msg 137, Level 15, State 1, Line 14
Must declare the variable '@Date'.
Server: Msg 137, Level 15, State 1, Line 61
Must declare the variable '@BeginDate'.
Server: Msg 156, Level 15, State 1, Line 74
Incorrect 
syntax near the keyword 'Not'.
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@TherapistName'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

gmmastros · Nov 7, 2008

Taking another look.....

Your date handling could be a lot better.

[tt][blue]/**Force 12AM on begin date***/
SET @MM = DATEPART(mm,@BeginDate)
SET @DD = DATEPART(dd,@BeginDate)
SET @YY = DATEPART(yy,@BeginDate)
SET @Date = @MM + '/' + @DD + '/' + @YY + ' 00:00:00'
SET @BeginDate = @Date
[/blue][/tt]

That is not a good way to handle this. I mean.... it's at the top of the procedure, so it won't really make much difference for performance, but there's a better way.

To remove the time portion of a variable, you can do this...

Code:

Set @BeginDate = DateAdd(Day, DateDiff(Day, 0, @BeginDate), 0)

Do something similar for your end date too. Then, I encourage you to add 1 to your end date and change the where clauses, like this...

Code:

Set @EndDate = DateAdd(Day, DateDiff(Day, 0, @EndDate), 0) [!] + 1[/!]

Then, your where clauses...

Code:

WHERE ServiceDt > = @BeginDate AND ServiceDt < @EndDate

Notice that originally, you had [!]<= @EndDate[/!] and I suggest you change it to [!]<[/!]. This is because we add 1 to the end date.

There are a couple benefits here. First, it may be possible for your ServiceDT to have milliseconds in them, so it's possible that you may be losing a couple rows. Likely, no. Possible, yes. By changing the where clause like I suggest, you won't lose and rows.

Second, since you are declaring varchar's and building the dates, you may run in to internationalization issues. By using the code I suggest here, you won't have to worry about it.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

gmmastros · Nov 7, 2008

Try this....

Code:

ALTER PROCEDURE dbo.usp_ProgressNotes
         @TherapistName VARCHAR(60),@ProgramOrCampus VARCHAR(60), @BeginDate DATETIME, @EndDate DATETIME, @ServiceCodeGroup VARCHAR(30)
 
WITH RECOMPILE

 AS

BEGIN

SET NOCOUNT ON

/**Force 12AM on begin date***/
Set @BeginDate = DateAdd(Day, DateDiff(Day, 0, @BeginDate), 0)

/**Force midnight on End date***/
Set @EndDate = DateAdd(Day, DateDiff(Day, 0, @EndDate), 0) + 1

Select *
From   (
       /**PROGRESS NOTE***/
       SELECT
       v.CampusInNote AS Campus
       ,'PNGen' AS NoteType
       ,v.DocID
       ,v.TFSclientnumber AS TFSClientNumber
       ,v.TFSProgramInNote
       ,v.CPTCd
       ,v.ServiceDt AS ServiceDt
       ,DatePart(ww,v.ServiceDt) AS ServiceWeek
       ,'FundingSource' = ISNULL(v.FundingSource,'Not Entered In Progress Note')
       ,v.ProblemDesc AS ProblemDesc
       ,v.Detail
       ,v.ServiceCd AS ServiceCd
       ,v.ServiceUnits AS ServiceUnits
       ,v.ServiceMinutes AS ServiceMinutes
       ,v.ServiceTm AS ServiceTm
       ,v.ServiceProvider
       ,v.ServiceDesc
       ,'Held' AS SessionStatus
       ,v.DocLastSavedNm AS TherapistOrProvider
       ,FS.NameCalcInit
       ,FS.MedicaidNumber AS MedicaidNbr
       ,FS.ClientDOB AS DOB
       ,v.DocCreatedDt
       ,v.DocStatusCd
       ,v.DocSignedNm
       ,v.DocCreatorNm
       ,v.DocLastSavedDt
       ,v.MHODesc
       ,v.RateType
       ,v.MinutesRequired
       ,v.BillingUnitRate
       ,v.SupervisorUnitRate
       ,v.ServiceCodeGrpDesc
       ,v.ServiceLocation
       ,v.ServiceLocationBillingCd
       ,NULL AS PhysComments
       ,NULL AS InfectionControl
       ,v.NonServiceMinutes
       FROM v_ProgressNotesGeneral v
       INNER JOIN FD__TFS_FACE_SHEET FS ON v.TFSClientNumber = FS.TFSClientNumber
       WHERE ServiceDt > = @BeginDate AND ServiceDt < @EndDate
       
       UNION
       /**PROGRESS NOTE HEALTH SERVICES***/
       Select
       v.CampusInNote AS Campus
       ,'PNHS'
       ,v.DocID
       ,v.TFSclientnumber
       ,v.TFSProgramInNote
       ,v.CPTCd
       ,v.ServiceDt
       ,DatePart(ww,v.ServiceDt) AS ServiceWeek
       ,'FundingSource' = ISNULL(v.FundingSource,'Not Entered In Progress Note')
       ,NULL
       ,v.Detail
       ,v.ServiceCd
       ,v.ServiceUnits
       ,v.ServiceMinutes
       ,NULL
       ,v.ServiceProvider
       ,v.ServiceDesc
       ,'Held' AS SessionStatus
       ,v.DocLastSavedNm AS TherapistOrProvider
       ,FS.NameCalcInit
       ,FS.MedicaidNumber AS MedicaidNbr
       ,FS.ClientDOB AS DOB
       ,v.DocCreatedDt
       ,v.DocStatusCd
       ,v.DocSignedNm
       ,v.DocCreatorNm
       ,v.DocLastSavedDt
       ,v.MHODesc
       ,v.RateType
       ,v.MinutesRequired
       ,v.BillingUnitRate
       ,v.SupervisorUnitRate
       ,v.ServiceCodeGrpDesc
       ,v.ServiceLocation
       ,v.ServiceLocationBillingCd
       ,v.PhysComments
       ,v.InfectionControl
       ,v.NonServiceMinutes
       FROM v_ProgressNotesHS v
       INNER JOIN FD__TFS_FACE_SHEET FS  ON v.TFSClientNumber = FS.TFSClientNumber
       WHERE ServiceDt > = @BeginDate AND ServiceDt < @EndDate
       
       UNION
       /**PROGRESS NOTE OP***/
       Select
       v.CampusInNote AS Campus
       ,'PNOP'
       ,v.DocID
       ,v.TFSclientnumber
       ,v.TFSProgramInNote
       ,v.CPTCd
       ,v.ServiceDt
       ,DatePart(ww,v.ServiceDt) AS ServiceWeek
       ,'FundingSource' = ISNULL(v.FundingSource,'Not Entered In Progress Note')
       ,NULL
       ,v.Detail
       ,v.ServiceCd
       ,v.ServiceUnits
       ,v.ServiceMinutes
       ,v.ServiceTm
       ,NULL
       ,v.ServiceDesc
       ,v.SessionStatus
       ,v.ServiceProvider AS TherapistOrProvider
       ,FS.NameCalcInit
       ,FS.MedicaidNumber AS MedicaidNbr
       ,FS.ClientDOB AS DOB
       ,v.DocCreatedDt
       ,v.DocStatusCd
       ,v.DocSignedNm
       ,v.DocCreatorNm
       ,v.DocLastSavedDt
       ,v.MHODesc
       ,v.RateType
       ,v.MinutesRequired
       ,v.BillingUnitRate
       ,v.SupervisorUnitRate
       ,v.ServiceCodeGrpDesc
       ,v.ServiceLocation
       ,v.ServiceLocationBillingCd
       ,NULL AS PhysComments
       ,NULL AS  InfectionControl
       ,v.NonServiceMinutes
       FROM v_ProgressNotesOP v
       INNER JOIN FD__TFS_FACE_SHEET FS  ON v.TFSClientNumber = FS.TFSClientNumber
       WHERE ServiceDt > = @BeginDate AND ServiceDt < @EndDate
       ) as AliasName
WHERE DocCreatorNm = @TherapistName
AND TFSProgramInNote = ISNULL(@ProgramOrCampus,'')
AND  ServiceCodeGrpDesc=ISNULL(@ServiceCodeGroup,'')        

END

Notice that I changed things around a bit. All for the better. Best part is, no temp table.

Let me know if you have any questions regarding the changes I made.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

AlienHost · Nov 7, 2008

George, Thank you for the more streamlined code! Also, I removed *all* the "go"s and removed the two extra "insert into"s that I had in the union query and I'm down to an error near "not" which I can handle. Thanks so much for your help!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Create Proc won't recognize declared variables

AlienHost

Programmer

jbenson001

Programmer

gmmastros

Programmer

AlienHost

Programmer

gmmastros

Programmer

gmmastros

Programmer

AlienHost

Programmer

Similar threads

Part and Inventory Search

Sponsor