Hello,

Does anybody has any clue if its possible to verify if a record has any
related data?

Let me try to explain these a better, i'd like to show the delete
option only when no data related with the specifc Record is available
and not return an error message if the data that they try to delete had
any child. Since I have tons of tables and registers associated with an
invoice, i was trying to build a function to retrieve it without
develop code the each case, to do that I tried to develop a function
that could do it.

I already have the SQL to the function created but I'm stuck with the
sp_executesql that cannot be used inside the function.

I'm running out of ideas and i'll start to write some less reusable
code, but since i still believe that this would be really useful i
decide to ask for external help. :-)

If anybody has any idea how I could finish it i'll be really glad.

Maybe this function already exists in SQL but I really did not found
it.

Here is the code that I already have

------- ------- ------- -------

select dbo.hasRelationalData('tbName', 42)

------- ------- ------- -------

create function dbo.hasRelationalData
(
    @tbName        varchar(255), -- table name
    @Id        varchar(255)  -- primary key value
) returns bit
as
begin
-- The record with the primary key @id in the table @tbName
-- has any child record.
-- To do that, this functions verifies all foreing keys associated
-- with the primary key of this table and than compare with the given
value.
-- It will return 1 - true if any relational data were found and
-- 0 if none.

--declare @tbName        varchar(255)
--declare @Id        varchar(255)

--set @tbName = 'tbJob'
--set @Id = 286

    -- declare variables
    declare @strSQL        Nvarchar(4000)
    declare @totalRecords    int
    declare @hasData    bit

    -- initialize strSQL
    set @strSQL = ''

    -- create an SQL that returns one line for each foreign record that is
associated
    -- with the record in the given table that has a primary key value
that matches
    -- the value given as parameter of this function
    select     @strSQL    = @strSQL + 'select 1 RowIdentifier from ' +
ForeignTables.name +
                    ' where ' + ForeignColumns.name + ' = ' + @Id + '|'
    from    sysObjects OriginalTable, sysObjects PrimaryKey,
        sysIndexes PrimaryIndex, sysForeignKeys ForeignKeys,
        sysObjects ForeignTables, sysColumns ForeignColumns
    where    OriginalTable.name = @tbName
    and    OriginalTable.id = PrimaryKey.parent_obj
    and    PrimaryKey.xtype = 'PK'
    and    PrimaryIndex.name = PrimaryKey.name
    and    PrimaryIndex.id = ForeignKeys.rkeyid
    and     ForeignKeys.fkeyId = ForeignTables.id
    and    ForeignKeys.fkeyId = ForeignColumns.id
    and    ForeignKeys.fkey = ForeignColumns.colid

    -- remove the final pipe in the query string
    set @strSQL = substring(@strSQL, 0, len(@strSQL))
    -- replace the pipe that separate each one of the tables that
    -- has a foreign key associated with the primary key of the
    -- given table with the 'UNION ALL' clause to retrieve all
    -- rows in a unique sql statement
    set @strSQL = replace(@strSQL, '|', ' UNION ALL ')

    -- insert the current sql statement as a subquery that store all
returned
    -- values in a variable. The value in this variable will not be used
since it is a local
    -- variable of transaction executed with the sp_executesql procedure,
but it still need
    -- to be used to guarantee that no value will be printed by the
sp_executesql command
    -- and that all rows in the old @strSQL will be read, that will make
the @@rowcount usefull
    set @strSQL = 'select @RowStorage = RowIdentifier from (' + @strSQL +
') as tbRelatedValues'

    -- execute the build @strSQL
    exec(sp_executesql @strSQL, N'@RowStorage int', @RowStorage = 0)

    -- return all rows read by the sp_executesql
    select @totalRecords = @@rowcount

    if (@totalRecords > 0)
        set @hasData = 1
    else
        set @hasData = 0

    return @hasData

end

------- ------- ------- -------


Ps: sorry about the english, i hope its understandable

Any help will be highly apreciated.

Thanks

Tarcila

<tarc***@infomercial.tv> wrote in message
Show quoteHide quoteI assume you are returning the existing row(s) from the parent table for the
user to review before he deletes them. You can return some indication of the
existence of dependent rows with the rest of the data. Example:

SELECT A.key_col, A.col1, A.col2, A.col3,
T.dependency
FROM A /* Parent table */
LEFT JOIN
(SELECT A.key_col,
  'Y' AS dependency
  FROM A
  WHERE EXISTS
   (SELECT *
    FROM B /* Referencing table */
    WHERE A.col1 = B.col1)
   OR EXISTS
   (SELECT *
    FROM C /* Referencing table */
    WHERE A.col2 = C.col2)
   OR EXISTS
   (SELECT *
    FROM D /* Referencing table */
    WHERE A.col3 = D.col3)) AS T
ON A.key_col = T.key_col ;

Hi David,

Thanks for your response.

I'm using Microsoft SQL Server 2000 - version 8.00.000, i did not
insert the tables and the insert codes because i'm trying to create a
generic function to return parent data from any table that has a
primary key defined.

Il got your code but i was trying to develop something more generic
that I did not need to rewrite for each table and that will need to be
updated every time a new table is added to the database, do you think
that is possible?

Thanks again,

regards,

Tarcila

<tarc***@infomercial.tv> wrote in message
Show quoteHide quoteIt may be possible but it's a bad idea. In general, it's best to perform all
data access through stored procedures. There are many advantages to using
procs (security, maintainability, performance) but most of those advantages
are lost if you start using dynamic code in procs. For that reason, common
best practice is to write a set of STATIC procs for each new table to
perform INSERT, UPDATE, DELETE and SELECT operations. Sometimes these procs
are called CRUD procs - meaning Create/Update/Delete.

A lot of the hard work of generating CRUD procs can be done
semi-automatically. It isn't too difficult to write a script to generate
skeleton procs dynamically from table structures at design time. With a few
keystrokes and a little editing you can create working procs very quickly.
You could build your foreign key validating code into those scripts. That's
always going to be a far superior solution to doing the same thing
dynamically at runtime. In any case, your current approach won't work
because dynamic code is not permitted in user-defined functions.

See:
http://www.sommarskog.se/dynamic_sql.html

generic audit trail best practice questions
append a string to all the values in a column SQL
Extracting from linked tables into pivot
What's a good SQL server naming convention
Optimizing an IN clause