Incorrect Syntax Creating Stored Proc 1

[raven4]

I am trying to create a stored proc to be used to insert values into a table. This is my stored proc:

USE [My_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_TC_MyProc]
@MemberID varchar(10),
@MemberOption varchar(50)
AS
BEGIN

SET NOCOUNT ON;

IF @MemberOption = 'ACT'

INSERT INTO TABLENAME (ID,PRODUCT_CODE,PROD_TYPE,BEGIN_DATE,BILL_DATE,BILL_AMOUNT,BALANCE,
STATUS,COPIES,BILL_BEGIN,BILL_THRU,BILL_COPIES,DATE_ADDED,LAST_UPDATED,UPDATED_BY)
VALUES
(@MemberID,'ACT','DUES', cast('1/1/'+cast(year(getdate()) as varchar) as datetime),getdate(),510,510,
'A',1,cast('1/1/'+cast(year(getdate()) as varchar) as datetime),
CASE
when month(getdate()) between 7 and 12 then cast('12/31/'+cast((year(getdate())+1) as varchar) as datetime)
when month(getdate()) between 1 and 6 then cast('12/31/'+ cast(year(GETDATE()) as varchar) as datetime)
END,
1,getdate(),getdate(),'Value')
INSERT INTO TABLENAME (ID,PRODUCT_CODE,PROD_TYPE,BEGIN_DATE,BILL_DATE,BILL_AMOUNT,BALANCE,
STATUS,COPIES,BILL_BEGIN,BILL_THRU,BILL_COPIES,DATE_ADDED,LAST_UPDATED,UPDATED_BY)
VALUES
(@MemberID,'ETHICS','SEC', cast('1/1/'+cast(year(getdate()) as varchar) as datetime),getdate(),25,25,
'A',1,cast('1/1/'+cast(year(getdate()) as varchar) as datetime),
CASE
when month(getdate()) between 7 and 12 then cast('12/31/'+cast((year(getdate())+1) as varchar) as datetime)
when month(getdate()) between 1 and 6 then cast('12/31/'+ cast(year(GETDATE()) as varchar) as datetime)
END,
1,getdate(),getdate(),'Value')
INSERT INTO TABLENAME (ID,PRODUCT_CODE,PROD_TYPE,BEGIN_DATE,BILL_DATE,BILL_AMOUNT,BALANCE,
STATUS,COPIES,BILL_BEGIN,BILL_THRU,BILL_COPIES,DATE_ADDED,LAST_UPDATED,UPDATED_BY)
VALUES
(@MemberID,'ACAD_INFORM','SEC', cast('1/1/'+cast(year(getdate()) as varchar) as datetime),getdate(),25,25,
'A',1,cast('1/1/'+cast(year(getdate()) as varchar) as datetime),
CASE
when month(getdate()) between 7 and 12 then cast('12/31/'+cast((year(getdate())+1) as varchar) as datetime)
when month(getdate()) between 1 and 6 then cast('12/31/'+ cast(year(GETDATE()) as varchar) as datetime)
END,
1,getdate(),getdate(),'Value')

No matter how many insert statements I include, whether one or 10, I get the following error on the last VALUES line when trying to create the proc:

Incorrect syntax near ')'.

If I pull the insert statements out and supply a value for the @MemberID variable and just run it as a query it works properly.

Thanks.

 

[markros]

Looks like you insert the same date values, so try

declare @BeginDate datetime, @BillBegin_Date datetime, @Bill_Thru datetime

Calculate these dates and then use them instead in your insert statements - they will become easier to read too.

 

[raven4]

Thank you for that tip. Actually, there will be several more statements and those dates will vary so I can't set those variables for everything.

I'm still getting the error.

 

[markros]

Post your new script after you simplified it. Also, when you get an error, double click on it, it should show you the exact statement with the error.

 

[hmckillop]

I think you need an END for the procedure BEGIN so

USE [My_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_TC_MyProc] 
    @MemberID varchar(10),
    @MemberOption varchar(50)
AS
BEGIN

    SET NOCOUNT ON;

IF @MemberOption = 'ACT'

                INSERT INTO TABLENAME (ID,PRODUCT_CODE,PROD_TYPE,BEGIN_DATE,BILL_DATE,BILL_AMOUNT,BALANCE,
                STATUS,COPIES,BILL_BEGIN,BILL_THRU,BILL_COPIES,DATE_ADDED,LAST_UPDATED,UPDATED_BY)
                VALUES 
                    (@MemberID,'ACT','DUES', cast('1/1/'+cast(year(getdate()) as varchar) as datetime),getdate(),510,510,
                    'A',1,cast('1/1/'+cast(year(getdate()) as varchar) as datetime),
                        CASE 
                        when month(getdate()) between 7 and 12 then cast('12/31/'+cast((year(getdate())+1) as varchar) as datetime)
                        when month(getdate()) between 1 and 6 then cast('12/31/'+ cast(year(GETDATE()) as varchar) as datetime)
                    END,
                    1,getdate(),getdate(),'Value')
                INSERT INTO TABLENAME (ID,PRODUCT_CODE,PROD_TYPE,BEGIN_DATE,BILL_DATE,BILL_AMOUNT,BALANCE,
                STATUS,COPIES,BILL_BEGIN,BILL_THRU,BILL_COPIES,DATE_ADDED,LAST_UPDATED,UPDATED_BY)
                VALUES
                (@MemberID,'ETHICS','SEC', cast('1/1/'+cast(year(getdate()) as varchar) as datetime),getdate(),25,25,
                    'A',1,cast('1/1/'+cast(year(getdate()) as varchar) as datetime),
                        CASE 
                        when month(getdate()) between 7 and 12 then cast('12/31/'+cast((year(getdate())+1) as varchar) as datetime)
                        when month(getdate()) between 1 and 6 then cast('12/31/'+ cast(year(GETDATE()) as varchar) as datetime)
                    END,
                    1,getdate(),getdate(),'Value')
                INSERT INTO TABLENAME (ID,PRODUCT_CODE,PROD_TYPE,BEGIN_DATE,BILL_DATE,BILL_AMOUNT,BALANCE,
                STATUS,COPIES,BILL_BEGIN,BILL_THRU,BILL_COPIES,DATE_ADDED,LAST_UPDATED,UPDATED_BY)
                VALUES
                (@MemberID,'ACAD_INFORM','SEC', cast('1/1/'+cast(year(getdate()) as varchar) as datetime),getdate(),25,25,
                    'A',1,cast('1/1/'+cast(year(getdate()) as varchar) as datetime),
                        CASE 
                        when month(getdate()) between 7 and 12 then cast('12/31/'+cast((year(getdate())+1) as varchar) as datetime)
                        when month(getdate()) between 1 and 6 then cast('12/31/'+ cast(year(GETDATE()) as varchar) as datetime)
                    END,
                    1,getdate(),getdate(),'Value')

END --you didnt have this posted

"I'm living so far beyond my income that we may almost be said to be living apart