Accept parameters in the combination of columns for retrieving many records

Question

Table1 has 4 columns = Col1 , Col2 , Col3 , Col4

Col1 is id(integer) column = 156 , 159 , 175

Col2 is varchar column = CAA , DFE , EME

Col3 is varchar column = 12345 , 23465 , 43122

Col4 is varchar column = 2 , 3 , 6

I am creating a stored procedure which accepts collection of combinations as parameters

Combinations = Col2 + Col3 + Col4 which returns Col1 value. Similarly to accept collections and returns many values (Col1)

For Example : Need to get the inputs CAA + 12345 + 2 has to return 156. Similarly DFE + 23465 + 43122 has to return 159.

How to accept parameters in the stored procedure for the above scenario? How to accept parameters in the combination of columns for retrieving many records in SQL Server?

Answer 1

I have explored and found the solution.

I have created a table type - having 3 fields

Then, I have used this table type in the stored procedure accepting as a parameter

Then, I have used inner join to accomplish my task.

Answer 2

create proc spstack
@col2 varchar(10),
@col3 varchar(10),
@col4 varchar(10)
as begin
select col1 from #stack where
col2=@col2 and col3=@col3 and col4=@col4
end
execute spstack 'CAA','12345','2'
output-156

Answer 3

CREATE PROCEDURE [procedure-name]
@Col2 nvarchar(3),
@Col3 nvarchar(5),
@Col4 nvarchar(1)

SELECT Col1 from Table1
WHERE Col2 = @Col2 AND Col3 = @Col3 AND Col4 = @Col4

Hope this is what you're asking and helps!