multiple transaction ?

I have a question regarding multiple transactions. I
would like to create a main stored procedure that calls
several other stored procedures. I would like to use
transactions when I write all of my SP's but am unsure
how it would works. I am running into problems when a
rollback occurs when i have multiple transactions. Is
there a standard way of setting up a commit/rollback
procedure in all SP's? I always use the following but it
does not seem to work correctly.
BEGIN TRANSACTION
IF @.ErrorCount = 0
COMMIT TRANSATION
ELSE
ROLLBACK TRANSACTION
Do i need something in here to look at the transaction
count?
Any help would be appreciated.
Thanks
B.A.I have to assume that @.ErrorCount is something of your doing so I will also
assume it is getting this correctly. When you issue a ROLLBACK, regardless
of where it's issued, all teh open transactions will be rolled back. So you
want to test to see if @.@.TRANCOUNT is greater than 0 before issuing the
rollbck otherwise you will get an error.
Andy
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:014c01c34f12$4d4b6c20$a501280a@.phx.gbl...
> I have a question regarding multiple transactions. I
> would like to create a main stored procedure that calls
> several other stored procedures. I would like to use
> transactions when I write all of my SP's but am unsure
> how it would works. I am running into problems when a
> rollback occurs when i have multiple transactions. Is
> there a standard way of setting up a commit/rollback
> procedure in all SP's? I always use the following but it
> does not seem to work correctly.
> BEGIN TRANSACTION
> IF @.ErrorCount = 0
> COMMIT TRANSATION
> ELSE
> ROLLBACK TRANSACTION
> Do i need something in here to look at the transaction
> count?
> Any help would be appreciated.
> Thanks
> B.A.
>

Multiple resultSet from SPs

Hi,
Question regarding displaying data on Datagrid. it works fine
I have 4 SP's the results of which I feel are too much overhead. so I put
all the 4 queries into as single STP and it runs successfully but only the
first one is bound for eg:
Create PROCEDURE dbo.Sample
(
@.id int,
}
as
Select * from Sample1 where id = @.id
Select * from Sample2 where id = @.id
GO
why does it not append/display the entire resultset?
Thanks,
Stephen
[P.S: Read a Post that "SPs Operate on a single rowset from each query"
Is it True?]RS (both RS 2000 and RS 2005) only support a single resultset. You can't do
what you want to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen" <stephen_jn@.hotmail.com> wrote in message
news:uZy9Ky%234FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Question regarding displaying data on Datagrid. it works fine
> I have 4 SP's the results of which I feel are too much overhead. so I put
> all the 4 queries into as single STP and it runs successfully but only the
> first one is bound for eg:
> Create PROCEDURE dbo.Sample
> (
> @.id int,
> }
> as
> Select * from Sample1 where id = @.id
> Select * from Sample2 where id = @.id
> GO
> why does it not append/display the entire resultset?
> Thanks,
> Stephen
> [P.S: Read a Post that "SPs Operate on a single rowset from each query"
> Is it True?]
>|||If the column data types and number of columns are the same you can use a
union to construct a single result set ie
select * from a
UNION ALL
Select * from b
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Stephen" wrote:
> Hi,
> Question regarding displaying data on Datagrid. it works fine
> I have 4 SP's the results of which I feel are too much overhead. so I put
> all the 4 queries into as single STP and it runs successfully but only the
> first one is bound for eg:
> Create PROCEDURE dbo.Sample
> (
> @.id int,
> }
> as
> Select * from Sample1 where id = @.id
> Select * from Sample2 where id = @.id
> GO
> why does it not append/display the entire resultset?
> Thanks,
> Stephen
> [P.S: Read a Post that "SPs Operate on a single rowset from each query"
> Is it True?]
>
>|||Hi Bruce and Wayne
Thanks for the info.
Stephen
"Stephen" <stephen_jn@.hotmail.com> wrote in message
news:uZy9Ky%234FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Question regarding displaying data on Datagrid. it works fine
> I have 4 SP's the results of which I feel are too much overhead. so I put
> all the 4 queries into as single STP and it runs successfully but only the
> first one is bound for eg:
> Create PROCEDURE dbo.Sample
> (
> @.id int,
> }
> as
> Select * from Sample1 where id = @.id
> Select * from Sample2 where id = @.id
> GO
> why does it not append/display the entire resultset?
> Thanks,
> Stephen
> [P.S: Read a Post that "SPs Operate on a single rowset from each query"
> Is it True?]
>