How to convert this stored procedure to simple query

I write this stored procedure but I am returning all sql queries to site code and I have a little problem to convert this query. I insert data in one table then insert it in another table with key that is generated in first table. I don't know what is the best way to write this from site code. To make three methods or what?

@m_UserId uniqueidentifier,
@m_WispTypeId int,
@m_CreatedOnDate datetime,
@m_PrivacyTypeId int,
@m_WispText nvarchar(200)
AS

SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

        DECLARE @wispId int

        INSERT INTO dbo.tbl_Wisps
        (UserId,WispTypeId,CreatedOnDate,PrivacyTypeId,WispText)
        VALUES
        (@m_UserId,@m_WispTypeId,@m_CreatedOnDate,@m_PrivacyTypeId,@m_WispText)

        SELECT @wispId = SCOPE_IDENTITY()

        INSERT INTO dbo.tbl_CommentableEntity
        (ItemId)
        VALUES
        (@wispId)

        DECLARE @ceid int

        sel开发者_运维技巧ect @ceid = SCOPE_IDENTITY()

        UPDATE dbo.tbl_Wisps SET CommentableEntityId = @ceid WHERE WispId = @wispId

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR ('Error in adding new wisp', 16, 1)
END CATCH

There are a number of ways to use the OUTPUT clause. The following pattern may work for you:

insert dbo.tbl_CommentableEntity (ItemId)
select wispId from (
    insert dbo.tbl_Wisps (UserId,WispTypeId,CreatedOnDate,PrivacyTypeId,WispText)
    output inserted.wispId
    values (@m_UserId, @m_WispTypeId, @m_CreatedOnDate, @m_PrivacyTypeId, @m_WispText)
) as ins

If you want to do it in code, I would split it into several methods and have the save methods return the identity. Then, you can just create a method that encompasses all three queries, and which emulates through code the same logic that is in the stored procedure

public int SaveThis()
{
    return -1 //return identity
}

public int SaveThat(int thisID)
{
    return -2 //return identity
}

public void SaveThisAndThat()
{
    int thisID = this.SaveThis();
    int thatID = this.SaveThat(thisID);

    //so on and so forth    
}