Link to home
Start Free TrialLog in
Avatar of ajnorton
ajnorton

asked on

How do I pass multiple criteria into a single parameter in vba?

I'd like to be able to pass multiple criteria into a single parameter in a vba code which calls a sql procedure.

In creating the command to call the procedure I've used is:
      cmd1.Parameters("@clientcode") = "AB3,BM1"
which returns no result.

The section of the sql code which relates to the parameter is:
      where T01.ClientCode in (@clientcode)

Any thoughts?  
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Create the function below, then change your code from

      where T01.ClientCode in (@clientcode)

to

      where T01.ClientCode in (select value from values2table(@clientcode, ','))
CREATE function [dbo].[values2table]
(
@values varchar(max),
@separator varchar(2)
) returns @res table ([value] varchar(100))
as
begin
declare @value int
declare @commapos int, @lastpos int
set @commapos = 0
select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
while @commapos > @lastpos
begin
	select @value = substring(@values, @lastpos+1, @commapos-@lastpos-1)
	insert into @res select @value where @value <> ''
	select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
end
select @value = substring(@values, @lastpos+1, len(@values))
insert into @res select @value where @value <> ''
return
end
GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of WiB
WiB

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ajnorton
ajnorton

ASKER

Thanks for the reply.
I've just got to duck out of office and will look at it in more detail tomorrow.
Thanks again!
Sorry its taken so long in getting back.

Thanks for the help!