Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Exec a Stored Procedure with a Datetime Parameter

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';

1 Solution

Accepted Solutions
Gethyn
Contributor

Re: Exec a Stored Procedure with a Datetime Parameter

Hi Thomas,

Use this instead:

LET loadstart = TimeStamp(DayStart(Now()-1), 'MM.DD.YYYY hh:mm:ss');

19 Replies
sasiparupudi1
Honored Contributor III

Re: Exec a Stored Procedure with a Datetime Parameter

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

MM.DD.YYYY hh:mm:ss

?

Gethyn
Contributor

Re: Exec a Stored Procedure with a Datetime Parameter

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.

sasiparupudi1
Honored Contributor III

Re: Exec a Stored Procedure with a Datetime Parameter

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)

Gethyn
Contributor

Re: Exec a Stored Procedure with a Datetime Parameter

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.

sasiparupudi1
Honored Contributor III

Re: Exec a Stored Procedure with a Datetime Parameter

it has to be like this for a single parameter

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

Not applicable

Re: Exec a Stored Procedure with a Datetime Parameter

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?


Not applicable

Re: Exec a Stored Procedure with a Datetime Parameter

The attempt with statement

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


generates the following syntax error:

Logging3.png

Gethyn
Contributor

Re: Exec a Stored Procedure with a Datetime Parameter

That looks like the wrong number of ' as you have three of them?

sasiparupudi1
Honored Contributor III

Re: Exec a Stored Procedure with a Datetime Parameter

try

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

Community Browser