Stored Procedure Fix - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Fix

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-04-27 : 14:52:03

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-27 : 15:03:30

Why are you using dynamic SQL for this?

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

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

SELECT CURRVALUE
FROM CASE_NUM_SEQ
WHERE SEQ_CD = @tblname

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources