How to compare multiple parameters of a row column value?

Question

how to write query for following request? my table:

id        designation
1         developer,tester,projectlead
1         developer
1         techlead

 if id=1,designation="'developer'"

Then need to first,second records.Because 2 rows are having venkat. if id=1,designation="'developer','techlead'" then need to get 3 records as result. i wrote one service for inserting records to that table .so that i am maintaining one table to store all designation with same column with comas.

By using service if user pass id=1 designation="'developer','techlead'" then need to pull the above 3 records.so that i am maintaining only one table to save all designations

SP:

ALTER PROCEDURE [dbo].[usp_GetDevices]
@id INT,
@designation NVARCHAR (MAX)
AS

BEGIN
declare @idsplat varchar(MAX)
set @idsplat = @UserIds
    create table #u1 (id1 varchar(MAX))
    set @idsplat = 'insert #u1 select ' + replace(@idsplat, ',', ' union select ')
    exec(@idsplat)
  Select 
    id FROM dbo.DevicesList WHERE id=@id  AND designation IN (select id1 from #u1)
  END

score 3 · Accepted Answer · edited May 23 '17 at 12:04

3

You need to use the boolean operators AND and OR in conjunction with LIKE:

IF empid = 1 AND (empname LIKE '%venkat%' OR empname LIKE '%vasu%')

The above example will return all rows with empid equals 1 and empname containing venkat or vasu.

Apparently you need to create that query based on the input from user, this is just an example of how the finally query should look like.

Edit: Trying to do this within SqlServer can be quite hard so you should really change your approach on how you call the stored procedure. If you can't do this then you could try and split your designation parameter on , (the answers to this question show several ways of how to do this) and insert the values into a temporary table. Then you can JOIN on this temporary table with LIKE as described in this article.

edited May 23 '17 at 12:04

Community

1
1

answered Apr 01 '12 at 06:52

ChrisWue

18,612
4
58
83

we don't need to hard code.if user pass id=1 and desigantion="'developer','tester','techlead','projectlead'" like that pass multiple parameters .Then need to pull developer,tester,techlead,projectlead records matching with passing id...... – user1237131 Apr 01 '12 at 07:04
@user1237131: Of course you need to create that query based on the input from user, this is just an example of how the finally query should look like. – ChrisWue Apr 01 '12 at 07:08
How to dynamically write empname LIKE '%venkat%' OR empname LIKE '%vasu%' for suppose 5 users are there then how ?for suppose 10 .please tell me? – user1237131 Apr 01 '12 at 07:47

How to compare multiple parameters of a row column value?

1 Answers1