How to call a store proc which has insert & select both

[Sudip_inn]

how to use ado.net classes to call a store procedure which has insert & select query.

basically i want to call SP which has insert first and after insert there is a select query whose data i need to get at winform side.

if i use ExecuteNonQuery then only insert will work but i will not get result of select query at winform side. if i use data reader or datatable then data insertion may not work.

so please guide me. how to write code. here is my SP code

--Sample Data & created by Tridip
--<?xml version="1.0" encoding="utf-16"?>
--<Root>
-- <PeriodData>
-- <PeriodType>ANNUALONLY</PeriodType>
-- <Period>2010 FY</Period>
-- </PeriodData>
-- <PeriodData>
-- <PeriodType>ANNUALONLY</PeriodType>
-- <Period>2011 FY</Period>
-- </PeriodData>
--</Root>

CREATE PROCEDURE [dbo].[USP_SaveCalendarData]
@TickerID varchar(20),
@Following53Weeks char(1),
@TransitionPeriod char(1),
@Periods xml

AS
BEGIN
BEGIN TRY
DECLARE @MasterID INT

BEGIN TRAN

DELETE FROM tblCalenderMaster
DELETE FROM tblCalenderDetail

SET @MasterID = 0

INSERT INTO tblCalenderMaster (TickerID,Following53Weeks,TransitionPeriod)
VALUES(@TickerID,@Following53Weeks,@TransitionPeriod)

SELECT @MasterID = SCOPE_IDENTITY()

INSERT INTO tblCalenderDetail (MasterID,PeriodType,Period)
SELECT @MasterID AS MasterID,
d.v.value('(PeriodType/text())[1]','VARCHAR(20)') AS PeriodType,
d.v.value('(Period/text())[1]','VARCHAR(50)') AS Period
FROM @Periods.nodes('/Root/PeriodData') AS d(v)

COMMIT TRAN

SELECT * FROM tblCalenderDetail WHERE MasterID = @MasterID


END TRY
BEGIN CATCH

ROLLBACK TRAN

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END


thanks

Continue reading...