Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
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
Gethyn
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
sasiparupudi1
Master III
Master III

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

MM.DD.YYYY hh:mm:ss

?

Gethyn
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.

sasiparupudi1
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)

Gethyn
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.

sasiparupudi1
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
Author

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
Author

The attempt with statement

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


generates the following syntax error:

Logging3.png

Gethyn
Creator
Creator

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

sasiparupudi1
Master III
Master III

try

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