How to dynamically write the query in SQL Server 2008?
- by user1237131
How to write the dynamically the below query?
Table
empid designation interestes
1 developer,tester cricket,chess
1 developer chess
1 techlead cricket
Condition:
IF empid = 1
AND (designation LIKE '%developer%' OR designationLIKE '%techlead%')
OR (interests LIKE '%cricket%').
How to write the above query dynamically if designations need to send more than 2,and also same on interstes .
please tell me ...
EDIT stored procedure code:
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
