Exec a Stored Procedure with a Datetime Parameter

[#1]

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

[sasiparupudi1]

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

MM.DD.YYYY hh:mm:ss

?

[Gethyn]

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]

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]

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]

it has to be like this for a single parameter

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

[#7]

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:

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:

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

[#8]

The attempt with statement

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

generates the following syntax error:

[Gethyn]

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

[sasiparupudi1]

try

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