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 in case the 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. I can have a lot of parameters and and some of them can be empty or not. Thanks. ------------------------------------ To study, study and only to study
-
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 in case the 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. I can have a lot of parameters and and some of them can be empty or not. Thanks. ------------------------------------ To study, study and only to study
You can use conditional logic in your procedure, but you may want to consider why you've got empty parameters coming into a stored procedure - there are valid reasons, but it's nice to try to avoid it. You can also set defaults on parameters to sidestep having to use conditional logic in some cases. Example:
-- These variables would typically be input params declare @param1 nvarchar(10) declare @param2 nvarchar(10) set @param2 = 'Value1' -- These variables just hold the length of the params declare @lenP1 int declare @lenP2 int set @lenP1 = len(@param1) set @lenP2 = len(@param2) -- Outputs the state of the params if @lenP1 > 0 and @lenP2 > 0 select 'Both parameters were provided.' else if @lenP1 > 0 and (@lenP2 = 0 or @lenP2 is null) select 'The first parameter was provided but the second was omitted' else -- This statement will be executed. select 'The second parameter was provided but the first was omitted'The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
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 in case the 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. I can have a lot of parameters and and some of them can be empty or not. Thanks. ------------------------------------ To study, study and only to study
Instead of defaulting the value to an empty string you can leave it as null Then you can do a select like this
CREATE PROCEDURE MyProcedure
@criteria1 int,
@criteria2 varchar(50)
ASSELECT *
FROM MyTable
WHERE column1 = @criteria1
AND (@criteria2 IS NULL OR column2 = @criteria2)
GOThe key here is the
AND... line. What it says is that if @criteria2 is null then the clause is true. If @criteria2 is not null then column2 must equal @criteria2 for the clause to be true. In an OR condition one or both sides must be true for the whole to be true. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Instead of defaulting the value to an empty string you can leave it as null Then you can do a select like this
CREATE PROCEDURE MyProcedure
@criteria1 int,
@criteria2 varchar(50)
ASSELECT *
FROM MyTable
WHERE column1 = @criteria1
AND (@criteria2 IS NULL OR column2 = @criteria2)
GOThe key here is the
AND... line. What it says is that if @criteria2 is null then the clause is true. If @criteria2 is not null then column2 must equal @criteria2 for the clause to be true. In an OR condition one or both sides must be true for the whole to be true. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Yes, thanks it's help. However the statement that u wrote:
AND (@criteria2 IS NULL OR column2 = @criteria2)possible write as: WHERE column1 = @criteria1 AND (column2 = ISNULL(@criteria2, column2)) ------------------------------------ To study, study and only to study -
Yes, thanks it's help. However the statement that u wrote:
AND (@criteria2 IS NULL OR column2 = @criteria2)possible write as: WHERE column1 = @criteria1 AND (column2 = ISNULL(@criteria2, column2)) ------------------------------------ To study, study and only to studyIf you want you can write it that way too. However, I'd think that using a function like this wouldn't be as efficient (but SQL Server often surprises me with what it can do efficiently and what not) - so, if that is important to you then you should consider writing both versions and running them through the query analyser to see which generates the better execution plan.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
If you want you can write it that way too. However, I'd think that using a function like this wouldn't be as efficient (but SQL Server often surprises me with what it can do efficiently and what not) - so, if that is important to you then you should consider writing both versions and running them through the query analyser to see which generates the better execution plan.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
generates the better execution plan, what do u mean by saying it? how to check execution plan in the query analyser. Thank u ------------------------------------ To study, study and only to study
Query-->Show Execution Plan. Or Press Ctrl+K Then run the query. The plan that the query optimiser used will be shown after the query is run. You can examine it to see which is more efficient.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Query-->Show Execution Plan. Or Press Ctrl+K Then run the query. The plan that the query optimiser used will be shown after the query is run. You can examine it to see which is more efficient.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
thanks i got it, I have another question to u: i have stored procedure that perform select ... from .... also this procedure return value (e.g. return 0) if i call to the sp by EXECUTE i got the result of my sp(result of query.) if i do: DECLARE @res int EXEC @res = My Stored proc PRINT @res i got only what the sp return. how to get the return value and the query result(set result) both? ------------------------------------ To study, study and only to study