Stored Procedure Fix

Question

Post reply

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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