I'm trying to code a storedprocedure which looks for a product in a table. It takes one Paramater. Value may be 1 or 2 or 3.
CREATE PROCEDURE [dbo].[procSearchProduct]
@.PromoPrm int
AS
BEGIN
SELECT *
FROM tblProduct AS P
WHERE ?
END
END
--
if @.PromoPrm = 1 then where statement will be like this one: WHERE P.Promo = 'True'
else if @.PromoPrm = 2 then where statement will be like this one: WHERE P.Promo = 'False'
else if @.PromoPrm = 3 then where statement will be like this one: WHERE P.Promo = 'False' OR P.Promo = 'True'
Hope i am clear on my problem. Let me know if you don't understand it. Waiting for a solution.
Happy Coding...
One method is to us if/else:
IF @.PromoPrm = 1
select * from tblProduct p
where p.promo = 'True'
else if @.promo = 2
select * from tblProduct p
where p.promo = 'False'
else if @.promoPrm = 3
select * from tblProduct p
where p.promo = 'False'
or p.promo = 'True'
If your PROMO column can only have the values of TRUE or FALSE you can leave out the WHERE condition on the IF @.promoPrm = 3 portion.
The next comment is that you should avoid using the SELECT * syntax in stored procedures. Give a look to this post concerning this issue:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1108698&SiteID=1
Use of the 'SELECT *' syntax in stored procedures can leave behind "land mines" that go off at a later date and time when changes are made to the columns of the source table.
Another approach to the problem is something like:
|||select * from tblProduct p
where @.promoPrm = 1
and p.promo = 'TRUE'
or @.promoPrm = 2
and p.promo = 'FALSE'
or @.promoPrm = 3
and ( p.promo = 'TRUE' OR
p.promo = 'FALSE'
)
Here's a version using dynamic SQL.
declare @.sql varchar(max);
declare @.where varchar(max);
set @.where = case @.promoprm
when 1 Then'WHERE Promo = ''True'''
when 2 Then 'WHERE Promo = ''False'''
when 3 Then 'WHERE Promo = ''True'' OR Promo = ''False'''
end;
set @.sql = 'SELECT * FROM tblProducts ' + @.where;
exec(@.sql);
Jamie
|||Yeap it is a solution but i gave little part of the code. Trying to make a seach form in asp.net . And u know this kind of page sends many parameters to sql. I can't make all combination in sql. I mean what about if this stored procedure takes 5 Parameters like @.PromoPrm . Point this problem is i have to write a code which makes decision on WHERE part. May be like this one :
SELECT *
FROM tblProduct AS P
WHERE case when P.Promo = 1 then P.Promo = 'True' else P.Promo = 'False'
This query is wrong but i hope u got the point?
|||Beware using any dynamic SQL, especially in a web environment. You don't want to leave yourself open to code injection.
Jamie
|||To simplyfy this,
|||
Code Snippet
select * from tblProduct p
where @.promoPrm & 1 = 1 and p.promo = 'TRUE'
or @.promoPrm & 2 = 2 and p.promo = 'FALSE'
Give a look to the "dynamic search" article by MVP Erland Sommarskog:
http://www.sommarskog.se/dyn-search.html
Also, it will help if you finish describing your problem rather giving information in portions.
|||JHunter i think your solution is good but sql injection is important in this stiuation. If i use your code sample than i need to check the paramateres in my code but it is very very difficult because it takes it from query string. Is there any solution to this problem?
Kent thanks for post. Also thanks for link that u gave.
|||
ManiD wrote:
To simplyfy this,
Code Snippet
select * from tblProduct p
where @.promoPrm & 1 = 1 and p.promo = 'TRUE'
or @.promoPrm & 2 = 2 and p.promo = 'FALSE'
ManiD i think your solution is the best. What about the performance. Because i will use this on 5 different paramaters.
|||If you are only passing an integer to the stored proc, leaving the proc to decide how the where clause is constructed, you'll be okay. Problems come when you start passing strings that contain SQL as parameters .
The simplest check you can do is make sure the parameter contains only "expected" values.
Erlands (as previously posted) page provides some of the best content relating to dynamic SQL.
Jamie
|||How many possible values are there for the PROMO column? If it is but a handful this will probably scan and not seek because of the cardinalities.|||Ok here is the problem again: I'm trying to make a search form. Form runs a stored procedure call it procSearchProduct. This stored procedure takes 6 parameters.
@.CityPrm varchar(50), --takes the name of city
@.HasPromotionPrm int, -- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False
@.HasDescriptionPrm, int,-- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False
@.HasImagePrm int,-- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False
@.StateofShopPrm varchar(50), -- takes the name of the state
@.ShopClosedOpenedPrm int -- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False
what i am trying to do is select values from table. You know the rest. Sory i couldn't paste the whole code because i wrting it on dofferent language so it will not help.
|||I'd avoid passing CityPrm and StateofShopPrm as varchars. Instead, create lookup tables for the city and state with a numeric primary key.
Populate any dropdown menus from these lookups, but capture the primary key (ID) of the selected value - not the name. This numeric ID can be passed to the stored procedure.
If you've not normalised your main table, you can use the primary key to lookup the textual value for the lookup.
Jamie
|||Also, on these VARCHAR fields, are you doing = searches or LIKE searches?|||Another point , user selects the option "WHOLE WORD" or "SOME OF THEM" in drop down for search condition on City but it will mess the problem much more. Assume that i make = search. And I cant take the State in to a dropdownlist. May be a solution but not for me.
This is what I have in my vb file for the webservice....