Not to return result set from Stored Procedure - Problem with ISOLATION LEVEL SERIALIZABLE

  • Hi,

        My stored procedure is this:

        create procedure netaji

            @o_a INT OUTPUT

    as

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION

     select * from table_1

    --some operations on this table (insert/update)

    COMMIT TRANSACTION

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    DBCC FREEPROCCACHE

     select @o_a = 1000

    go

    In this procedure, transaction isolation level is set as serializable and the statement in the transaction is

    select * from table_1  

    This will lock the table within this session till isolation level is changed.

    Now this procedure returns 1000(@a).

    Problem: Procedure returns

      1) the result set

      2) output parameter @o_a value.

    But I need only @o_a and not the result set.

    Because, I am calling this from my application using RogueWaveDB libraries. This is returning NULL value to the output parameter. But this works fine i.e returns 1000 when we comment the select statement.

    I want to have select statement for locking purpose but I don't want to return the result set of table_1

    U can test this simply in query analyzer:

    declare @res int

     exec netaji @res output

     select @res

    Here it prints result of select statment and value of @res.

    I don't want to print the result of select statement.

     

    Thanks,

    Netaji

     

  • Maybe one quick optimization..

    Select 1 from Table where 1=0

    You still get a recordset but at least it's empty.

    Then on the app side you only need to extract the return parameter...

    You might also have a look at SET FMTONLY ON, but I'm not sure it'll still lock the table like you wish to.

    Maybe someone else has a better idea.

  • Try this:

    "select 1 as [one] into #x from table_1 with (tablock, holdlock) option ( fast 1 ) "

    This way:

    1 - you directing the o/p to a temp table so you wont recieve it as an o/p.

    2 - tablock with lock the table while holdlock will hold the lock till you commit.

    3 - option (fast 1) is just to optimize the query better and can be removed.

    Note: drop the temp table in the end.

    Might work.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply