problem using default value in SP

I created an SP for searching rows in a table.
Create Proc stp_search
@.recID varchar(100) = '%'
As
Select * from tbl1
Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
The SP works if I pass in only 1 recordID, or if I don't pass in anything
(default val of %). But I want to be able to search for multiple recID's, s
o
I created a UDF that returns a table of recID's. If the UDF returns one
recID in the return table, the SP works fine. But if I pass in a string wit
h
2 or more recID's (separated by commas - thus varchar), I get this error
message:
'Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
The statement has been terminated.'
My goal is to be able to pass in multiple recID's, or just one, or none -
using the default value. My options to achieve this is to have multiple
select statements like this wrapped inside of IF Else:
if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
select * from tbl1 where recID in coalesce(@.recID, recID)
Else
select * from tbl1 where recID in (select * from dbo.udf(@.recID)
The problem is that the actual select statment is quite large and I have 10
parameters. So the IF else thing would be quite verbose (because I am
actually searching on several tables in this SP - one of the params specifie
s
which table).
So my question is if there is a way to implement the functionality of my
UDF which can return more than one value in the subquery (searching on more
than one recID) and still be able to use the default value (with coalesce)
without having to do the If Else thing.
Any suggestions appreciated (I hope I am not stuck with verbose - it would
be a book)
Thanks,
RichSee if this helps.
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>|||I think that my real question should be
Is there a way to implement a form of If Else inside a Where clause?
Declare @.recIDLen int
select @.recIDLen = len(@.recID) - len(replace(recID, ',',''))
select * from tbl1 Where
Case when @.recIDLen = 0 Then recID = coalesce(@.recID, recID)
Case When @.recIDLen > 0 Then recID In (select * from dbo.UDF(@.recID))
Is there a way to do something like this?
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>|||try this
select * from tbl1 Where
(@.recIDLen = 0 and recID = coalesce(@.recID, recID)) or
(recID In (select * from dbo.UDF(@.recID))
"Rich" wrote:
> I think that my real question should be
> Is there a way to implement a form of If Else inside a Where clause?
> Declare @.recIDLen int
> select @.recIDLen = len(@.recID) - len(replace(recID, ',',''))
> select * from tbl1 Where
> Case when @.recIDLen = 0 Then recID = coalesce(@.recID, recID)
> Case When @.recIDLen > 0 Then recID In (select * from dbo.UDF(@.recID))
> Is there a way to do something like this?
>
> "Rich" wrote:
>|||Thanks all for your replies. And Omnibuzz gave me an even better idea. I
will add a varchar parameter which gets the string of recordIDs or default
value of '%'. If this param is '%' then @.recordID int will be used as the
parameter of choice. I was having a problem with the recID column which is
actually an int.
Anyway, this also solved another problem I was having pulling date columns
that contain nulls. I can bypass using a date params with the OR operator.
Thanks again.
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>