Multiple parameters/Stored procedure/IN clause

[rmelnyck]

I have a stored procedure that contains a parameter that may have multiple values or may be null. If I pass in one or more values for the parameter it works fine but if I do not pass in a parameter it gives me only the records that have a 'null' value instead of all the records.

The code is below as well as the function which handles the multiple parameters.

Any help would be much appreciated. The IN clause is what is messing me up, if it was '=' instead of IN it would be much easier.

---------------------------------------------------------
SELECT *
FROM Content
INNER JOIN Links
ON Content .ContentID = Links.ContentID
WHERE Content.ContentType IN (
Select value from dbo.listToTable(@vcContentType, ',')
)


ALTER FUNCTION listToTable(@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
Position int,
Value varchar(8000)
)
AS
BEGIN
declare @myPos int
set @myPos = 1

while charindex(@delim, @list) > 0
begin
insert into @listTable(Position, Value)
values(@myPos, left(@list, charindex(@delim, @list) - 1))

set @myPos = @myPos + 1
if charindex(@delim, @list) = len(@list)
insert into @listTable(Position, Value)
values(@myPos, '')
set @list = right(@list, len(@list) - charindex(@delim,
@list))
end

if len(@list) > 0
insert into @listTable(Position, Value)
values(@myPos, @list)

RETURN
END

 

[hmckillop]

change this to be

SELECT *
FROM Content
INNER JOIN Links
ON Content .ContentID = Links.ContentID
WHERE @vcContentType iS NULL OR Content.ContentType IN (
 Select value from dbo.listToTable(@vcContentType, ',')
)

This should mean it will only compare the other stuff when it needs to i.e. there are values in the variable @vcContentType


"I'm living so far beyond my income that we may almost be said to be living apart

 

[rmelnyck]

Perfect! Thanks..