"star" wrote:

> Hello
> I have written this stored procedure but I am getting following error.
>
>
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1'.
> Server: Msg 50000, Level 16, State 1, Procedure CopyDataArchive, Line
> 24
> Error occured while copying data to December
>
> Here is the code for stored procedure:
> CREATE PROC dbo.CopyDataArchive AS
> BEGIN
>     SET nocount ON
>     Declare @CutOffDate datetime
>     SET @CutOffDate ='2005-12-11'
>     declare @tablename as varchar(20)
>     set @tablename = datename(month, @CutOffDate)
>     Declare @fdgm datetime
>     Declare @fdnm datetime
>     SET @fdgm =  DATEADD(mm, DATEDIFF(mm,0,@CutOffDate), 0)
>     SET @fdnm=DATEADD(mm,0,DATEADD(mm, DATEDIFF(m,0,@CutOffDate)+1, 0))
>
>     Print cast(@fdgm as varchar(20)) + 'firstdaygivenmonth'
>     print cast(@fdnm as varchar(20)) + 'firstdaynextmonth'
>
>
>     BEGIN TRAN
>         EXEC(' INSERT INTO  dbo.' + @tablename  + '  SELECT * FROM dbo.Events
> WHERE TimeGenerated>='+ @fdgm  +  ' and TimeGenerated< = '+@fdnm)
>
>         IF @@ERROR <> 0
>         BEGIN
>             ROLLBACK TRAN
>             RAISERROR ('Error occured while copying data to %s', 16, 1,
> @tablename)
>             RETURN -1
>         END
>
>         DELETE dbo.Events WHERE TimeGenerated>=@fdgm and TimeGenerated<
> =@fdnm
>
>         IF @@ERROR <> 0
>         BEGIN
>             ROLLBACK TRAN
>             RAISERROR ('Error occured while deleting data from dbo.Orders', 16,
> 1)
>             RETURN -1
>         END
>     IF @@TRANCOUNT > 0
>     BEGIN
>         COMMIT TRAN
>         RETURN 0
>     END
> END
> GO
>
>
> Thanks in advance for the help.
>
>

>Thanks for the reply: This is what I got!!
>
>As Mark said I corrected the space between < and = still getting the
>same error.
>So it is with the quotes finally but how do I add those quotes to that
>stmt?
>
>This is the SQL stmt that is being generated
>INSERT INTO  dbo.December  SELECT * FROM dbo.Events WHERE
>TimeGenerated>=Dec  1 2005 12:00AM and TimeGenerated<= Jan  1 2006
>12:00AM
>
>As Michael mentioned quotes are missing from the dates. So I put the
>extra quotes as he montioned and got this error:
>
>Syntax error converting datetime from character string.
>
>So I did cast to varchar ie., ' INSERT INTO  dbo.' + @tablename  + '
>SELECT * FROM dbo.Events WHERE TimeGenerated>='''+ cast(@fdgm as
>varchar(20))  +  ''' and TimeGenerated< = '''+ cast(@fdnm as
>varchar(20))+''''
>
>I get this error:
>Server: Msg 208, Level 16, State 1, Line 1
>Invalid object name 'dbo.December'.
>Server: Msg 50000, Level 16, State 1, Procedure CopyDataArchive1, Line
>23
>Error occured while copying data to December
> INSERT INTO  dbo.December  SELECT * FROM dbo.Events WHERE
>TimeGenerated>='Dec  1 2005 12:00AM' and TimeGenerated< = 'Jan  1 2006
>12:00AM'