January 27, 2004 at 10:21 am
I am trying to create a SP to update a table that holds the max value of the primary key for a number of tables in the database. I want the SP to go through the table (autoinc) and update the max value (next_number) of the primary key (property_group_name) for each table (tbl_name).
My SP code when parsing returns the error that the variable @tbl is not declared, but it sure looks like it is decalred to me. Can anyone point out what I have done wrong here? The syntax is the same as other cursors I have (reluctantly) wriiten for this DB. Am I right in assuming that field and table names can be assigned to variables that are used in SQL statements?
Thanks!
CREATE PROC uspUpdateAutoInc
AS
DECLARE autoinc_cursor CURSOR FORWARD_ONLY
FOR
SELECT tbl_name
FROM autoinc
ORDER BY tbl_name
DECLARE @tbl varchar(50)
DECLARE @fld varchar(50)
OPEN autoinc_cursor
FETCH NEXT FROM autoinc_cursor INTO @tbl
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fld = (
SELECT property_group_name
FROM autoinc
WHERE tbl_name = @tbl)
UPDATE autoinc
SET next_number = (SELECT MAX(@fld)FROM @tbl)
WHERE property_group_name = @fld
FETCH NEXT FROM autoinc_cursor INTO @tbl
END
CLOSE autoinc_cursor
DEALLOCATE autoinc_cursor
Tim
January 27, 2004 at 11:47 am
The problem is that you will need to use dynamic sql for the update statment. Oh BTW: you will also want to declare your variables for the tablename and fieldnames to be of sysname.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 28, 2004 at 6:27 am
Here's an example. Note that I changed the cursor to retrieve the column name instead of using a separate SET.
drop table tbl1
drop table tbl2
drop table autoinc
go
create table tbl1 (id1 int not null, item1 varchar(25))
create table tbl2 (id2 int not null, item2 varchar(25))
create table autoinc (tbl_name sysname not null, property_group_name sysname not null, next_number int)
SET NOCOUNT ON
insert tbl1 (id1, item1) values (1, '1-Item 1')
insert tbl1 (id1, item1) values (2, '1-Item 2')
insert tbl1 (id1, item1) values (3, '1-Item 3')
insert tbl2 (id2, item2) values (1, '2-Item 1')
insert autoinc (tbl_name, property_group_name, next_number) values ('tbl1', 'id1', 0)
insert autoinc (tbl_name, property_group_name, next_number) values ('tbl2', 'id2', 0)
SET NOCOUNT OFF
GO
DROP PROCEDURE uspUpdateAutoInc
GO
CREATE PROCEDURE uspUpdateAutoInc
AS
DECLARE autoinc_cursor CURSOR FORWARD_ONLY
FOR
SELECT tbl_name, property_group_name
FROM autoinc
ORDER BY tbl_name
DECLARE @tbl sysname
DECLARE @fld sysname
DECLARE @cmd varchar(1000)
OPEN autoinc_cursor
FETCH NEXT FROM autoinc_cursor INTO @tbl, @fld
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'UPDATE autoinc '
+ ' SET next_number = (SELECT MAX(' + @fld + ') FROM ' + @tbl + ')'
+ ' WHERE tbl_name = ''' + @tbl + ''''
+ ' AND property_group_name = ''' + @fld + ''''
-- PRINT @cmd
EXEC (@cmd)
FETCH NEXT FROM autoinc_cursor INTO @tbl, @fld
END
CLOSE autoinc_cursor
DEALLOCATE autoinc_cursor
GO
------------------------------------------------------------------------
-- Test the code
------------------------------------------------------------------------
SELECT 'BEFORE:', next_number, tbl_name FROM autoinc
EXEC uspUpdateAutoInc
SELECT 'AFTER:', next_number, tbl_name FROM autoinc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply