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

A good way to increment field value

Hi

I have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.

Is there a single query doing that in data table.

You can create all your calculation in an sql server user defined function.

And call this function while inserting the data into a table.

|||

Thanks. That seems interesting. And while searching for user defined functions I found help to other problem as well.

I found this:

create function getfulldate (@.date varchar(10))
returns datetime
as
begin
declare @.getfulldate datetime
set @.getfulldate = dateadd (mi,55,@.date)
return @.getfulldate
end

and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp

If I undestand this right, this goes into code. Do you know how to put udf in sql server. Or rather, where to start learning it.

Regards

Leif

|||

I found a tutorial about T-sql.