|
|
Posted by Plamen Ratchev on 01/18/07 13:51
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" <jrpfinch@gmail.com> wrote in message
news:1169122051.697233.51850@l53g2000cwa.googlegroups.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
>
Navigation:
[Reply to this message]
|