quote:

---

Originally posted by mailtosaja

Hi,
Thanks for your reply..But @project_id it's con't convert to bigint .Because it's contain different formate for example "project_id='1019,1006,1019,1019'" Did you have any other idea...

Thanks & Regards,
S.Sajan

---

declare @project_id varchar(800)
set @project_id='1019,1006,1019,1019'

select *
from trn_defect_details
where
	CAST(i_project_id AS VARCHAR(800)) IN
	(
		( CASE
			when @project_id is null then CAST(i_project_id AS VARCHAR(800))
			when @project_id <> '' then @project_id
		END
		)
	)

DECLARE @project_Id VARCHAR(800)
SET @projectId = '1019,1006,1019,1019'

SELECT
	*
FROM
	trn_defect_details
WHERE
	i_project_id = CASE
		WHEN NULLIF(@project_id, '') IS NULL THEN i_project_id
		ELSE CAST(REPLACE(@project_Id,',','') AS BIGINT)
	END

quote:

---

Originally posted by VGuyz

Hi charlie,
Is there any wrong with my query..?

---

quote:

---

Hi VGuys,
Don't think so. I'm not sure what Original Poster wants though. I'm wondering if he wants to check for any value in a comma separated string or if the commas represent a logical split of a big integer. Like splitting (1019100610191019) into (1019,1006,1019,1019) for readability.

Your query does the former doesn't it? == returns rows that contain any sub values in a comma separated list.

I don't think OP wants this though because why would you search for the same value 3 times? (1019)


-------------
Charlie

---

quote:

---

..)@project_id is a optional parameter user may or may not be give the value
..)@project_id is a Bigint datatype
..)if you are convert the i_project_id into varchar means the condition not match....because '4545,454545,454' this values taken
as single varchar...So this is mismatch.
..)I need the result is get the all column from trn_defect_details table
..)condition is i_project_id in (4545,454545,454)

---

IF EXISTS (
	SELECT * from dbo.sysobjects
	WHERE 
		id = object_id(N'[dbo].[fn_Split]')
		AND xtype IN (N'FN', N'IF', N'TF')
	)
	DROP FUNCTION [dbo].[fn_Split]
GO


CREATE FUNCTION fn_Split (@text VARCHAR(8000), @delimiter VARCHAR(20) = ' ')

	RETURNS @Strings TABLE (
		[position] INT IDENTITY PRIMARY KEY
		, [value] VARCHAR(8000)
		)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (@text)
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
	END
	RETURN
END
GO


DECLARE @project_id varchar(800)
SELECT @project_id = '1019,1006,1019,1019'


SELECT
	*
FROM
	trn_defect_details
WHERE
	(
		@project_Id IS NULL
		OR
		i_project_id IN (SELECT CAST([value] AS BIGINT) FROM dbo.fn_Split( @project_Id, ',' ))
	)