|
|
Posted by Plamen Ratchev on 01/18/07 20:47
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" <shortcutter@googlemail.com> wrote in message
news:519g5uF1il4k4U1@mid.individual.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
[Back to original message]
|