Parameter using two fields

Transact-SQL
1

I'm trying to figure out how to use a parameter based on two different fields from the same table.

select distinct groupCode , partNo ,
CASE

WHEN prod.groupCode = '#ALF' then 'Aluminum'
WHEN prod.groupCode = '#ALW' then 'Aluminum Wire'
WHEN prod.groupCode = '#CUF' then 'Copper'
WHEN prod.groupCode = '#CUW' then 'Copper Wire'
WHEN prod.groupCode = '#INS' then 'Nomex'
WHEN prod.partNo LIKE '8%600' then 'Lamination Kit'
WHEN prod.partNo LIKE '8%700' then 'Core Kit'
--else 'Unknown'
end as 'Material'

from
prod

This is the code I'm using in the dataset I have that is giving the parameter it's available values.

Thank you.

2

which fields?

3

groupCode and partNo

4

You need a lookup table with groupCode and groupValue columns:

CREATE TABLE dbo.GCLookup
   ( groupCode varchar(10)
   , groupValue varchar(60)
   );
INSERT INTO dbo.GCLookup (
                groupCode
              , groupValue
                )
VALUES
   ( '#ALF' , 'Aluminum' )
 , ( '#ALW' , 'Aluminum Wire' )
 , ( '#CUF' , 'Copper' )
 , ( '#CUW' , 'Copper Wire' )
 , ( '#INS' , 'Nomex' )
 , ( '8%600' , 'Lamination Kit' )
 , ( '8%700' , 'Core Kit' );
GO
SELECT DISTINCT
     p.groupCode
   , p.partNo
   , gcl.groupValue
FROM
   dbo.prod p
JOIN
   dbo.GCLookup gcl
ON p.groupCode LIKE gcl.groupCode ;
5

Thank you!

I'm trying to understand the statement. The create table statement is not considering the partNo, which is the 2nd field I'm trying to incorporate into the parameter option (along with groupCode)

Let me know if that makes sense.

Thank you for your time!

6

Using a lookup table rather than using a case expression saves you having to repeat that case expression everywhere you need to present the "translated human understandable value" instead of the group code. This is basic relational database design. The example I created is more a utility look up table as a foreign key wouldn't work as 2 of the rows could only be joined using like, not =.

Without the DDL for the tables in you database suggestions are more guesswork.

In a example relational model:
If prod (product) had an FK referencing a bom (bill of materials) table and that would be referenced by a bom-parts, a link table, also referenced by parts which would reference the group code lookup table.

7

To use parameters create a stored procedure or table valued function:

CREATE PROCEDURE dbo.MyExample 
@PartNo varchar(10), @GropuCode varchar(10)
AS
BEGIN
SELECT DISTINCT
     p.groupCode
   , p.partNo
   , gcl.groupValue
FROM
   dbo.prod p
JOIN
   dbo.GCLookup gcl
ON p.groupCode LIKE gcl.groupCode 
WHERE p.groupCode = @GroupCode
AND p.PartNo = @PartNo ;
END

IF Object_Id(N'dbo.MyExampleInlineTableValuedFunction')
CREATE FUNCTION dbo.MyExampleInlineTableValuedFunction
(	
	@PartNo varchar(10), @GropuCode varchar(10)
)
RETURNS TABLE 
AS
RETURN 
(
@PartNo varchar(10), @GropuCode varchar(10)
AS
BEGIN
SELECT DISTINCT
     p.groupCode
   , p.partNo
   , gcl.groupValue
FROM
   dbo.prod p
JOIN
   dbo.GCLookup gcl
ON p.groupCode LIKE gcl.groupCode 
WHERE p.groupCode = @GroupCode
AND p.PartNo = @PartNo ;
)
GO