Software Voices

Create proc sp_AsManyAsYouLike as

set nocount on Select myInt * 1000 from #tempParam

go

$cmd->{ActiveConnection} = $conn;

$cmd->{CommandType} = 1;

$cmd->{CommandText} = "set nocount on Create table #tempParam (MyInt int)\n";

$cmd->{CommandText} = $cmd->{CommandText} . "Insert #tempParam Select 1\n";

$cmd->{CommandText} = $cmd->{CommandText} . "Insert #tempParam Select 2\n";

$cmd->{CommandText} = $cmd->{CommandText} . "Exec sp_AsManyAsYouLike";



$WScript->Echo($cmd->CommandText . "\n");

    

$RS = $cmd->Execute();



while(!$RS->EOF){

$WScript->Echo($RS->Fields(0)->value);

$RS->MoveNext;}

set nocount on Create table #tempParam (MyInt int)

Insert #tempParam Select 1

Insert #tempParam Select 2

Exec sp_AsManyAsYouLike



1000

2000

Create proc sp_OutputMyIdent (@myOutputIdent int output)

as

set nocount on

Begin transaction 

    Insert myIdentTable Select 'foo'

    Select @myOutputIdent = @@Identity

rollback transaction

go

myCommand.CommandText = "sp_OutputMyIdent";

myCommand.CommandType = adCmdStoredProc;

myCommand.ActiveConnection = myConnection;        

myCommand.Parameters("@myOutputIdent") = 0;        

WScript.Echo("Before:  " + myCommand.Parameters("@myOutputIdent"));

myCommand.Execute();        

WScript.Echo("After:  " + myCommand.Parameters("@myOutputIdent"));

Before:  0

After:  1

Create proc sp_OutputMyIdent (@myOutputIdent int output)

as

set nocount on

Begin transaction 

    Insert myIdentTable Select 'foo'

    Select @myLocalVar = @@Identity

    RaisError ( N'Uh Oh!', 16, 1 )

rollback transaction 

go



try

{

myCommand.CommandText = "sp_OutputMyIdent";

myCommand.CommandType = adCmdStoredProc;

myCommand.ActiveConnection = myConnection;        

myCommand.Parameters("@myOutputIdent") = 0;        

WScript.Echo("Before:  " + myCommand.Parameters("@myOutputIdent"));

myCommand.Execute();

}

catch(e){ WScript.Echo(e.description) }

WScript.Echo("After: " + myCommand.Parameters("@myOutputIdent"));

Before:  0

Uh Oh!

After:  2

Create proc sp_OutputMyIdent (@myOutputIdent int output)

as

set nocount on

Declare @myLocalVar int

Begin transaction 

    Insert myIdentTable Select 'foo'

    Select @myLocalVar = @@Identity

    RaisError ( N'Uh Oh!', 16, 1 )

commit transaction 

Select @myOutputIdent = @myLocalVar

go

Set XACT_ABORT On

Begin Transaction

RaisError ( N'Uh Oh!', 16, 1 )

If @@Error <> 0 

begin print 'My rollback' rollback transaction end

Msg 50000, Level 16, State 1, Line 3

Uh Oh!

My rollback

Set XACT_ABORT On

Begin Transaction

update dbo.TestXACT1 set mySmallInt = 0 where myInt = 1

print @@TranCount

RaisError ( N'Uh Oh!', 16, 1 )

If @@Error <> 0 

begin print @@TranCount print 'My rollback' rollback transaction end

(1 row(s) affected)

1

Msg 50000, Level 16, State 1, Line 5

Uh Oh!

1

My rollback

Set XACT_ABORT On

Begin Transaction

update dbo.TestXACT1 set mySmallInt = 32768 -- clearly not small

 where myInt = 1

print @@TranCount

If @@Error <> 0 

begin 

    Print 'Couldn''t update that myInt 1' 

    RaisError ( N'Oh this is bad!  Alert the troops!', 16, 1 ) with log 

    rollback transaction

end

Msg 220, Level 16, State 1, Line 3

Arithmetic overflow error for data type smallint, value = 32768.

Create procedure sp_TestXACT1 as 

update dbo.TestXACT1 set mySmallInt = 32768 -- clearly not small

where myInt = 1

return -1

go



Set XACT_ABORT On

Begin Transaction

Declare @RetCode int

update dbo.TestXACT1 set mySmallInt = 0 

 where myInt = 1

print @@TranCount

Exec @RetCode = sp_TestXACT1

print @RetCode

If @RetCode <> 0 

begin 

    RaisError ( N'Badness happened when I called sp_TestXACT1!!', 16, 1 ) with log 

    rollback transaction

end

(1 row(s) affected)

1

Msg 220, Level 16, State 1, Procedure sp_TestXACT1, Line 3

Arithmetic overflow error for data type smallint, value = 32768.