Beginner question: find or create function

[jrpfinch]

I have a table with two columns: siteID (int primary key) and siteName
(varchar(50) unique constraint).

I am completely new to databases and UDFs and would like to write a
function that looks for a particular siteName and returns the siteID.
If the siteName is not found then it would create a record and return
that record's siteID.

I am pretty sure there is a standard way of doing it and have been
looking for examples, but have yet to find anything on Google.

If anyone could point me in the right direction I would be very
grateful - I am still looking and will reply if I find anything.

Many thanks

Jon

[Plamen Ratchev]

Hi Jon,

Are you sure you really need to use UDF? In UDFs you can only insert in
table variables that are local to the function. However, using a stored
procedure it works just fine. Here is an example:

CREATE PROCEDURE GetSiteID (
@siteName AS VARCHAR(50))
AS
DECLARE @siteID INT

IF Exists(SELECT 1
FROM MySitesTable
WHERE siteName = @siteName)
BEGIN

-- Site exists: get the site Id
SELECT @siteID = siteID
FROM MySitesTable
WHERE siteName = @siteName

END
ELSE
BEGIN

-- Site does not exist: insert a new site
INSERT INTO MySitesTable (
siteName )
VALUES (
@siteName )

-- Get the new site ID
SELECT @siteID = @@IDENTITY

END

RETURN (@siteID)

GO

I am assuming here you have your siteID column defined as identity and it
will automatically generate the siteID number. You can return the new siteID
in different ways based on how you need to use it. It could be an output
parameter of the SP, using SELECT, or RETURN. Here is how you use it with
RETURN as I wrote it:

DECLARE @siteID INT

EXEC @siteID = GetSiteID 'Test'

SELECT @siteID

Regards,

Plamen Ratchev
http://www.SQLStudio.com

"jrpfinch" <jr******@gmail .comwrote in message
news:11******** *************@l 53g2000cwa.goog legroups.com...

>I have a table with two columns: siteID (int primary key) and siteName
(varchar(50) unique constraint).

I am completely new to databases and UDFs and would like to write a
function that looks for a particular siteName and returns the siteID.
If the siteName is not found then it would create a record and return
that record's siteID.

I am pretty sure there is a standard way of doing it and have been
looking for examples, but have yet to find anything on Google.

If anyone could point me in the right direction I would be very
grateful - I am still looking and will reply if I find anything.

Many thanks

Jon

[Robert Klemme]

On 18.01.2007 14:51, Plamen Ratchev wrote:

Are you sure you really need to use UDF? In UDFs you can only insert in
table variables that are local to the function. However, using a stored
procedure it works just fine.

Agreed.

Here is an example:

CREATE PROCEDURE GetSiteID (
@siteName AS VARCHAR(50))
AS
DECLARE @siteID INT

IF Exists(SELECT 1
FROM MySitesTable
WHERE siteName = @siteName)
BEGIN

-- Site exists: get the site Id
SELECT @siteID = siteID
FROM MySitesTable
WHERE siteName = @siteName

END
ELSE
BEGIN

-- Site does not exist: insert a new site
INSERT INTO MySitesTable (
siteName )
VALUES (
@siteName )

-- Get the new site ID
SELECT @siteID = @@IDENTITY

END

RETURN (@siteID)

GO

There's a more efficient option:

SELECT @siteID = siteID
FROM MySitesTable
WHERE siteName = @siteName

IF @siteID IS NULL
BEGIN
INSERT INTO MySitesTable ( siteName )
VALUES ( @siteName )
SET @siteId = SCOPE_IDENTITY( )
END

return @siteID
First, use the SCOPE_IDENTITY( ) in order to avoid concurrent sessions to
interact with each other. Then start out with the SELECT and see
whether you got something. That way you avoid the overhead of first
checking for existence. Also, it might actually happen that the check
suceeds and when you want to retrieve the value it's gone.

Note also that you need something like SELECT MIN(siteid) if siteName is
not unique.

My 0.02EUR

robert

[Plamen Ratchev]

Hi Robert,

I agree on the performance note and use of SCOPE_IDENTITY( ), I was just
trying to simplify things and illustrate better the steps. But if there is
concurrent activity this better be in a transaction, as even in your example
if siteID is NULL by the time you insert it there might be another insert
with the same siteName and the statement will result in error. And not an
issue with uniqueness for siteName as it was indicated there is unique
constraint on it.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

"Robert Klemme" <sh*********@go oglemail.comwro te in message
news:51******** *****@mid.indiv idual.net...

On 18.01.2007 14:51, Plamen Ratchev wrote:

>Are you sure you really need to use UDF? In UDFs you can only insert in
table variables that are local to the function. However, using a stored
procedure it works just fine.

Agreed.

Here is an example:

CREATE PROCEDURE GetSiteID (
@siteName AS VARCHAR(50))
AS
DECLARE @siteID INT

IF Exists(SELECT 1
FROM MySitesTable
WHERE siteName = @siteName)
BEGIN

-- Site exists: get the site Id
SELECT @siteID = siteID
FROM MySitesTable
WHERE siteName = @siteName

END
ELSE
BEGIN

-- Site does not exist: insert a new site
INSERT INTO MySitesTable (
siteName )
VALUES (
@siteName )

-- Get the new site ID
SELECT @siteID = @@IDENTITY

END

RETURN (@siteID)

GO

There's a more efficient option:

SELECT @siteID = siteID
FROM MySitesTable
WHERE siteName = @siteName

IF @siteID IS NULL
BEGIN
INSERT INTO MySitesTable ( siteName )
VALUES ( @siteName )
SET @siteId = SCOPE_IDENTITY( )
END

return @siteID
First, use the SCOPE_IDENTITY( ) in order to avoid concurrent sessions to
interact with each other. Then start out with the SELECT and see whether
you got something. That way you avoid the overhead of first checking for
existence. Also, it might actually happen that the check suceeds and when
you want to retrieve the value it's gone.

Note also that you need something like SELECT MIN(siteid) if siteName is
not unique.

My 0.02EUR

robert

[Robert Klemme]

Please don't top post.

On 18.01.2007 21:47, Plamen Ratchev wrote:

I agree on the performance note and use of SCOPE_IDENTITY( ), I was just
trying to simplify things and illustrate better the steps.

With all due respect, don't you think my solution is simpler or at least
as simple? There's not really a difference in complexity between
SCOPE_IDENTITY( ) and @@IDENTITY and your solution needs one more SQL
statement. :-)

But if there is
concurrent activity this better be in a transaction, as even in your example
if siteID is NULL by the time you insert it there might be another insert
with the same siteName and the statement will result in error.

Of course all this must be in a transaction - how would you execute the
code without TX at all, especially since it's in a stored procedure?

And not an
issue with uniqueness for siteName as it was indicated there is unique
constraint on it.

Ah, thanks! I hadn't seen that.

Regards

robert

[jrpfinch]

Many thanks for you help. After further research, stored procedures do
seem the way forward. Both your solutions are better than by first-go
hacky solution.

Jon

[Plamen Ratchev]

Hi Robert,

Like I said I do agree with you. As for the complexity I was just referring
to taking the extra step in order to illustrate the process, not the using
of SCOPE_IDENTITY( )... :)

Thanks!

Plamen Ratchev
http://www.SQLStudio.com