First SP is used to check how many tables Referred
and return the Referred tables names
Second SP ,First Execute the First SP and get the O/P values
if Referred then displays the Referred tables names, else delete the matched data from the table1
SP1
CREATE PROCEDURE [dbo].[Table_spCheckRef]
@CodeNVARCHAR(50),
@COUNT BIGINT = NULL OUTPUT,
@STRNVARCHAR(500)= NULL OUTPUT
AS
SET @COUNT=(SELECT COUNT(*) FROM Table1 WHERE Code=@Code)
IF @COUNT > 0
SET @STR='table value Referred the following table1,table2 '
ELSE
SET @STR=null
SP2
CREATE PROCEDURE [dbo].[Table_spdelete]
@CodeNVARCHAR(50)
AS
DECLARE @COUNT BIGINT,@STR NVARCHAR(500);
EXEC test_sp @Code, @COUNT OUTPUT, @STR OUTPUT
IF @COUNT= 0
BEGIN
DELETE FROM Table1 WHERE Code=@Code
PRINT 'Data deleted...'
END
ELSE
BEGIN
PRINT LTRIM(@STR)
END
But i cant get the exact values...
how to solve this problem help me please