Update Foreign Keys Procedure - 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

SQL Server Development (2000)

Update Foreign Keys Procedure

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2001-08-30 : 14:47:19

Is it ok if I use this type of stored procedure to update tables for my foreign keys? Is there already a sys sproc that does this? Since I use triggers I can't use cascading in sqlsvr2k. The update won't occur that often, only on deletes.


CREATE PROCEDURE spUpdateRef (
	@TableName varchar(256),
	@OldValue int,
	@NewValue int
) AS
 SET NOCOUNT ON
 BEGIN
	DECLARE @table varchar(256), @column varchar(256)
	DECLARE srcCur CURSOR FOR
		SELECT DISTINCT
			so1.name TableName, sc1.name ColumnName
		FROM
			sysforeignkeys sfk INNER JOIN sysobjects so1 ON (sfk.fkeyid = so1.id)
			INNER JOIN sysobjects so2 ON (sfk.rkeyid = so2.id)
			INNER JOIN syscolumns sc1 ON (sfk.fkey = sc1.colid and sc1.id = so1.id)
			INNER JOIN syscolumns sc2 ON (sfk.rkey = sc2.colid and sc2.id = so2.id)
		WHERE
			so2.name = @TableName
	FOR READ ONLY

	OPEN srcCur
	FETCH NEXT FROM srcCur INTO @table, @column

	WHILE @@FETCH_STATUS = 0
	 BEGIN
		DECLARE @SQL nvarchar(4000)

		SELECT @SQL = 'UPDATE ' + @table + ' SET ' + @column + ' = ' + CONVERT(nvarchar(32), @NewValue) + ' WHERE ' + @column + ' = ' + CONVERT(nvarchar(32), @OldValue)
		EXEC sp_executesql @SQL

		FETCH NEXT FROM srcCur INTO @table, @column
	 END

	CLOSE srcCur
	DEALLOCATE srcCur
 END

Regards,
Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources