Posted by Robert Klemme on 01/18/07 14:57

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]