asked
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.
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.
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.*)
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.*)
Don't forget to wrap up the code in a transaction to ensure consistent behaviour.
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.
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
Hope this link will help you a little: [
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/][1][barcode-generation][2] [1]:
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/414742fc-aab6-4d3e-aaea-aa3a5ec01245 [2]:
http://www.barcodelib.com/net_barcode/main.html
Can these codes be integrated within the [
vb.net barcode creating][1] application to create barcode symbols? 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 [1]:
http://www.businessrefinery.com/barcode-generate/vbnet_barcode_generator.html
I guess this can be achieved using trigger on insert. Try writing a trigger, which will read max value of barcode from the table into a variable, increment the variable and insert into table. This will behave very much similar to identity column as you will not have to mention the value of barcode in your select statement.
3 People are following this question.
