How To Use The Stored Procedure Result To Update The Table

Hi,

I am new to stored procedure.

My stored procedure is returning me the list of tables names where the given tables PK is used as FK (can be null), based on the result of stored procedure I need to update the tables.


Following Stored procedure will return the list of tables where the usertable's PK is referred as a FK

I want to use the returned table name and update the set eh FK's value = null.

Some how it is not working , can some one point me out on correct solution.



CREATE PROCEDURE [dbo].[sp_delete_data]
AS
DECLARE @update_Var table (
REF_Table nvarchar(50),
FK_Column varchar(25),
PK_Table varchar(25),
PK_Column varchar(25),
Constraint_Name varchar(50));

insert into @update_Var
SELECT
FK.TABLE_NAME,
CU.COLUMN_NAME,
PK.TABLE_NAME,
PT.COLUMN_NAME,
C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME

WHERE PK.TABLE_NAME='usertable';

Select * from @update_Var



Thanks
Santosh Maskar