Assuming I have a stored procedure that contains a
return @something
statement, what's the right way to find out that value? I know I can
get the OUTPUT arguments of a stored procedure by preparing
EXEC dbo.SomeProc ?, ?, ?
and using bind_param_inout for those that were declared with OUTPUT
but if I try
EXEC ? = dbo.SomeProc ?, ?, ?
I get:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
'='. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared.
and if I use SQL Profiler to see what was passed to the server I get
something like this:
declare @p1 int
set @p1=23
declare @p5 int
set @p5=null
declare @p6 varchar(51)
set @p6=''
declare @p7 varchar(103)
set @p7=''
exec sp_prepexec @p1 output,N'@P1 int OUTPUT,@P2 int,@P3 varchar(51)
OUTPUT,@P4 varchar(103) OUTPUT',
N'EXEC @P1 OUTPUT = dbo.GetUsersDetails @P2, @P3 OUTPUT, @P4 OUTPUT
',2,@p5 output,@p6 output,@p7 output
select @p1, @p5, @p6, @p7
Notice the "OUTPUT" before the "= dbo.GetUserDetails".
Without that "OUTPUT" it would work:
----------
CREATE DATABASE DBIx_Declare_Test;
go
use DBIx_Declare_Test
go
CREATE TABLE Users (Id int not NULL PRIMARY KEY IDENTITY(1,1),
FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Email
varchar(100)
)
go
INSERT INTO Users (FirstName,LastName,Email)
values ('John','Doe','[email protected]')
go
CREATE PROCEDURE dbo.FetchUsersAndRetval
as
BEGIN
Declare @Count int;
SELECT @Count = Count(*) FROM dbo.Users;
SELECT * FROM dbo.Users ORDER BY LastName, FirstName;
return @Count;
END
go
declare @p1 int
set @p1=NULL
declare @p4 int
set @p4=NULL
exec sp_prepexec @p1 output,
N'@P2 int OUTPUT',N'EXEC @P2 = dbo.FetchUsersAndRetval',
@p4 output
select @p1, @p4
---------------------
use strict;
use DBI;
my $db = DBI->connect("dbi:ODBC:Driver=SQL
Server;Server=JobVIPeR;Database=DBIx_Declare_Test");
my $sth = $db->prepare('EXEC ? = dbo.FetchUsersAndRetval') or die;
my $retval;
$sth->bind_param_inout( 1, \$retval, 16, { TYPE => DBI::SQL_INTEGER
}) or die;
$sth->execute(); #here it errors out
while (my $row = $sth->fetchrow_arrayref) {
print "@$row\n";
}
print "\$retval=$retval\n";
----------------------
It looks like the sp_prepexec is nowhere to be found in the DBD::ODBC
sources so it's probably something that the SQL Server ODBC driver
does. Is there any way to prevent the "OUTPUT" included there? I'm
sorry, I tried, but could not find anything on the net.
I have DBI 1.607, DBD::ODBC 1.15, ActivePerl v5.8.9 build 825,
Windows Vista Home Premium SP1, SQLSRV32.dll (the ODBC driver)
6.0.6001.18000.
Thanks, Jenda
===== [email protected] === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
