Stored procedure help

• denis.gendera

  Right there with Babe

  Points: 733

  More actions

  March 1, 2012 at 10:44 am

  #256893

  I need to know how to write stored procedure for updating column in my table.

  I need to search for string value from columnA and need to update this in Column B. My table countains 120,000 rows.

  Table Columns

  machine_id, firmwarerevision, pnpdeviceid0

  Criteria

  If column firmwarerevision is NULL or empty, extract firewarerevison value from deviceID, and update column deviceID

  Script Logic I'm using to determine the value for firmwarerevision

  declare @document varchar(256);

  declare @pos1 int;

  declare @pos2 int;

  declare @len int;

  declare @doc1 varchar(256);

  declare @firmware varchar(50);

  select @document = pnpdeviceid0 from customstorageinfo WHERE firmwarerevison is null

  set @len = LEN(@document)

  set @pos1 = CHARINDEX('REV_',@document)

  set @doc1 = SUBSTRING(@document, @pos1, @len)

  print @document

  print @doc1

  set @pos2 = CHARINDEX('&',@doc1)

  set @firmware = SUBSTRING(@doc1, 5, @pos2 - 5)

  For this to work do I need to use CURSOR, or can this be done another way.

  thx for the help

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 1, 2012 at 11:04 am

  #1453789

  does the select statement i'm providing produce the list of items that would need to be updated;

  you didn't mention the actual table naem to be updated...you'll need to change that.

  it looks like oyu could do a single pass UPDATE FROM, but without the acutal table structures and sample data, i'm just guessing.

  SELECT

  SUBSTRING(customstorageinfo.pnpdeviceid0, CHARINDEX('REV_',customstorageinfo.pnpdeviceid0)+4,256)

  AS NEWVALUE,

  * FROM SomeTable,customstorageinfo

  WHERE SomeTable.pnpdeviceid0 = SUBSTRING(customstorageinfo.pnpdeviceid0, CHARINDEX('REV_',customstorageinfo.pnpdeviceid0)+ 4,256)

  AND (SomeTable.firmwarerevision is NULL

  OR SomeTable..firmwarerevision = '')

  /*

  maybe the update

  UPDATE SomeTable

  SomeTable.firmwarerevision =

  From customstorageinfo

  WHERE SomeTable.pnpdeviceid0 = SUBSTRING(customstorageinfo.pnpdeviceid0, CHARINDEX('REV_',customstorageinfo.pnpdeviceid0)+4,256)

  AND (SomeTable.firmwarerevision is NULL

  OR SomeTable.firmwarerevision = '')

  */

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• denis.gendera

  Right there with Babe

  Points: 733

  More actions

  March 1, 2012 at 3:57 pm

  #1453941

  Hi Lowell,

  The data/column I need to update with the new value is actually in the same table "CustomerStorageInfo"

  I need to do double string function to get the firmwarerevision value

  Example data: SCSIDISK&VEN_HITACHI&PROD_HTS725016A9A364&REV_PCBO4&3938C3D8&0&000

  Lookup1: Search for REV_ using the full string, create new string (Temp Value) ==> PCBO4&3938C3D8&0&000

  Lookup2: Search for '&' using string PCBO4&3938C3D8&0&000

  Output: PCBO4

  Any idea how to construct this in SELECT Statement, you provided already.

  Thx for your help.