July 27, 2009 at 1:18 pm
I have a stored procedure with the following variables and statements (not exact syntax, just illustrating basic concept here):
alter usp_Get_ProductInfo
@p1 int = 0
@p2 nvarchar(255) = ' '
as
begin
select ProductId, ProductName
from ProductTable
where (ProductId = @p1) and (ProductName like '@p2')
This stored procedure (sproc) is going to be tied to an .ASPX form with two text box controls and a submit button.
How can I make this sproc return all rows of info if nothing is entered into the text box controls ( meaning that no value is supplied to the @p1 and @p2 input parameters)?
Also, if only one parameter value is supplied, how can I get the other one to be ignored. For example if 'Ford%' is entered into the text box for the ProductName, but nothing is entered into the ProductId text box, then the ProductId param would need to be ignored (or replaced with a wildcard).
For the ProductName parameter, I was thinking about checking the IsNull value for an ASPX text control, and if it was true then I would pass a '%' to the @p2 sproc. param. But, I do not see how to do this for a numeric int param. I see no way to pass a numeric wildcard to the sproc for @p1.
If you disregard the fact that I am hooking up a sproc with an ASPX form, how can I handle this from SQL Mgt Studio alone by passing wildcard parameters to this sproc using a simple query?
Thanks in advance,
Pat
July 27, 2009 at 1:40 pm
Hi, I built this procedure for you, hope that it helps you understand how you can easily nullify the results, or return everything, based on the parameters.
USE TEMPDB
GO
CREATE TABLE ProductTAble (
productID INT IDENTITY ( 1 , 1 ),
ProductName NVARCHAR(255))
GO
INSERT INTO ProductTable
SELECT 'Test123'
UNION ALL
SELECT 'ABC1'
UNION ALL
SELECT 'ABC'
UNION ALL
SELECT 'AB'
UNION ALL
SELECT '%ABC'
UNION ALL
SELECT 'TestABC'
UNION ALL
SELECT 'Test234'
GO
CREATE PROCEDURE usp_Get_ProductInfo(
@p1 INT = NULL,
@p2 NVARCHAR(255) = NULL)
AS
BEGIN
SELECT ProductId,
ProductName
FROM ProductTable
WHERE (ProductId = @p1
OR @P1 IS NULL) -- this handles the first parameter issue, when the user does not send information, it displays all
AND (ProductName LIKE @p2
+ '%'
OR @p2 IS NULL) --This handles the like, and is defaulted as
END
GO
EXEC usp_Get_ProductInfo
NULL ,
'TES'
EXEC usp_Get_ProductInfo
6 ,
'TES'
EXEC usp_Get_ProductInfo
NULL ,
'AB'
EXEC usp_Get_ProductInfo
5 ,
NULL
GO
DROP PROCEDURE usp_Get_ProductInfo;
DROP TABLE ProductTable
Tell me if there is something you still need help with.
Cheers,
J-F
July 27, 2009 at 1:54 pm
J-F Bergeron (7/27/2009)
Hi, I built this procedure for you, hope that it helps you understand how you can easily nullify the results, or return everything, based on the parameters.
USE TEMPDBGO
CREATE TABLE ProductTAble (
productID INT IDENTITY ( 1 , 1 ),
ProductName NVARCHAR(255))
GO
INSERT INTO ProductTable
SELECT 'Test123'
UNION ALL
SELECT 'ABC1'
UNION ALL
SELECT 'ABC'
UNION ALL
SELECT 'AB'
UNION ALL
SELECT '%ABC'
UNION ALL
SELECT 'TestABC'
UNION ALL
SELECT 'Test234'
GO
CREATE PROCEDURE usp_Get_ProductInfo(
@p1 INT = NULL,
@p2 NVARCHAR(255) = NULL)
AS
BEGIN
SELECT ProductId,
ProductName
FROM ProductTable
WHERE (ProductId = @p1
OR @P1 IS NULL) -- this handles the first parameter issue, when the user does not send information, it displays all
AND (ProductName LIKE @p2
+ '%'
OR @p2 IS NULL) --This handles the like, and is defaulted as
END
GO
EXEC usp_Get_ProductInfo
NULL ,
'TES'
EXEC usp_Get_ProductInfo
6 ,
'TES'
EXEC usp_Get_ProductInfo
NULL ,
'AB'
EXEC usp_Get_ProductInfo
5 ,
NULL
GO
DROP PROCEDURE usp_Get_ProductInfo;
DROP TABLE ProductTable
Tell me if there is something you still need help with.
This technique works, but it generates a bad query plan. If the query is running on a small table, then it is O.K, but if this code should run on a big table, then you can expect performance problems. Unfortunately the only way that I’ve found that deals with it and creates a good query plan, is using dynamic SQL. I’m aware of the security implications with the dynamic SQL. Would be interesting if someone would show a way that is save and also has an optimal query plan.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 29, 2009 at 6:46 am
I understand that this method might not be a "best practice", but it worked well for my situation.
Your help is greatly appreciated!
Pat
July 29, 2009 at 7:09 am
Thanks for the feedback Pat,
Have a nice day,;-)
Cheers,
J-F
July 30, 2009 at 4:43 am
Hi Pat,
may this can be useful....
Create proc GetResult(
@p1 int = null,
@p2 int = null)
As
Begin
if @p1 is null and @p2 is null
begin
select * from temp
end
else if @p1 is null
begin
Select * from temp where ProductName = @p2
end
else if @p2 is null
begin
Select * from temp where ProductId = @p1 or Convert(varchar,ProductName) like convert(varchar,@p2) + '%'
end
else
select * from temp where ProductId = @p1 or Productname = @p2
Go
If i m wrong anywhere then feel free to correct..
Thanks,
Amit
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply