DBI::ODBC Stored Procedure execution

hallikpapa has asked for the wisdom of the Perl Monks concerning the following question:

I have multiple stored procedures stored in a MS SQL database. I have my connection setup fine, but having problems passing variables into it. Here is my sp call:

#execute stored procedure
my $sp = $msdbh->prepare(qq|usp_Dashboard_CallType \@start = 20080101|
+);
$sp->execute or die("$sp->errstr");
[download]

It doesn't like the date I am passing shown here:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL 
+Server]Error converting data type int to datetime.
[download]

I have two questions about this: 1) How can I pass the variables properly in the format the SQL server wants, and 2) I need to execute this stored procedure for every date starting from the beginning of the year up until yesterday. I am assuming I need to loop through the an array somehow, maybe put this and the other stored procedure executions I need to do into a subroutine and pass each date one by one to the sub, while converting it properly to the SQL datetime? I am also looking into the fact there may be a problem with what the SQL server is doing as far as conversion. But regardless, Is this correct if I want to loop thru dates in a subroutine?

my $sp = $msdbh->prepare(qq|usp_Dashboard_CallType \@start = $next_dat
+e|);
[download]

Comment on DBI::ODBC Stored Procedure execution Select or Download Code

Replies are listed 'Best First'.
Re: DBI::ODBC Stored Procedure execution by Narveson (Chaplain) on Jan 27, 2008 at 19:28 UTC
As to your first question, put single quotes around the date string: `my $sql = qq\|usp_Dashboard_CallType \@start = '20080101' \|; my $sp = $msdbh->prepare($sql);` [download]	[reply] [d/l]
Re^2: DBI::ODBC Stored Procedure execution by hallikpapa (Scribe) on Jan 27, 2008 at 19:40 UTC
That worked perfectly. Thank you. May I ask how you just know when to use single quote, double, or none? I would like to read more about that somewhere. Thanks again!	[reply]
Re^3: DBI::ODBC Stored Procedure execution by john_oshea (Priest) on Jan 28, 2008 at 13:20 UTC
As far as I'm aware, it's specified in the SQL standard - the following is lifted from Joe Celko's "SQL For Smarties" book (recommended, btw): In SQL, character strings are printable characters enclosed in single quotation marks. ... Double quotations marks are reserved for column names that have embedded spaces or that are also SQL-reserved words. Without the quotes, you're passing a number a bit larger than 20 million to the stored procedure (which is left unconverted, even though it looks like a date). With the quotes, SQL Server is clever enough to try to convert the string to a date, so it does what you mean. You can, if you want, explicitly control how the conversion gets done using `CAST`: http://msdn2.microsoft.com/en-us/library/ms141704.aspx. Hope that helps.	[reply] [d/l]
Re: DBI::ODBC Stored Procedure execution by olus (Curate) on Jan 27, 2008 at 23:06 UTC
You could use placeholders in your SQL statement. This way you will need to do the preparing once and pass the new dates to the execute. You will need to compute the dates for which the procedure should run, and pass those values in the format that is expected by the SP. `# compute the dates into array @dates #execute stored procedure my $sp = $msdbh->prepare(qq\|usp_Dashboard_CallType \@start = ?\|); foreach my $cur_date(@dates) { $sp->execute($cur_date) or die("$sp->errstr"); }` [download]	[reply] [d/l]