October 31, 2008 at 1:18 pm
I have the following table:
CREATE TABLE [tblPnParamValues1] (
[ValueID] [int] IDENTITY (1, 1) NOT NULL ,
[ParameterID] [int] NOT NULL ,
[ValueReal] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ValueCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblPnParamValues_ValueCode1] DEFAULT (''),
[Sequence] [tinyint] NOT NULL CONSTRAINT [DF_tblPnParamValues_Sequence1] DEFAULT (0),
[ParamValueCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
CONSTRAINT [PK_tblPnParamValues1] PRIMARY KEY CLUSTERED
(
[ValueID]
) ON [PRIMARY] ,
CONSTRAINT [IX_tblPnParamValues1] UNIQUE NONCLUSTERED
(
[ParameterID],
[ParamValueCode]
) ON [PRIMARY]
) ON [PRIMARY]
GO
The fields ParameterID and ParamValueCode have a unique constraint. I need to assign these with a two alpha character code (note this field is case sensitive to give me enough combinations) upon insert. I can do this if records are added one at a time, but I cannot figure out how to make this set based friendly. Below is the function that actually assigns the two digit code
ALter FUNCTION dbo.fnPnParamValueGenCode
(
@ParamID int
)
RETURNS CHAR(2)
AS
BEGIN
DECLARE @r CHAR(2)
Declare @x Char(2)
Declare @1 Char(1), @1Pos Tinyint
Declare @2 Char(1), @2Pos Tinyint
Declare @Map Char(52)
Select @Map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
Select @X = IsNull(Max(ParamValueCode),'')
From dbo.tblPnParamValues
Where ParameterID = @ParamID
If @x = ''
Begin
Select @r = 'AA'
Goto Finished
End
Select @1 = Substring(@X,1,1)
Select @2 = Substring(@X,2,1)
Select @1Pos = CharIndex(@1, @Map)
Select @2Pos = CharIndex(@2, @Map)
If @1Pos = Len(@Map) and @2Pos = Len(@Map)
-- will use check constraint on column to disallow this value
Begin
Select @r = '00'
Goto Finished
End
If @1Pos = 0 or @2Pos = 0
-- will use check constraint on column to disallow this value
Begin
Select @r = '00'
Goto Finished
End
Select @r =
Case
When @1Pos < Len(@Map) Then Substring(@Map, @1Pos + 1, 1) + @2
When @1Pos = Len(@Map) Then Substring(@Map, 1, 1) + SubString(@Map, @2Pos + 1, 1)
End
Finished:
RETURN @r
END
Basically the function is only designed to do this one record at a time with the whole MAX part. If I insert records into the table using this function, it fails because if there is more than one record with the same ParameterID, they end up with the same code. Other than doing something in a trigger (looping thru inserted records, bad idea) or doing updates exclusively thru stored procedures, is there another way? Is there something here I am missing? I hope I provided enough information to help. Any suggestions are appreciated.
October 31, 2008 at 1:22 pm
I have to ask, why do you need this 2 character code if the you already have a unique key and have added an identity field which is the primary key which you can use as a surrogate in joins?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 1:37 pm
I probably should have explained the reasoning behind the codes. This is for inventory part numbers and the table stores options that all parts can have. The plan is to build out a string based on the codes applicable to the part number. Then it will be rather quick to find matching parts from competing manufacturers. I realize we could associate the part numbers with the parameters themselves in a cross table (we are going to do that), but to quickly find alternate parts, having a preconstructed string to use as a lookup will be the fastest method hands down. There are over 500K+ part numbers and 20K+ parameters possible and each part consists of at least 4-5 parameters (if not more). This is an unusual approach, but is being done in the name of speed for the user. I hope this makes sense.
October 31, 2008 at 1:49 pm
I don't think you do set-based using a function since the function is referencing the table it is updating.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 2:35 pm
I'm a bit confused, but check out my post I just posted and my other post, "Recursive"; I think that may be what you want...
October 31, 2008 at 6:57 pm
john.steinbeck (10/31/2008)
I'm a bit confused, but check out my post I just posted and my other post, "Recursive"; I think that may be what you want...
Please post the correct URL.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2008 at 6:58 pm
dbaInTraining (10/31/2008)
I probably should have explained the reasoning behind the codes. This is for inventory part numbers and the table stores options that all parts can have. The plan is to build out a string based on the codes applicable to the part number. Then it will be rather quick to find matching parts from competing manufacturers. I realize we could associate the part numbers with the parameters themselves in a cross table (we are going to do that), but to quickly find alternate parts, having a preconstructed string to use as a lookup will be the fastest method hands down. There are over 500K+ part numbers and 20K+ parameters possible and each part consists of at least 4-5 parameters (if not more). This is an unusual approach, but is being done in the name of speed for the user. I hope this makes sense.
It would really help if we had some sample data and a Before'n'After listing of some of that data. Please the the link in my signature below for how to post data ready for use... it'll help you get a better answer quicker.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply