CREATE PROCEDURE up_foo
@yourvalue VARCHAR(100)
AS
DECLARE @searchID INT
SET @searchID = 0
SELECT @searchID = [PK OF TABLE]
FROM [TABLE]
WHERE COLUMN = @yourvalue
IF @ searchID = 0
BEGIN
DO YOUR INSERT
SELECT @@identity (or other ident type)
END
In your CF, a simple cfstoredproc call with a resultset will give you the
necessary new id or the existing key.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 24 July 2006 15:51
To: SQL
Subject: stored procedure branching best practices
I've been up all night rebuilding a lot of the site UI so I'm not to
clear-headed at the moment. That being said, what would be the most
efficient way of doing the following as a stored procedure:
1. Check if a text value exists in a table
1a. if yes, get the ID of the text value
2. if not, enter the value into the table and return the newly created ID
number
This obviously looks like a select, check, return or insert and return. Is
there a more efficient way of doing this? Maybe something built into SQL2000
that automatically allows this type of operation?
Thanks
p.s. this is part of a rebuild of my logging. It's gotten a little out of
hand with almost 13 gig of data since January. I've already set it up nice
and tight, but there's always room for improvement and I really don't want
to dump the data if I can help it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2521
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
