x

Need to Update a column within a function

In order to batch process some inserts, I need to be able to use a function within the Insert statement, but I need the function to update a table that maintains the next available value (in this case, a barcode):

INSERT Into SomeTable (name, docType, barcode)

SELECT Name, DocType, fn_getNextBarcode FROM SomeOtherTable WHERE Criteria = DesiredCriteria

where the function would update the barcode table so that the barcode is now increased by 1, then select and return that incremented barcode value.

Yes, things would be easy if the vendor had auto incremented the barcode field, but they didn't, and we can't change it.

Please help.
more ▼

asked Dec 10 '10 at 06:27 AM in Default

dvpdvpdvp gravatar image

dvpdvpdvp
11 1 1 1

a function will return a value or a table to be used to update a value, but you will not be able to put code inside the function itself that will manipulate data.
Dec 10 '10 at 08:09 AM Jeff Oresik
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first
Don't forget to wrap up the code in a transaction to ensure consistent behaviour.
more ▼

answered Dec 10 '10 at 06:49 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

(comments are locked)
10|1200 characters needed characters left
You can't update a base table from a function, unfortunately. This is going to prove a bit trickier. It is certainly possible to do this but a simpler alternative would get a temporary table of the barcodes first, and then assign them within the select statement.--all wrapped within a transaction.
more ▼

answered Dec 10 '10 at 11:44 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(comments are locked)
10|1200 characters needed characters left

Since your select statement might return more than one record, the fact that

You can't update a base table from a function

is actually a very good news as otherwise, it would allow you to update the base table as many times as there are records affected by the insert istead of doing it only once. This would be horrible. Here is the primitive sceleton of how you can do it. It should work if you have SQL Server 2005 or better.

declare @current_code int;
declare @final_code;

begin tran;

select @current_code = max(barcode_column) from dbo.Barcode;

insert into SomeTable ([name], docType, barcode)
select 
    [name], DocType, 
    @current_code + row_number() over (order by some_column)
    from SomeOtherTable 
    where Criteria = DesiredCriteria;

-- assuming that the number of affected rows matches the @@rowcount

set @final_code = @@rowcount + @current_code + 1;

update dbo.Barcode set barcode_column = @final_code;

commit tran;
go
Oleg
more ▼

answered Dec 10 '10 at 12:10 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

Hi, I am not fully understand about your questions. May be that is what you want -

INSERT INTO T1(name, type, barcode)
SELECT T2.Name, T2. Type, T3.Barcode
FROM Table2 as T2 inner join Table3 as T3 
ON T2.Type = T3.Type 
WHERE T3.Criteria = 'xxxxx' (or something Tx.*)
more ▼

answered Dec 10 '10 at 06:38 AM

Leo gravatar image

Leo
1.6k 51 56 58

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Jun 04 '13 at 02:33 AM

hongdida gravatar image

hongdida
0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x128
x82
x55

asked: Dec 10 '10 at 06:27 AM

Seen: 3435 times

Last Updated: Feb 14 at 02:49 AM