|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
September 15th, 2004, 12:49 PM
|
|||
|
|||
stored Procedure
Does anyone see anything wrong with this statement, I keep getting an Incorrect Syntax message must declare the variable
Code:
CREATE PROCEDURE [insert_Corovan_Table_1] @pdBegin DATETIME = TerminationDate (BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102)) @pdEnd DATETIME= NULL AS IF @pdBegin IS NULL SET @pdBegin = DateAdd(year, -2, GetDate()) IF @pdEnd IS NULL SET @pdEnd = DateAdd(year, 1,@pdBegin) AS INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table] ( [TM #], [FirstName], [LastName], [SS #], [TerminationDate], [Voluntary or Involuntary], [Notes]) SELECT ([TM#], [FirstName], [LastName], [SS #], [TerminationDate], [Voluntary_or_Involuntary], [Notes],) FROM TERMINATION GO
|
|
#3
September 15th, 2004, 12:54 PM
|
||||
|
||||
|
You have 2 "AS" statements in your code.
Code:
CREATE PROCEDURE [insert_Corovan_Table_1] @pdBegin DATETIME = TerminationDate (BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102)) @pdEnd DATETIME= NULL AS IF @pdBegin IS NULL SET @pdBegin = DateAdd(year, -2, GetDate()) IF @pdEnd IS NULL SET @pdEnd = DateAdd(year, 1,@pdBegin) AS INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table] ( [TM #], [FirstName], [LastName], [SS #], [TerminationDate], [Voluntary or Involuntary], [Notes]) SELECT ([TM#], [FirstName], [LastName], [SS #], [TerminationDate], [Voluntary_or_Involuntary], [Notes],) FROM TERMINATION GO
|
|
#5
September 15th, 2004, 12:58 PM
|
||||
|
||||
|
Try this
Code:
CREATE PROCEDURE [insert_Corovan_Table_1]
@pdBegin DATETIME = TerminationDate (BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
@pdEnd DATETIME= NULL
AS
IF @pdBegin IS NULL
BEGIN
SET @pdBegin = DateAdd(year, -2, GetDate())
END
IF @pdEnd IS NULL
BEGIN
SET @pdEnd = DateAdd(year, 1,@pdBegin)
END
INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table]
([TM #],
[FirstName],
[LastName],
[SS #],
[TerminationDate],
[Voluntary or Involuntary],
[Notes])
SELECT ([TM#],
[FirstName],
[LastName],
[SS #],
[TerminationDate],
[Voluntary_or_Involuntary],
[Notes],)
FROM TERMINATION
GO
Also, I don't see a WHERE Clause in your SELECT statement. Are you meaning to pull all the records in?
|
|
#8
September 15th, 2004, 01:35 PM
|
||||
|
||||
|
okay, now I am confused.
What exactly is this, then? Code:
@pdBegin DATETIME = TerminationDate (BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
|
|
#9
September 15th, 2004, 01:42 PM
|
|||
|
|||
|
Ok
What I am trying to do is Move Data from one table to another automatically once year. I want the stored procedure to execute once a year. I want the 2003 Terms data to go to the Corovan table at the beginning of 2005. Thats what I am trying to do.
|
|
#10
September 15th, 2004, 02:00 PM
|
||||
|
||||
|
I understand that, what I don't understand is the code of yours I pasted above?
It looks to me like you are setting the value of @pdBegin to the returned results of a UDF.
|
|
#11
September 15th, 2004, 02:55 PM
|
|||
|
|||
|
I see
Yes actually thats what it looks like to me also. I inhertied this database and I'm analyzing alot of the procedures and structure this is the one I am having the most problems with
|
|
| Viewing: ASP Free Forums > Database > SQL Development > stored Procedure |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
