Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Creator
Creator

Hi Thomas,

Use this instead:

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

View solution in original post

19 Replies
Master III
Master III

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

MM.DD.YYYY hh:mm:ss

?

Creator
Creator

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.

Master III
Master III

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)

Creator
Creator

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.

Master III
Master III

it has to be like this for a single parameter

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

Not applicable

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

The attempt with statement

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


generates the following syntax error:

Logging3.png

Creator
Creator

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

Master III
Master III

try

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