Checking for Existance to Enforce Relationships

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)

Checking for Existance to Enforce Relationships

Author

Topic

alebey
Starting Member

17 Posts

Posted - 2001-07-14 : 17:23:28

Hey everyone.

This is not really a problem rather an attempt to pick your brains for a better (more efficient?) solution.

I have a stored procedure that adds a "reference" to a particular ServiceID in Services table to a record in Acccounts table. These tables are joined on the ServiceID fields by a "hard" relationship -- with "Enforce relationship for INSERTs and UPDATEs" enabled.

ServiceID is passed in as an input parameter (@service_id_in) to the stored procedure in question. But I want to first check that the input service id actually exists in the Services table, and only then insert it into Accounts table.

Right now, I have a (kind of yucki) solution that I use:



DECLARE @x tinyint
SELECT @x = COUNT(*) FROM Services WHERE ServiceID = @service_id_in

IF @x > 0
BEGIN
    INSERT INTO Accounts (Blah, Blee, Doo, ServiceID)
    VALUES ('doo', 'daa', 'dee', @service_id_in)

    PRINT 'added ok'
END

ELSE
    PRINT 'did NOT add!!!'

I'm sure there's a more elegant approach to doing this.

Thanks for your ideas

PS: it's a SQL Server 2000 database.

Edited by - alebey on 07/14/2001 17:26:09

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources