SQL Server Performance Forum – Threads Archive

Varbinary columns

RosID1 RosID2
67108865 33884956
67108865 33885211
67108865 33885212
67108865 33947907
67108865 33947921
67108865 33947922
The integer values in the RosID1 and RosID2 columns are actually the hex values.
The First Byte of the RosID1 Column represents a threadID and the other three bytes
represents a Seq Number. When I copy these values to the destination tables
I have to change the first byte to a new ThreadID which I will geting as an Input
parameter to the Stored Proc as integer. Also I have to get the max seqNumber from the
destination table’s ROSID1 Column for the ThreadID. So I need to split the destination tables ROSID1 Column to get the max sequence number. what is a best way to do this. Is there a set bases solution to it or we have to use
a CURSOR

What is RosID2 for ? Can you also post how should the data in destination tables like ?
KH

RosID2 has some information in that the first byte has represent the order type and the other 3 bytes will have the date. both the sorce and destination tables structure is the same. But when I move the records from source to destination I have to chnage the RosID1’s first byte to have a new threadID and the other three bytes of the RosID1 is the
sequence number. I have to get the max seqnumber from the destination table ( i.e the SeqNumber from the RosID1 of the destination table for the same threadID which is the first byte ) Hope its not too confusing Thx
Venu

If you are storing the values as integers and you want to get the maximum sequence number for a particular thread ID, I would create a scalar function like this: CREATE FUNCTION MaxSequenceNumber (@ThreadID int) RETURNS int AS BEGIN DECLARE @SequenceNumber int SET @SequenceNumber=(SELECT REPLACE(convert(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,MAX(ROSID1)),convert(varchar(2),@ThreadID),”) FROM YOURTABLE WHERE convert(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,ROSID1) LIKE convert(varchar(2),@ThreadID) + ‘%’) RETURN convert(int,@SequenceNumber) END GO and use it wherever necessary. Nathan H. Omukwenyi MVP [SQL Server]

]]>