question

Murali avatar image
Murali asked

in operator

DECLARE @JOB VARCHAR(50) ='''MANAGER'',''CLERK''' PRINT @DEPTNO SELECT * FROM emp WHERE JOB IN(@JOB) here i am not fetching any rows .....what could be the reason...the same thing if we execute by using dynamic sql it is working ....
t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
If you look at the SQL that is being executed you would see just **one** entry in the IN list and it would have the value ''MANAGER'',''CLERK'' This is not the same as having two entries MANAGER, CLERK. Doing this in dynamic SQL, the quotes around the job 'list' are removed. To do this in a non-dynamic way, create a set of data (e.g. table, inline-view etc) SELECT * FROM emp WHERE JOB IN (select 'MANAGER' as [Job] union SELECT 'CLERK')
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
You can't do it in the way you describe but you can do it this way: SET @JOB = 'Manager;Clerk' SET @JOB = ';' + @JOB + ';' Select * from #emp where patindex('%;' + JOB + ';%' , @JOB) 0 Hope this helps
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I would do it using a Numbers or Tally table and a split function. First create the Numbers table: SELECT top 100000 IDENTITY(int,1,1) AS N INTO dbo.Nummer FROM sys.all_columns c1 cross join sys.all_columns c2 cross join sys.all_columns c3 --Clustered index is pretty important ALTER TABLE dbo.Nummer ADD CONSTRAINT PK_Nummer PRIMARY KEY(N) Then the Split function CREATE FUNCTION [dbo].[Split](@s nvarchar(MAX), @c nchar(1)) RETURNS @Elements Table( Position INT IDENTITY(1,1) PRIMARY KEY, Value NVARCHAR(4000) ) AS BEGIN SET @s= @c + @s + @c INSERT INTO @Elements (Value) SELECT SUBSTRING(@s,N+1,CHARINDEX(@c,@s,N+1)-N-1) FROM dbo.Nummer WHERE N < LEN(@s) AND SUBSTRING(@s,N,1) = @c RETURN END Finally your query: DECLARE @JOB VARCHAR(50) ='MANAGER,CLERK' SELECT * FROM emp WHERE JOB IN (select Value from dbo.Split(@JOB,','))
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.