March 9, 2005 at 11:14 am
Hi,
I am trying to create a stored procedure. This stored procedure should display the anomalies results. The Anomalies could be records with Price <= 0 or Cost <= 0 or Qty <= 0. The Price, Cost and Qty are the columns in the table. Instead of having separate stored procedure for each anomaly (price, Cost, Qty) I want to create a generic stored procedure and pass to this stored procedure the where condition(which states whether we need Price or Cost or Qty Anomalies) as input parameter. When I create the stored procedure I'm getting the syntax error. Below is the sample code for stored procedure
Code
-----
CREATE PROCEDURE spAnomResults
(@WhereCondition VARCHAR(100)
@StartDate datetime,
@EndDate datetime)
AS
SELECT * FROM table where '+@WhereCondition+'
and Period between @StartDate and @EndDate
GO
Please let me know how I could do this.
THanks,
Sridhar!!
March 9, 2005 at 11:31 am
Search the forums on 'Dynamic SQL'
Search the forums on 'SQL Injection' to see why you shouldn't use it.
March 9, 2005 at 12:24 pm
Create proc spAnomResults @qte as int = null, @Price as decimal(18,4) = null, @Cost as decimal(18,4) = null, @DateStart as datetime, @DateEnd as datetime
Select * from dbo.Orders where OrderDate between @DateStart and @DateEnd
and (Qte < @qte or @qte is null)
and (Price < @Price or @Price is null)
and (Cost < @Cost or @Cost is null)
March 9, 2005 at 12:26 pm
Article on dynamic sql and why it should NOT be used... especially in this case.
March 9, 2005 at 12:59 pm
Thank you All. I have used Remi Gregoire's code. It is working.
Thanks Remi Gregoire.
Thanks,
Sridhar!!
March 9, 2005 at 1:02 pm
HTH
March 9, 2005 at 2:05 pm
Hi,
Now I have to perform two more Anomalies. One is (Cost > Price) and the other is All Anomalies i.e. Price <= 0 or Cost <= 0 or Qty <= 0. Is there a way to insert this logic in the previous stored procedure. Suppose if i use Dynamic Sql and if i want to pass date variables how could I that. I'm getting an error when I try to execute the stored procedure which has dynamic sql and contains date variables in that dynamic sql.
Following is the sample
DECLARE @WhereCondition VARCHAR(100)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
EXEC ('select * from table1' + @WhereCondition + 'Period between ' + @StartDate + ' AND ' @EndDate')
Please let me know if i need to place any extra code for this to work.
Thanks,
Sridhar!!
March 9, 2005 at 2:21 pm
I'm not gonna say this again to you. DON'T USE DYNAMIC SQL FOR THIS.
Create proc spAnomResults @qte as int = null, @Price as decimal(18,4) = null, @Cost as decimal(18,4) = null, @DateStart as datetime, @DateEnd as datetime, @CostPriceCheck as bit = null
Select * from dbo.Orders where OrderDate between @DateStart and @DateEnd
and (Qte < @qte or @qte is null)
and (Price < @Price or @Price is null)
and (Cost < @Cost or @Cost is null)
and ((@CostPriceCheck = 1 AND Cost < Price) OR @CostPriceCheck IS NULL)
Keep in mind that everytime you pass one more parameter, you add a cumulative condition. If it's not what you need write back and I'll try to help you out some more.
March 9, 2005 at 2:50 pm
Thanks Remi Gregoire. I have worked it out using your sample. I appreciate your help. I'm new to Sql Server. I have started it 6 months back. And I'm learning to do things efficiently. Thanks for your feedback.
Sridhar!!
March 10, 2005 at 2:06 am
Hi,
I'm new to SQL myself and I was admiring the code snippet from Remi.
Should the conditions being checked be "OR" rather than "AND" as all the conditions need to agree to have it display - correct me if I'm wrong as I want to learn.
Remi - I have saved your code as a worthwhile example - thanks
Richard
March 10, 2005 at 5:59 am
Think about what you just said.
Create proc spAnomResults @qte as int = null, @Price as decimal(18,4) = null, @Cost as decimal(18,4) = null, @DateStart as datetime, @DateEnd as datetime, @CostPriceCheck as bit = null
Select * from dbo.Orders where OrderDate between @DateStart and @DateEnd
and
(
(Qte < @qte or @qte is null)
or (Price < @Price or @Price is null)
or (Cost < @Cost or @Cost is null)
or ((@CostPriceCheck = 1 AND Cost < Price) OR @CostPriceCheck IS NULL))
If any of the nullable parameters are set to null then ALL the records would come back from that DateRange.
I'll try to think of a way to use the or operator for this stored proc, but this will be a first for me...
March 10, 2005 at 6:06 am
Maybe something like this could be a substitute for the OR requirements :
Create proc spAnomResults @qte as int = null, @Price as decimal(18,4) = null, @Cost as decimal(18,4) = null, @DateStart as datetime, @DateEnd as datetime, @CostPriceCheck as bit = null
as
Select * from dbo.Orders where OrderDate between @DateStart and @DateEnd
and
0 < CASE
WHEN NOT @qte IS NULL AND Qte < @qte THEN 1
WHEN NOT @Price IS NULL AND Price < @Price THEN 1
WHEN NOT @Cost IS NULL AND Cost < @Cost THEN 1
WHEN NOT @CostPriceCheck IS NULL AND Cost < Price THEN 1
ELSE 0 END
March 10, 2005 at 7:39 am
Hi,
Why don't we use And clause in the query? The logic seems correct to me if we use And clause.
Create proc spAnomResults @qte as int = null, @Price as decimal(18,4) = null, @Cost as decimal(18,4) = null, @DateStart as datetime, @DateEnd as datetime, @CostPriceCheck as bit = null
Select * from dbo.Orders where OrderDate between @DateStart and @DateEnd
and (Qte < @qte or @qte is null)
and (Price < @Price or @Price is null)
and (Cost < @Cost or @Cost is null)
and ((@CostPriceCheck = 1 AND Cost < Price) OR @CostPriceCheck IS NULL)
Suppose if call the procedure like EXEC spAnomResults '2/25/2005','3/3/2005',@Qty=0
then the query becomes
Select * from dbo.Orders where OrderDate between '2/25/2005' and '3/3/2005'
and (Qte < 0 or 0 is null)
and (Price < null or null is null)
and (Cost < null or null is null)
and ((@CostPriceCheck = 1 AND Cost < Price) OR null IS NULL)
Since null is null will evaluate to true I think the query becomes
Select * from dbo.Orders where OrderDate between '2/25/2005' and '3/3/2005'
and (Qte < 0)
which is what I wanted. Please correct me if I'm wrong and let me know whether I need to use And or 'OR' clause.
Thanks,
Sridhar!!
March 10, 2005 at 8:02 am
Depends on what you need.
exec spAnomResults qte = 1, Cost = 1
WITH ORS
Create proc spAnomResults @qte as int = null, @Price as decimal(18,4) = null, @Cost as decimal(18,4) = null, @DateStart as datetime, @DateEnd as datetime, @CostPriceCheck as bit = null
as
Select * from dbo.Orders where OrderDate between @DateStart and @DateEnd
and
0 < CASE
WHEN NOT 1 IS NULL AND Qte < 1 THEN 1
WHEN NOT @Price IS NULL AND Price < @Price THEN 1
WHEN NOT 1 IS NULL AND Cost < 1 THEN 1
WHEN NOT @CostPriceCheck IS NULL AND Cost < Price THEN 1
ELSE 0 END
WITH ANDS
Select * from dbo.Orders where OrderDate between @DateStart and @DateEnd
and (Qte < 1 or 1 is null)
and (Price < null or null is null)
and (Cost < 1 or 1 is null)
and ((@CostPriceCheck = 1 AND Cost < Price) OR null IS NULL)
the 2nd query will return all the lines ONLY IF BOTH Qte and Cost are under 1. While the first query would return a row if EITHER or both of 'em are under 1.
I think the case version is more flexible as you can check for more problems at once... and it should be pretty fast too since the case is a shortcircuit meaning that once any condition is met, nothing else gets checked. But now that I think of it, it might start a clustered index scan, but you'd have to do testing to see which one runs faster assuming they both fulfill your needs.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply