Help: Getting errors in Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help: Getting errors in Stored procedure

Author

Topic

Nav522
Starting Member

27 Posts

Posted - 2010-02-18 : 17:21:39

Hello Folks,
with the help of this forum i got to get these dates done. But when i tried to execute the procedure am getting back with errors. Can anyone please throw some light on this. thanks a lot

USE [medcap]
GO
/****** Object: StoredProcedure [dbo].[rptCancelledClaims_test] Script Date: 02/12/2010 10:19:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[rptCancelledClaims_test] (
--@StartDate datetime,
--@EndDate datetime,
@Payer numeric,
@Product numeric,
@ActionCode numeric,
@Frequency varchar(20) )
AS
/* Dates Start*/

v_startdate datetime;
v_enddate datetime;

BEGIN
IF @Frequency = 'DECADE'
THEN
v_startdate= CONVERT(datetime, '20000101')
v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

ELSE IF @Frequency = 'YEARLY'
THEN
v_startdate= dateadd(year, datediff(year, 0, getdate()), 0)
v_enddate= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

ELSE @Frequency = 'MONTHLY'
THEN
v_startdate =dateadd(Month, datediff(Month, 0, getdate())-1, 0)
v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
END IF

END

/* Dates End*/

Errors:
Msg 170, Level 15, State 1, Procedure rptCancelledClaims_test, Line 12
Line 12: Incorrect syntax near 'v_startdate'.
Msg 156, Level 15, State 1, Procedure rptCancelledClaims_test, Line 17
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure rptCancelledClaims_test, Line 22
Incorrect syntax near the keyword 'THEN'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-02-18 : 17:43:44

That is a real mess

.
Not sure if these changes are what you intended but at least there aren't any syntax errors now. I assume this is partial code because this won't do anything.


USE [medcap]
GO
/****** Object: StoredProcedure [dbo].[rptCancelledClaims_test] Script Date: 02/12/2010 10:19:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[rptCancelledClaims_test] (
--@StartDate datetime,
--@EndDate datetime,
@Payer numeric,
@Product numeric,
@ActionCode numeric,
@Frequency varchar(20) )
AS
/* Dates Start*/

declare @v_startdate datetime
       ,@v_enddate datetime

BEGIN
       IF @Frequency = 'DECADE'
       begin
              select @v_startdate= CONVERT(datetime, '20000101')
                     ,@v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
       end

       ELSE IF @Frequency = 'YEARLY'
       begin
              select @v_startdate= dateadd(year, datediff(year, 0, getdate()), 0)
                     ,@v_enddate= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
       end

       ELSE if @Frequency = 'MONTHLY'
       begin
              select @v_startdate =dateadd(Month, datediff(Month, 0, getdate())-1, 0)
                     ,@v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
       end
END

/* Dates End*/

Be One with the Optimizer
TG

Nav522
Starting Member

27 Posts

Posted - 2010-02-18 : 17:53:05

Hey Thanks for getting back.Yeah i looked at the code for sometime and figured as a real mess. But anywayz i will try to explain what i wanted to do. Previously the procedure has startdate and enddate as parameters and my requirement was to get rid of those parameters and include the logic which will take care of three frequencies of dates(D,M and Y).
Thanks for ur logic but now i get the error
Invalid object name 'dbo.rptCancelledClaims_test'.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 18:09:02

Does this stored procedure already exist in the database? Otherwise you may need to CREATE it instead of ALTERing it

Nav522
Starting Member

27 Posts

Posted - 2010-02-18 : 18:16:58

My bad Thanks vijay

Nav522
Starting Member

27 Posts

Posted - 2010-02-18 : 18:38:15

Hi Am facing another trouble.Previously the Date parameters were given in the join condition as seen below in the red markings. Am not quite sure how would we change that actiondate because now i have delted the date parameters and am looking for three frequencies of Dates. Please throw some light on this.
Thanks
CREATE PROCEDURE [dbo].[rptCancelledClaims_test] (
--@StartDate datetime,
--@EndDate datetime,
@Payer numeric,
@Product numeric,
@ActionCode numeric,
@Frequency varchar(20) )
AS
/* Dates Start*/

declare @v_startdate datetime,@v_enddate datetime

BEGIN
IF @Frequency = 'DECADE'
begin
select @v_startdate= cast('20000101' as datetime)
,@v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
end

ELSE IF @Frequency = 'YEARLY'
begin
select @v_startdate= dateadd(year, datediff(year, 0, getdate()), 0)
,@v_enddate= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
end

ELSE if @Frequency = 'MONTHLY'
begin
select @v_startdate =dateadd(Month, datediff(Month, 0, getdate())-1, 0)
,@v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
end
END

/* Dates End*/

Select @Product as productID into #ppp

If @Product = -1
Begin
Delete from #ppp
Insert into #ppp Select productID from tblProducts
End
Else If @Product = 1
Begin
Insert into #ppp (productID) Values (8)
End

Select @ActionCode as actioncode
into #ccc

If @ActionCode = -1
Begin
Delete from #ccc

Insert into #ccc
Select actioncode
from tblactioncodes
where newclaimstatus = 'C'
and actioncode NOT IN (26, 324)
End
/*******************************************************************************************************/

SELECT
c.claimid,
c.claimpatientlastname,
c.claimpatientfirstname,
c.claimadmitdate,
c.claimdischdate,
pv.providername,
c.claimpatientid,
c.claimpatientaccountnumber,
s.stafffirst,
s.stafflast,
c.claimtotalbill,
cp.claimproductproviderauditfees,
a.actioncode,
ac.actionname,
a.actiondate,
c.clientid,
cl.clientname,
cp.productid,
p.productname,
(SELECT TOP 1 actiondate
FROM tblactions
WHERE claimid = c.claimid
and productid = cp.productid
and actioncode = 129
and actionoverriddenflag = 0
and actiondeletedflag = 0
ORDER BY dateinserted DESC) AS AuditDate,
(SELECT TOP 1 CASE actioncode
WHEN 86 THEN 'Y'
ELSE 'N'
END
FROM tblactions
WHERE claimid = c.claimid
and productid = cp.productid
and actionoverriddenflag = 0
and actiondeletedflag = 0
ORDER BY actiondate DESC) AS Prep,
claimclientbusunit,
claimclientlobdesc,
claimclientlob
From
tables
Where
a.actioncode IN (Select actioncode from #ccc)
and a.actionoverriddenflag = 0
and a.actiondeletedflag = 0
and a.actiondate between @StartDate and @EndDate and (c.clientid = @Payer or @Payer = -1)
and a.productid IN (Select productid from #ppp)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 00:03:47

just replace it by new variables
ie.

and a.actiondate between @v_startdate and @v_enddate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources