A question about udf versus sp in a specific context...

Given the following objective:

1. Assume that I have a table that contains two fields: an auto-numbered id and an integer value
2. Check to see if a record exists in a table based on a parameter query of the integer value
3. If the record exists, return the record id
4. If the record does not exist, insert a new record into the table (using the parameter value as data) and return the auto-numbered id of the new record

I can do each of these things as a sequence of individual steps, of course, but it seems to me that I ought to be able to do it with a single udf (or perhaps a specialized query) that would be more efficient. I couldn't find something like this in the beginning SQL Express books I have on hand and I also didn't find anything exactly on point on this newsgroup or a search of Google. However, I am sure the answer is 'out there' and I am hoping that someone can point me in the right direction. Thanks!

Duncan

In thinking further about my original question and digging into one of my old SQL 2000 books, I concocted the following usp which seems to work:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT
AS
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF @.@.ROWCOUNT = 1
RETURN @.kit_id
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
RETURN @.kit_id
END

Does anyone see any particular issues with the above code? Thanks.

Duncan

|||

Perhaps something like this:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT * FROM kits WHERE product_variant_id = @.product_variant_id)

BEGIN
SELECT @.kit_id = id FROM kits WHERE product_variant_id = product_variant_id
END
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END

SET NOCOUNT OFF

END

|||Thanks for helping me out!|||

To give back in a small way, here is a slightly modified version of my earlier repsonse:

CREATE PROC [dbo].[usp_GetKitId] @.kit_id int OUTPUT, @.product_variant_id int
AS
SET NOCOUNT ON
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF NOT @.@.ROWCOUNT = 1
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END
SET NOCOUNT OFF

Then called from code-behind like so:

PublicSharedFunction GetKitId(ByVal product_variant_id)AsInteger'If a kit exists for the product variant, return the kit id; if a kit does'not exist for the product variant, create a new kit and return the new'kit's id.Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("atheniqueonlineConnectionString").ConnectionStringDim connectionAsNew SqlConnection(connectionString)Dim cmdAsNew SqlCommandDim kitIdAsIntegerWith cmd

.Connection = connection

.CommandText =

"usp_GetKitId"

.CommandType = CommandType.StoredProcedure

.Parameters.Clear()

.Parameters.Add(

New SqlParameter("kit_id", SqlDbType.Int))

.Parameters(

"kit_id").Direction = ParameterDirection.Output

.Parameters(

"kit_id").Value = 0

.Parameters.Add(

New SqlParameter("product_variant_id", SqlDbType.Int))

.Parameters(

"product_variant_id").Direction = ParameterDirection.Input

.Parameters(

"product_variant_id").Value = product_variant_idEndWithTry'Fill table

connection.Open()

Try

cmd.ExecuteNonQuery()

Catch exAs Exception'Handle exceptionsEndTryCatch eAs SqlException' Handle exceptionsFinally

connection.Close()

EndTry'Return the result

kitId = cmd.Parameters(

"kit_id").ValueReturn kitIdEndFunction