here is the problem..
When this procedure runs I am supposed to get 2 output parameters.
When an insert statement is going to generate duplicate names I get 1
for @checker and 3 for @insertid. When the Insert statement runs
properly I am supposed to get 2 for @checker and an actual identity
for @insertid.
When using the code below, the part where the insert statement is
unsuccessful works just as desired, however the part where it inserts
the record returns zeros for both parameters.
When the stored procedure is run using query analyzer it performs
correctly in both instances.
Here is my PHP code
$cmd = new COM("ADODB.Command", NULL, CP_UTF8);
$cmd->CommandText = "STP_insertartist";
$cmd->CommandType = 4;
$cmd->ActiveConnection = $conn;
$par=$cmd->Parameters;
$par->Append($cmd->CreateParameter("@firstname",200,1,50,$_POST["txtfname"]));
$par->Append($cmd->CreateParameter("@lastname",200,1,50,$_POST["txtlname"]));
$par->Append($cmd->CreateParameter("@livedate1",3,1,9,$begdate));
$par->Append($cmd->CreateParameter("@livedate2",3,1,9,$enddate));
$par->Append($cmd->CreateParameter("@style",200,1,300,$_POST["txtstyle"]));
$par->Append($cmd->CreateParameter("@datedescription",200,1,75,$date text));
$par->Append($cmd->CreateParameter("@insertid",3,2,4));
$par->Append($cmd->CreateParameter("@checker",3,2,4));
$rec=$cmd->Execute;
$invid=$cmd->Parameters["@insertid"]->Value;
$check=$cmd->Parameters["@checker"]->Value;
echo("Check: ".$check." Invid: ".$invid);
here is my SQL Stored Procedure:
CREATE procedure STP_insertartist
(@firstname nvarchar(50), @lastname nvarchar(50), @livedate1 int,
@livedate2 int, @style varchar(300), @datedescription varchar(75),
@insertid int OUTPUT, @checker int output)
as
declare @check int
select @check = artistid from artist where firstname = @firstname and
lastname = @lastname
If @check is null
begin
insert into artist (firstname, lastname, livedate1, livedate2, style,
datedescription)
values(@firstname, @lastname, @livedate1, @livedate2, @style,
@datedescription)
set @insertid = scope_identity()
set @checker = 2
end
If @check is not null
begin
set @checker = 1
set @insertid = 3
end
GO
thanks
-Jim