Question about SELECT

Suppose i create sp
CREATE PROC myProc @.criteria1 int,
@.criteria2 nvarchar(50) = ''
AS
SELECT * FROM myTable
WHERE column1 = @.criteria1
AND
column2 = @.criteria2
GO
my question is if parameter @.criteria2 is empty (equal '')
i want the select will ignore this criteria
so it perform like:
SELECT * FROM myTable
WHERE column1 = @.criteria1
the same with other criterias
Message posted via http://www.webservertalk.comE B via webservertalk.com wrote:
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
>
WHERE
(column1 = @.criteria1 OR @.criteria1 ='')
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Simply try this:
CREATE PROC myProc @.criteria1 int,
@.criteria2 nvarchar(50) = ''
AS
Begin
If @.criteria2 = ''
SELECT * FROM myTable
WHERE column1 = @.criteria1
Else
SELECT * FROM myTable
WHERE column1 = @.criteria1
AND column2 = @.criteria2
End
Gert-Jan
"E B via webservertalk.com" wrote:
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> --
> the same with other criterias
> --
> Message posted via http://www.webservertalk.com|||however if i have suppose 20 paramaters, so i need to write a lot of SELECT
...., because it's a lot of combination.
Any ideas?
Message posted via http://www.webservertalk.com|||This should do. However, for some reason, this query runs very slowly.
SELECT *
FROM myTable
WHERE column1 = @.criteria1
AND column2 = ISNULL(@.criteria2,Column2)
Gopi
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:912aa0dbc4fb4c68b84db0a061654c2d@.SQ
webservertalk.com...
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> --
> the same with other criterias
> --
> Message posted via http://www.webservertalk.com|||Thanks u. u r the best
Message posted via http://www.webservertalk.com