select and insert in one sp

[Rick]

I want to write a single sp where I pass in the column values and if
it finds an exact match it returns the refid of that match else if it
doesn't find it, it adds a new row using the passed in values and
returns the RefID (primary key) of the new row (identity). I only
want one return value, being the RefID of the found or new row.

I currently am doing a select and then testing the @@ROWCOUNT. If < 1
I do an INSERT and return the identity. It doesn't give the desired
results.

Thanks,
RickN

[David Portas]

Something like this maybe?

SET @refid =
(SELECT refid
FROM YourTable
WHERE col1 = @col1
AND col2 = @col2, ...
)

IF @refid IS NULL
INSERT INTO YourTable (col1, col2, ...)
VALUES (@col1, @col2, ...)

SET @refid = SCOPE_IDENTITY()

--
David Portas
SQL Server MVP
--

[John Bell]

Hi

Posting your code would show exactly what you are doing, but at a guess you
are not calling the SCOPE_IDENTITY() function (@@IDENTITY on versions of SQL
server prior to 2000) to return the Identity value inserted.

John

"Rick" <ri**@abasoftware.com> wrote in message
news:28**************************@posting.google.c om...

I want to write a single sp where I pass in the column values and if
it finds an exact match it returns the refid of that match else if it
doesn't find it, it adds a new row using the passed in values and
returns the RefID (primary key) of the new row (identity). I only
want one return value, being the RefID of the found or new row.

I currently am doing a select and then testing the @@ROWCOUNT. If < 1
I do an INSERT and return the identity. It doesn't give the desired
results.

Thanks,
RickN

[Rick]

"David Portas" <RE****************************@acm.org> wrote in message news:<fK********************@giganews.com>...

Something like this maybe?

SET @refid =
(SELECT refid
FROM YourTable
WHERE col1 = @col1
AND col2 = @col2, ...
)

IF @refid IS NULL
INSERT INTO YourTable (col1, col2, ...)
VALUES (@col1, @col2, ...)

SET @refid = SCOPE_IDENTITY()

Thanks, this set me off in the right direction.
Added a @RefID OUTPUT parameter (not sure if this is the preferred method)
Changed the above slightly to :
Set @RefID = (SELECT ....
IF @RefID IS NULL
BEGIN
INSERT INTO...
SET @RefID = SCOPE_IDENTITY()
END
SELECT @RefID
This gives me the results I wanted.
If there are any added improvements, let me know.
Thanks again.
RickN

[Ross Presser]

ri**@abasoftware.com (Rick) wrote in
news:28*************************@posting.google.co m:

"David Portas" <RE****************************@acm.org> wrote in
message news:<fK********************@giganews.com>...

Something like this maybe?

SET @refid =
(SELECT refid
FROM YourTable
WHERE col1 = @col1
AND col2 = @col2, ...
)

IF @refid IS NULL
INSERT INTO YourTable (col1, col2, ...)
VALUES (@col1, @col2, ...)

SET @refid = SCOPE_IDENTITY()

Thanks, this set me off in the right direction.
Added a @RefID OUTPUT parameter (not sure if this is the preferred
method) Changed the above slightly to :
Set @RefID = (SELECT ....
IF @RefID IS NULL
BEGIN
INSERT INTO...
SET @RefID = SCOPE_IDENTITY()
END
SELECT @RefID
This gives me the results I wanted.
If there are any added improvements, let me know.
Thanks again.
RickN

If @RefID is an output parameter, then your final SELECT @RefID is
superfluous. You're getting your result from the output parameter; no
need to return a rowset as well.

[Erland Sommarskog]

Ross Presser (rp******@NOSPAM.imtek.com.invalid) writes:

If @RefID is an output parameter, then your final SELECT @RefID is
superfluous. You're getting your result from the output parameter; no
need to return a rowset as well.

And to add to this: for a single scalar value in this case, you should
use an output parameter, and produce a result set. There is usually lower
overhead with a parameter. For an occasional call it may not matter, but
if you are making lots of them, there can be a considerable difference.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp