Stored Procedure Fix

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  April 27, 2007 at 12:56 pm

  #123676

  Guys,

  I have a table case_num_seq

  select * from case_num_seq

  seq_cd currval incr

  ____________________________

  accs 1 1

  I have stored procedure which call increments and currval column and spits out the next value

  CREATE PROCEDURE [dbo].[SEQVAL] @tblname sysname AS

  DECLARE @sql1 nvarchar(4000)

  SELECT @sql1 = ' UPDATE CASE_NUM_SEQ' +

  ' SET CURRVALUE = CURRVALUE+INCR WHERE SEQ_CD = '+quotename(@tblname)

  EXEC sp_executesql @sql1

  DECLARE @sql nvarchar(4000)

  SELECT @sql = ' SELECT CURRVALUE' +

  ' FROM CASE_NUM_SEQ WHERE SEQ_CD = ' + quotename(@tblname)

  EXEC sp_executesql @sql

  ____________________

  When I execute this stored procedure I get the following error

  EXEC SEQVAL 'ACCS'

  "Msg 207, Level 16, State 1, Line 1

  Invalid column name 'ACCS'.

  Msg 207, Level 16, State 1, Line 1

  Invalid column name 'ACCS'."

  Any suggestions and inputs would help

  Thanks

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  April 27, 2007 at 1:05 pm

  #703045

  I'm not sure why you're doing the dynamic SQL.  What about:

  CREATE PROCEDURE [dbo].[SEQVAL] @tblname varchar(100) AS

  Begin tran

  UPDATE CASE_NUM_SEQ SET CURRVALUE = CURRVALUE + INCR WHERE SEQ_CD = @tblname

  SELECT CURRVALUE FROM CASE_NUM_SEQ WHERE SEQ_CD = @tblname

  commit

   

   

  @tablename should be declared as the same type as seq_cd.  Wrap the thing in a commit to keep anyone from doing another update before your 'Select'

   

   

   

   

  ---

  And then again, I might be wrong ...
  David Webb

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 27, 2007 at 1:07 pm

  #703047

  SELECT

  @sql1 = ' UPDATE CASE_NUM_SEQ' +

  ' SET CURRVALUE = CURRVALUE+INCR WHERE SEQ_CD = '

  + '''@tblname'''

  EXEC

  sp_executesql @sql1

  DECLARE

  @sql nvarchar(4000)

  SELECT

  @sql = ' SELECT CURRVALUE' +

  ' FROM CASE_NUM_SEQ WHERE SEQ_CD = '

  + '''@tblname'''

  EXEC

  sp_executesql @sql

   

  Or try the changes above...

  

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  April 27, 2007 at 3:30 pm

  #703066

  CREATE PROCEDURE [dbo].[SEQVAL] @tblname varchar(100) AS

  Begin tran

  UPDATE CASE_NUM_SEQ SET CURRVALUE = CURRVALUE + INCR WHERE SEQ_CD = @tblname

  SELECT CURRVALUE FROM CASE_NUM_SEQ WHERE SEQ_CD = @tblname

  commit

  ---

  We had a similar procedure at work (created by a 3rd party)... it made an average of 640 deadlocks per day with occasional spikes to 4000 a day.  And, it's not gonna matter what you do to it so long as you have an UPDATE/SELECT in the transaction.  I can tell you how to fix it but....

  My first question would be "Why in the heck aren't you using an IDENTITY column on the tables to do this???"  What do you think this is?  ORACLE?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  April 27, 2007 at 4:49 pm

  #703084

  Jeff's right.  There are goofy things you have to do to limit conflicts with this scenario.  Artificially long rows to force each entry onto it's own page, no indexes, and some other stuff I can't recall now since I haven't done this since I worked on Sybase in the last century.

  Go with the IDENTITY, as suggested, it it's at all a possibility.

  ---

  And then again, I might be wrong ...
  David Webb