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

• Netaji Goud Ediga

  Old Hand

  Points: 325

  More actions

  June 23, 2005 at 11:11 am

  #362987

  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

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 23, 2005 at 11:29 am

  #568020

  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.

• HSM_DBA

  Right there with Babe

  Points: 771

  More actions

  June 26, 2005 at 5:25 pm

  #568479

  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.