19 Replies Latest reply: Sep 18, 2015 11:49 AM by Thomas Pepping RSS

    Exec a Stored Procedure with a Datetime Parameter

    Thomas Pepping

      Hi All,

       

      I try to execute a strored procedure in a Loadscript. The procedure needs a paramter @Timestamp which has the type "datetime".

      Since the @Timestamp parameter must be calculated at runtime, I try to solve this with a variable "loadstart" in the script.

      I read several hints from other discussion, but I couldn't get the correct syntax for the statement.

      Can someone help me?

       

      Syntax of my last attempt:

              LOAD loadstart;

      SQL SELECT loadstart=DATEADD(d, -1, GETDATE());

       

      LOAD Ident,

          Loop,

          RecNo() AS AutoIncrement,

          Identifier,

          Result AS Result,

          ResultBeschreibung AS Fehler,

          Result & ' - ' & ResultBeschreibung AS "Fehlercode - Text",

          WorkCount,

          Timestamp AS Zeitstempel,

          Artikel AS Artikelnummer,

          Stationskennung AS Testerkennung,

          Adapter AS Adapter,

          Nest,

          WP_Pos_Nr,

          LetzterDurchlauf;

      SQL EXEC 'Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp = "$(loadstart)" ' ;

       

      Only when typing in the timestamp directly as a string (see below) the statement works properly:

      LOAD Ident,

          Loop,

          RecNo() AS AutoIncrement,

          Identifier,

          Result AS Result,

          ResultBeschreibung AS Fehler,

          Result & ' - ' & ResultBeschreibung AS "Fehlercode - Text",

          WorkCount,

          Timestamp AS Zeitstempel,

          Artikel AS Artikelnummer,

          Stationskennung AS Testerkennung,

          Adapter AS Adapter,

          Nest,

          WP_Pos_Nr,

          LetzterDurchlauf;

      SQL EXEC Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp='09.17.2015 00:00:00';

       

        • Re: Exec a Stored Procedure with a Datetime Parameter
          Sasidhar Parupudi

          is the date time in  your variable is in the format required by DB?

          MM.DD.YYYY hh:mm:ss

           

          ?

          • Re: Exec a Stored Procedure with a Datetime Parameter
            Gethyn Owen

            When I execute a stored procedure in QlikView scripts I have to do it like this:

             

            SQL exec CLARITY.dbo.SP_GO_DRUG_DISP_AND_ADMIN '01/08/2015 00:00:00', '31/08/2015 23:59:59';

             

            So would yours work like this?

             

            SQL EXEC Linerecprod.Linerecprod.[sp_FT1HeaderData] '$(loadstart)' ;

             

            Gethyn.

            • Re: Exec a Stored Procedure with a Datetime Parameter
              Sasidhar Parupudi

              Try

              LOAD Ident,

                  Loop,

                  RecNo() AS AutoIncrement,

                  Identifier,

                  Result AS Result,

                  ResultBeschreibung AS Fehler,

                  Result & ' - ' & ResultBeschreibung AS "Fehlercode - Text",

                  WorkCount,

                  Timestamp AS Zeitstempel,

                  Artikel AS Artikelnummer,

                  Stationskennung AS Testerkennung,

                  Adapter AS Adapter,

                  Nest,

                  WP_Pos_Nr,

                  LetzterDurchlauf;

              SQL EXEC 'Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp=' &chr(39) & '$(loadstart)' &chr(39)

              • Re: Exec a Stored Procedure with a Datetime Parameter
                Gethyn Owen

                Is your parameter @Timestamp defined in the stored procedure? If so, you shouldn't need to specify the name, just pass the value as per my answer above.

                 

                Gethyn.

                  • Re: Exec a Stored Procedure with a Datetime Parameter
                    Thomas Pepping

                    The Statement

                         SQL EXEC Linerecprod.Linerecprod.[sp_FT1HeaderData] '$(loadstart)' ;

                    starts loading, but it doesn't load the defined period of time. It seems to load the whole table.


                    The LOG-Outputshows that the parameter string is empty:

                    Logging1.png


                    When starting the following statement

                         SQL EXEC Linerecprod.Linerecprod.[sp_FT1HeaderData] '2015-09-17 00:00:00'

                    the loading is correct and the LOG-Output locks as follows:

                    Logging2.png


                    It seems as iff $(loadstart) is empty, or the syntax isn't correct?


                      • Re: Exec a Stored Procedure with a Datetime Parameter
                        Sasidhar Parupudi

                        try

                         

                        SQL call Linerecprod.Linerecprod.[sp_FT1HeaderData] ('$(loadstart)')

                          • Re: Exec a Stored Procedure with a Datetime Parameter
                            Thomas Pepping

                            The suggestions still do not work.

                            I have the strong suspicion that the variable "loadstart" has no value when the stored procedure is executed.

                             

                            Is it possible that the statement to fill "loadstart" isn't executed before the stored procedure is executed?

                            Or is the value of "loadstart" not available within the script, only in the application?

                             

                            The order of the statements in the script is the following:

                                    LOAD loadstart;

                            SQL SELECT loadstart=DATEADD(d, -1, GETDATE());

                             

                            LOAD Ident,

                                Loop,

                                RecNo() AS AutoIncrement,

                                Identifier,

                                Result AS Result,

                                ResultBeschreibung AS Fehler,

                                Result & ' - ' & ResultBeschreibung AS "Fehlercode - Text",

                                WorkCount,

                                Timestamp AS Zeitstempel,

                                Artikel AS Artikelnummer,

                                Stationskennung AS Testerkennung,

                                Adapter AS Adapter,

                                Nest,

                                WP_Pos_Nr,

                                LetzterDurchlauf;

                            SQL EXEC 'Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp = "$(loadstart)" ' ;

                          • Re: Exec a Stored Procedure with a Datetime Parameter
                            Gethyn Owen

                            Hi Thomas,

                             

                            Could you try with two parameters? I nearly always use two to select a range of dates and defime the procedure along these lines.

                             

                            USE [CLARITY];

                            GO

                            SET ANSI_NULLS ON;

                            GO

                            SET QUOTED_IDENTIFIER ON;

                            GO

                            /*

                            =====================================

                            Author: Gethyn Owen

                            Create date: 07/09/2015

                            Description: Susceptibility Tests

                                          For AmSurv Report

                            =====================================

                            */

                            CREATE PROCEDURE [dbo].[SP_GO_AmSurve_SUSCEPTIBILITY] (@Start VARCHAR(50),  @End VARCHAR(50))

                            AS

                            DECLARE

                                  @StartDate  DATETIME,

                                  @EndDate    DATETIME

                            BEGIN

                             

                              -- SET NOCOUNT ON added to prevent extra result sets from

                              -- interfering with SELECT statements.

                              SET NOCOUNT ON;

                             

                             

                                  SET @StartDate = EPIC_UTIL.EFN_DIN (@Start)

                                  SET @endDate = EPIC_UTIL.EFN_DIN (@End)

                             

                             

                            SELECT DISTINCT

                                      (V_LAB_RESULTS.SPECIMEN_ID + '|') + (CAST (CLARITY_ORGANISM.ORGANISM_ID AS VARCHAR (18))) AS SORT,

                                      Cast(CLARITY_COMPONENT.ABBREVIATION as VarChar(9)) AS [Antibiotic Code],

                                    CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) AS LastVerifiedDate,

                                      CASE

                                          WHEN ZC_SUSCEPT.ABBR IS NOT NULL

                                            THEN ZC_SUSCEPT.ABBR

                                          ELSE

                                          CASE

                                            WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL

                                              THEN 'U'

                                            WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL

                                              THEN ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'

                                            WHEN ZC_SUSCEPT.ABBR IS NULL

                                              THEN ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'

                                            ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'

                                          END

                                      END AS RES_MICRO_SUSC_group

                                      FROM CLARITY.dbo.RES_MICRO_CULTURE RES_MICRO_CULTURE

                                          LEFT OUTER JOIN CLARITY.dbo.CLARITY_ORGANISM CLARITY_ORGANISM

                                              ON (RES_MICRO_CULTURE.CULT_ORG_ID = CLARITY_ORGANISM.ORGANISM_ID)

                                          LEFT OUTER JOIN CLARITY.dbo.RES_DB_MAIN RES_DB_MAIN

                                              ON (RES_DB_MAIN.RESULT_ID = RES_MICRO_CULTURE.RESULT_ID)

                                          FULL OUTER JOIN CLARITY.dbo.RES_MICRO_SUSC RES_MICRO_SUSC

                                              ON (RES_MICRO_SUSC.CULTURE_ID = RES_MICRO_CULTURE.RESULT_ID)

                                              AND (RES_MICRO_SUSC.UNIQUE_ORGANISM_ID = RES_MICRO_CULTURE.LINE)

                                    LEFT OUTER JOIN CLARITY.dbo.V_LAB_RESULTS V_LAB_RESULTS

                                        ON (RES_DB_MAIN.RESULT_ID = V_LAB_RESULTS.RESULT_ID)

                                          LEFT OUTER JOIN CLARITY.dbo.CLARITY_COMPONENT CLARITY_COMPONENT

                                              ON (RES_MICRO_SUSC.SUSC_ANTIBIOTIC_ID = CLARITY_COMPONENT.COMPONENT_ID)

                                          LEFT OUTER JOIN CLARITY.dbo.METHOD_INFO METHOD_INFO

                                              ON (RES_MICRO_SUSC.SUSC_TEST_METH_ID = METHOD_INFO.METHOD_ID)

                                          RIGHT OUTER JOIN CLARITY.dbo.SPEC_DB_MAIN SPEC_DB_MAIN

                                              ON (RES_MICRO_SUSC.SPECIMEN_ID = SPEC_DB_MAIN.SPECIMEN_ID)

                                          LEFT OUTER JOIN CLARITY.dbo.TEST_MSTR_DB_MAIN TEST_MSTR_DB_MAIN

                                              ON (RES_MICRO_SUSC.SUSC_TEST_ID = TEST_MSTR_DB_MAIN.TEST_ID)

                                          LEFT OUTER JOIN CLARITY.dbo.ZC_SUSCEPT ZC_SUSCEPT

                                              ON (RES_MICRO_SUSC.SUSC_INTERPRETATN_C = ZC_SUSCEPT.SUSCEPT_C)

                                          LEFT OUTER JOIN CLARITY.dbo.RTYPE_DB_MAIN RTYPE_DB_MAIN

                                              ON (RES_MICRO_SUSC.RESULT_TYPE_ID = RTYPE_DB_MAIN.RESULT_TYPE_ID)

                                    WHERE (RES_DB_MAIN.RES_VAL_STATUS_C = 9

                                            AND RES_MICRO_SUSC.CULTURE_ID IS NOT NULL

                                            and CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) Between @StartDate and @EndDate

                                            AND RES_MICRO_SUSC.RESULT_ID Is NOT NULL)

                                            AND CLARITY_COMPONENT.ABBREVIATION <> 'ADNR'

                                            AND CASE

                                                    WHEN ZC_SUSCEPT.ABBR IS NOT NULL

                                                      THEN ZC_SUSCEPT.ABBR

                                                    ELSE

                                                    CASE

                                                      WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL

                                                        THEN 'U'

                                                      WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL

                                                        THEN ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'

                                                      WHEN ZC_SUSCEPT.ABBR IS NULL

                                                        THEN ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'

                                                      ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'

                                                    END

                                                END <> 'U'

                             

                            END

                            GO


                            My two parameters, @Start and @End can be entered as a date, timestamp or a relative period, ie T-5 for a 5 days ago, WB-1 for first day of last week. I have a function (EPIC_UTIL.EFN_DIN)which converts them to dd/mm/yyyy hh:mm:ss and saves them as @StartDate and @EndDate and then they get applied in the where clause of the sql code.


                            The code for the function is like this:


                            USE [CLARITY];

                            GO

                            SET ANSI_NULLS ON;

                            GO

                            SET QUOTED_IDENTIFIER ON;

                            GO

                            CREATE FUNCTION [EPIC_UTIL].[EFN_DIN]

                            (

                            @i_vDateString VARCHAR(255)

                            )

                            RETURNS datetime

                            AS

                             

                            BEGIN

                              -- Variables

                              DECLARE @vDate AS varchar(255)

                              DECLARE @dDate AS datetime

                              DECLARE @vDay as varchar(255)

                              DECLARE @vMonth as varchar(255)

                              DECLARE @vYear as varchar(255)

                              DECLARE @iSecs as int

                              DECLARE @iDays as int

                              DECLARE @iInstant as bigint

                             

                              IF @i_vDateString is NULL RETURN NULL

                             

                             

                              -- Set to Upper case and deleting spaces

                              -- Be careful when entering numbers separated like "t + 2 4"

                              SET @vDate = upper(replace(@i_vDateString,' ',''))

                             

                              -- Format Checking

                              -- Format DTE.xxxxx

                              IF @vDate LIKE 'DTE.%'

                                  return DATEADD(dd,FLOOR(CONVERT(numeric(18,2),SUBSTRING(@vDate,5,LEN(@vDate)-4))),'18401231')

                             

                              -- Format DAT.xxxxx

                              IF @vDate LIKE 'DAT.%'

                                  return DATEADD(dd,121531 - CEILING(CONVERT(numeric(18,2),SUBSTRING(@vDate,5,LEN(@vDate)-4))),'18401231')

                             

                              -- Format INST.xxxxx

                              IF @vDate LIKE 'INST.%'

                                  BEGIN

                                    SET @iInstant=CONVERT(bigint,SUBSTRING(@vDate,6,LEN(@vDate)-5))

                                    SET @iDays=@iInstant/86400 --Get the days out of the instant

                                    SET @iSecs=@iInstant % 86400 --Get the seconds out of the instant that are less than a day

                                    return DATEADD(ss,@iSecs,DATEADD(dd,@iDays,'18401231'))

                                  END

                                 

                              -- Format mmddyyyy

                              IF @vDate LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

                                  return CONVERT(datetime,SUBSTRING(@vDate,1,2)+'/'+SUBSTRING(@vDate,3,2)+'/'+SUBSTRING(@vDate,5,4))

                             

                              -- Date accepted by T-SQL

                             

                             

                              IF CHARINDEX('-',@i_vDateString)=5

                                  RETURN DATEADD(dd,0, DATEDIFF(dd,0,CONVERT(datetime,@i_vDateString,120)))

                            else

                              IF ISDATE(@i_vDateString)<>0

                                  RETURN DATEADD(dd,0, DATEDIFF(dd,0,CONVERT(datetime,@i_vDateString)))

                             

                             

                             

                              -- Relative to day interval

                              IF PATINDEX('T%',@vDate) = 1 --Today

                                BEGIN

                                    SET @vDate=SUBSTRING(@vDate,2,LEN(@vDate))

                              IF @vDate LIKE '[+-]_%' -- Add or substract days

                                  RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(d,CAST(@vDate as int),getdate())))

                              ELSE IF @vDate='' RETURN DATEADD(dd,0, DATEDIFF(dd,0,getdate())) -- Today

                              ELSE  RETURN NULL

                              END

                             

                              -- Relative to time

                              IF PATINDEX ('N%',@vDate)= 1 -- Now

                                  BEGIN

                                    SET @vDate=SUBSTRING(@vDate,2,LEN(@vDate))

                                    IF @vDate LIKE '[+-]_%' --Add or substract minutes

                                        RETURN DATEADD(mi,CAST(@vDate as int),getdate())

                                    ELSE IF @vDate='' RETURN getdate() --NOW

                                    ELSE  RETURN NULL

                                  END

                             

                             

                              -- Month interval

                              -- Month BEGINNING

                              IF PATINDEX('MB%',@vDate) = 1

                                BEGIN

                                SET @dDate=DATEADD(d,-day(getdate())+1,getdate()) -- Set the beginning of the month

                                RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(m,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate)))

                              END

                             

                              -- Month Ending

                              IF PATINDEX('ME%',@vDate) = 1

                                BEGIN

                                SET @dDate=DATEADD(d,-day(getdate())+1,getdate()) -- Set the beginning of the month

                                  RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(d,-1,DATEADD(m,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)+1,@dDate))))

                              END

                             

                              IF PATINDEX('M_%',@vDate) = 1 --Month

                              RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(m,CAST(SUBSTRING(@vDate,2,len(@vDate)-1) as int),getdate())))

                             

                              -- Relative to week interval

                              IF PATINDEX('WB%',@vDate) = 1 --Week beginning

                                  BEGIN

                                    SET @dDate = getdate() + 1 - DATEPART(dw, getdate()) + CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)*7 -- Set the beginning of the week

                                    RETURN DATEDIFF(dd,0,@dDate)

                                  END

                             

                             

                              IF PATINDEX('WE%',@vDate) = 1 --Week ending

                                  BEGIN

                                    SET @dDate = getdate() + 7 - DATEPART(dw, getdate()) + CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)*7 -- Set the ending of the week

                                    RETURN DATEDIFF(dd,0,@dDate)

                                  END

                             

                             

                                IF PATINDEX('W_%',@vDate) = 1 --Week

                                  RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(ww,CAST(SUBSTRING(@vDate,2,LEN(@vDate)-1) as int),getdate())))

                             

                              -- Relative to year interval

                                IF PATINDEX('YB%',@vDate) = 1 --Year beginning

                                  BEGIN

                                    SET @dDate=CAST('01/01/' + CAST(year(GETDATE()) as varchar(4)) as datetime) -- Set the beginning of the year

                                    RETURN DATEADD(yy,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate)

                                  END

                             

                             

                              IF PATINDEX('YE%',@vDate) = 1 --Year ending

                                  BEGIN

                                    SET @dDate=CAST('01/01/' + CAST(year(GETDATE()) as varchar(4)) as datetime) -- Set the beginning of the year

                                    RETURN DATEADD(d,-1,DATEADD(yy,1+CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate))

                                  END

                             

                             

                              IF PATINDEX('Y_%',@vDate) = 1 --Year

                                  RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(yy,CAST(SUBSTRING(@vDate,2,LEN(@vDate)-1) as int),getdate())))

                             

                              -- M,D,YYYY

                              IF PATINDEX('_%,_%,_%',@vDate) = 1

                                BEGIN

                                  SET @vMonth = LEFT(@vDate,CHARINDEX(',',@vDate)-1) -- get the month

                                  SET @vDate = SUBSTRING(@vDate,LEN(@vMonth)+2,LEN(@vDate)) -- Advance the date to read

                                  SET @vDay = LEFT(@vDate,CHARINDEX(',',@vDate)-1) -- get the day

                                  SET @vYear = SUBSTRING(@vDate,CHARINDEX(',',@vDate)+1,LEN(@vDate))

                                  RETURN CONVERT(datetime,@vMonth +'/'+@vDay+'/'+@vYear)

                                END

                              -- Omit year

                            IF PATINDEX('_%/_%',@vDate) = 1 and (isdate(@vdate)=1 or isdate(@vdate+'/'+datename(yyyy,getdate()))=1)

                                  RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX('/',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX('/',@vDate)+1,len(@vDate))+'/'+datename(yyyy,getdate()))--Add the year

                              IF PATINDEX('_%._%',@vDate) = 1 and (isdate(@vdate)=1 or isdate(@vdate+'/'+datename(yyyy,getdate()))=1)

                                  RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX('.',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX('.',@vDate)+1,len(@vDate))+'/'+ datename(yyyy,getdate()))--Add the year

                              IF PATINDEX('_%,_%',@vDate) = 1 and (isdate(replace(@vdate,',','/'))=1 or isdate(replace(@vdate,',','/')+'/'+datename(yyyy,getdate()))=1)

                                  RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX(',',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX(',',@vDate)+1,len(@vDate))+'/'+ datename(yyyy,getdate()))--Add the year

                              -- Default

                              ELSE

                                RETURN NULL

                             

                              RETURN @dDate

                            END

                             

                             

                            GO


                            I would then have this in QlikView:


                            SQL EXEC CLARITY.dbo.SP_GO_AmSurv_SUSCEPTIBILITY 'MB-1', 'ME-1';


                            This would then run the procedure and bring in the data for the whole of the last calendar month.


                            Could you do similar and have your @Start parameter as the earliest time that you want to start from perhaps YB-5 for the year start five years ago and have the @End as T-1 for yesterday?


                            Gethyn.