
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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';
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Thomas,
Use this instead:
LET loadstart = TimeStamp(DayStart(Now()-1), 'MM.DD.YYYY hh:mm:ss');


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
is the date time in your variable is in the format required by DB?
MM.DD.YYYY hh:mm:ss
?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
it has to be like this for a single parameter
SQL EXEC 'Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp=' &chr(39) & '$(loadstart)' &chr(39)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The attempt with statement
SQL EXEC 'Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp=' &chr(39) & '$(loadstart)' &chr(39)
generates the following syntax error:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That looks like the wrong number of ' as you have three of them?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try
SQL call Linerecprod.Linerecprod.[sp_FT1HeaderData] ('$(loadstart)')

- « Previous Replies
-
- 1
- 2
- Next Replies »