Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    10

    Unanswered: Multiple values in a stored procedure with single parameter.

    I have the following stored procedure. the date parameter(BATCH_RUN_DATE) generates html reports for that specific date. I have to produce these reports for few months and would like to avoid entering the date parameter everytime.

    I would like to input a range for the BATCH_RUN_DATE to produce all these daily reports in one go.
    Any help will be appreciated.

    Thanks




    DECLARE @RC int
    DECLARE @BATCH_RUN_DATE datetime
    SELECT @BATCH_RUN_DATE = '27 Aug 2011'
    EXEC @RC = [PAS2K_DB_L1].[dbo].[PRC_BX_BUSOBJ_BATCH] @BATCH_RUN_DATE
    DECLARE @PrnLine nvarchar(4000)
    PRINT 'Stored Procedure: PAS2K_DB_L1.dbo.PRC_BX_BUSOBJ_BATCH'
    SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
    PRINT @PrnLine

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Multiple values in a stored procedure with single parameter

    Could you build a batch script using a WHILE loop (as follows)?

    Code:
    DECLARE
      @RC int
     ,@BATCH_RUN_DATE datetime
     ,@BATCH_END_DATE datetime
     ,@PrnLine NVARCHAR(4000)
    
    SELECT
      @BATCH_RUN_DATE = '27 Aug 2011'
     ,@BATCH_END_DATE = '27 Nov 2011'
    
    WHILE (@BATCH_RUN_DATE <= @BATCH_END_DATE)
    BEGIN
    
      EXEC @RC = [PAS2K_DB_L1].[dbo].[PRC_BX_BUSOBJ_BATCH] @BATCH_RUN_DATE
    
      PRINT 'Stored Procedure: PAS2K_DB_L1.dbo.PRC_BX_BUSOBJ_BATCH'
      SET @PrnLine = '	Return Code = ' + CONVERT(NVARCHAR ,@RC)
      PRINT @PrnLine
    
      SET @BATCH_RUN_DATE = DATEADD(DD,1,@BATCH_RUN_DATE)
    END

  3. #3
    Join Date
    Aug 2011
    Posts
    10

    RE: Multiple values in a stored procedure with single parameter

    Thanks for the reply.
    I have run the script for range 20 OCt 2011 to 24 Oct 2011. and it has created respective folders for the reports but it has totaly ignored the date range and has populated all the folders with the same set of reports. The reports generated are for either 27th or 28th of October (This is hard coded in the sp.

    Any help is appreciated.

  4. #4
    Join Date
    Sep 2011
    Posts
    71
    Could you try this method to pass multiple values using one parameter
    DECLARE @IDs varchar(100)
    SELECT @IDs = '429,446,552,1001, 332 , 471'
    --Any IDs as an example


    SELECT Convert(Int, NullIf(SubString(',' + @IDs +
    ',' , ID , CharIndex(',' , ',' + @IDs + ',' , ID) -
    ID) , '')) AS IDList
    FROM tblToolsStringParserCounter
    WHERE ID <= Len(',' + @IDs + ',') AND SubString(',' +
    @IDs + ',' , ID - 1, 1) = ','
    AND CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •