how does ado.net read values from a sql server 2005 output clause.

microsoft.public.dotnet.framework.adonet

28 Oct 2006 10:42 PM

smHaig

I would like to use the really useful output clause in a stored procedure.
How would I capture these values. Are they returned like a select statement
into a dataset?
Here is a sample sp. The three values would be the values in the OUTPUT
statement.

I have not found much on this topic and I am wondering if it is something
that would work better than output parameters.

CREATE TABLE [dbo].[tbltestvalues](
    [SystemValuesID] [int] IDENTITY(1,1) NOT NULL,
    [CustomField1Name] [varchar](50),
        [TStamp] [timestamp],
CONSTRAINT [PK_tbltestvalues] PRIMARY KEY CLUSTERED
([SystemValuesID] ASC))
go

declare @error int
declare @rowcount int
BEGIN TRY
   BEGIN TRANSACTION
       insert into tbltestvalues (CustomField1Name)
        OUTPUT INSERTED.systemvaluesid, inserted.Tstamp, suser_name()
       VALUES('test6')
   COMMIT transaction
END TRY

BEGIN CATCH
select
        @error = @@error,
        @rowcount = @@rowcount
       ROLLBACK transaction
END CATCH

drop table tbltestvalues
go

--
Thanks for any help on this
smHaig