Stored procedure/Multi-value parameters/IN clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure/Multi-value parameters/IN clause

Author

Topic

rmelnyck
Starting Member

4 Posts

Posted - 2005-10-18 : 16:45:20

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 01:35:44

>>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.

Then you should pass parameter to the Function
Will it work correctly if you pass Valid data to the Function parameter?

Madhivanan

Failing to plan is Planning to fail

rmelnyck
Starting Member

4 Posts

Posted - 2005-10-19 : 08:41:56

Thanks for the response, this is how i did it:

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, ',')
)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 08:50:22

Is it working for you?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources