4

I have a stored procedure that looks like : 

CREATE procedure [dbo].[SP_EXEC] 
AS
   DECLARE @DATE AS varchar(50)

   SET @DATE = (SELECT TOP(1) CONVERT(VARCHAR, YEAR(DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1, GETDATE())), '01/01/2000'))) + RIGHT('00'+ CONVERT(VARCHAR, MONTH(DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1, GETDATE())), '01/01/2000'))), 2) + RIGHT('00' + CONVERT(VARCHAR, DAY(DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1, GETDATE())), '01/01/2000'))), 2)
                FROM produit)

    EXEC SP_DELETE_lIGNE_MOIS_EN_COURS  @DATE

The stored procedure works fine, my goal is to do a loop of a range of date from 2012/03/01 to the current date. How to update my stored procedure to do an update on history ?

Improve this question
2
  • Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! Commented Jul 9, 2015 at 12:41
  • What is that crazy top 1 query trying to do??? Also, since the values are not coming from the table there is no point in selecting from a table, just select the values. And why oh why are you using what appears to be a date in a varchar?? You need to read up on tally tables and start thinking in sets instead of thinking in loops. sqlservercentral.com/articles/T-SQL/62867 Commented Jul 9, 2015 at 13:39

1 Answer 1

Reset to default
4

There's really not enough information here to be sure what you need... but first few observations:

You have a select top 1 from a table, but you're not selecting anything from it

You have quite complex select, which looks like it's the same as this:

convert(dateadd(month, DATEDIFF(month, 0, getdate()), 0), 112)

Which is the first day of current month in YYYYMMDD format

You're assigning that to a varchar(50) -- and passing as a parameter to a procedure. Is the procedure actually using a date or datetime for this?

So, my guess is that you actually need this:

declare @date date

set @date = '20120301'

while (@date < getdate()) begin

    exec SP_DELETE_lIGNE_MOIS_EN_COURS @date

    set @date = dateadd(month, 1, @date)
end
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

the parameter entered in the stored procedure is a type varchar(50) ,
Then you'll need to have convert(varchar(50), @date, 112)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.