I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNTON;
SET ANSI_WARNINGSOFF
SET @.where=''
IF @.JobNoStart!=''SET @.where=+' AND LJOB BETWEEN @.JobNoStart AND @.JobNoEnd'
IF @.OrderDateStart!=''SET @.where=+' AND JOBDATE BETWEEN @.OrderDateStart AND @.OrderDateEnd'
IF @.DueDateStart!=''SET @.where=+' AND DUEDATE BETWEEN @.DueDateStart AND @.DueDateEnd'
IF @.ProofDateStart!=''SET @.where=+' AND PROOFDUE BETWEEN @.ProofDateStart AND @.ProofDateEnd'
IF @.CloseDateStart!=''SET @.where=+' AND CLOSEDATE BETWEEN @.CloseDateStart AND @.CloseDateEnd'
IF @.CogsDateStart!=''SET @.where=+' AND COGSDATE BETWEEN @.CogsDateStart AND @.CogsDateEnd'
IF @.ProductName!=''SET @.where=+' AND PRODUCT = @.ProductName'
IF @.CustomerNumber!=''SET @.where=+' AND FCUSTNO = @.CustomerNumber'
IF @.SalesPerson!=''SET @.where=+' AND FSALESPN = @.SalesPerson'
IF @.CSR!=''SET @.where=+' AND JOBPER = @.CSR'
IF @.Closed= 0SET @.where=+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'
ELSEIF @.Closed= 1SET @.where=+' AND CLOSEDATE IS NULL'
ELSEIF @.Closed= 2SET @.where=+' AND CLOSEDATE IS NOT NULL'
IF @.Canceled= 0SET @.where=+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'
ELSEIF @.Canceled= 1SET @.where=+' AND CANCDATE IS NOT NULL'
ELSEIF @.Canceled= 2SET @.where=+' AND CANCDATE IS NULL'
IF @.FinalShip= 0SET @.where=+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'
ELSEIF @.FinalShip= 1SET @.where=+' AND FINALSHIP IS NOT NULL'
ELSEIF @.FinalShip= 2SET @.where=+' AND FINALSHIP IS NULL'
SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL @.where
END
To answer my own post, this is how you build a dynamic query in a stored proc:
ALTERPROCEDURE [dbo].[cg_JobSearch]
-- Add the parameters for the stored procedure here
@.JobNoStart varchar(10)='',
@.JobNoEnd varchar(10)= @.JobNoStart,
@.OrderDateStart varchar(10)='',
@.OrderDateEnd varchar(10)= @.OrderDateStart,
@.DueDateStart varchar(10)='',
@.DueDateEnd varchar(10)= @.DueDateStart,
@.ProofDateStart varchar(10)='',
@.ProofDateEnd varchar(10)= @.ProofDateStart,
@.CloseDateStart varchar(10)='',
@.CloseDateEnd varchar(10)= @.CloseDateStart,
@.CogsDateStart varchar(10)='',
@.CogsDateEnd varchar(10)= @.CogsDateStart,
@.ProductName varchar(200)='',
@.CustomerNumber varchar(12)='',
@.SalesPerson varchar(3)='',
@.CSR varchar(15)='',
@.Closedint= 0,
@.Canceledint= 0,
@.FinalShipint= 0,
--@.Invoiced int = 0,
@.where varchar(8000)='',
@.sql varchar(8000)=''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNTON;
SET ANSI_WARNINGSOFF
SET @.where=''
IF @.JobNoStart!=''SET @.where= @.where+' AND LJOB BETWEEN '+ @.JobNoStart+' AND '+ @.JobNoEnd
IF @.OrderDateStart!=''SET @.where= @.where+' AND JOBDATE BETWEEN '+ @.OrderDateStart+' AND '+ @.OrderDateEnd
IF @.DueDateStart!=''SET @.where= @.where+' AND DUEDATE BETWEEN '+ @.DueDateStart+' AND '+ @.DueDateEnd
IF @.ProofDateStart!=''SET @.where= @.where+' AND PROOFDUE BETWEEN '+ @.ProofDateStart+' AND '+ @.ProofDateEnd
IF @.CloseDateStart!=''SET @.where= @.where+' AND CLOSEDATE BETWEEN '+ @.CloseDateStart+' AND '+ @.CloseDateEnd
IF @.CogsDateStart!=''SET @.where= @.where+' AND COGSDATE BETWEEN '+ @.CogsDateStart+' AND '+ @.CogsDateEnd
IF @.ProductName!=''SET @.where= @.where+' AND PRODUCT = '+ @.ProductName
IF @.CustomerNumber!=''SET @.where= @.where+' AND FCUSTNO = '+ @.CustomerNumber
IF @.SalesPerson!=''SET @.where= @.where+' AND FSALESPN = '+ @.SalesPerson
IF @.CSR!=''SET @.where= @.where+' AND JOBPER = '+ @.CSR
IF @.Closed= 0SET @.where= @.where+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'
ELSEIF @.Closed= 1SET @.where= @.where+' AND CLOSEDATE IS NULL'
ELSEIF @.Closed= 2SET @.where= @.where+' AND CLOSEDATE IS NOT NULL'
IF @.Canceled= 0SET @.where= @.where+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'
ELSEIF @.Canceled= 1SET @.where= @.where+' AND CANCDATE IS NOT NULL'
ELSEIF @.Canceled= 2SET @.where= @.where+' AND CANCDATE IS NULL'
IF @.FinalShip= 0SET @.where= @.where+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'
ELSEIF @.FinalShip= 1SET @.where= @.where+' AND FINALSHIP IS NOT NULL'
ELSEIF @.FinalShip= 2SET @.where= @.where+' AND FINALSHIP IS NULL'
SET @.sql='SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN FROM BBJTHEAD WHERE LJOB IS NOT NULL'
SET @.sql= @.sql+ @.where
PRINT @.sql
EXEC(@.sql)
END
|||SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL
AND ((@.JobNoStart='') OR (LJOB BETWEEN @.JobNoStart AND @.JobNoEnd))
AND ...
AND ((@.Closed<>0) OR ( CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL))
AND ((@.Closed<>1) OR (CLOSEDATE IS NULL))
AND ((@.Closed<>2) OR (CLOSEDATE IS NOT NULL))
AND ...
Which if you see the pattern, I flip the condition of your IF for the first part, and use the where condition in each of your IFs as the second part. Like:
AND ((reversed IF condition) OR (Your where clause minus the AND))
The SQL in pink is also worthless, it will always be true. Also, the way you are concatenating your OR's and AND's will cause your where clause to not behave the way you want. AND has precedence over OR, where it looks like you want OR to have precedence over AND. As written, if Closed, FinalShip, or Cancelled is 0, you will return data that I suspect you didn't want returned.
If you use EXEC like the above poster suggested (after you fix the AND/OR problems I mentioned, and some errors the above poster made), you'll end up with a SP that can suffer SQL Injection. You are much better off either using the approach I mentioned above, or using the stored procedure that takes both a query string, and a parameter string (Which is a bit more difficult to set up, which is why unless performance is really bad, I use the above). I believe you are looking for the stored procedure sp_ExecuteSQL.
|||Thanks for the advice. It is true that with my current proc I'm susceptable to an injection attack. That would not be good.|||This is how I ended up doing mine.
CREATE PROCEDURE [dbo].[pe_getAppraisals]
-- Add the parameters for the stored procedure here
@.PTypenvarChar(500),
@.ClientnvarChar(500),
@.PageSizeINT
AS
DECLARE
@.l_SelectnvarChar(4000),
@.l_FromnvarChar(4000),
@.l_SetWherebit,
@.l_PTypenvarChar(500),
@.l_ClientnvarChar(500)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
--Initialize SetWhere to test if a parameter has Added the keyword WHERE
--Initialize the Where statement in case all parameters are null
SET @.l_SetWhere= 0
--Create WHERE portion of the SQL SELECT Statement
IF(@.PTypeISNOTNULL)AND(@.PType<>'')
BEGIN
SET @.l_PType=' WHERE o.PropertyTypeID='+ @.PType
SET @.l_SetWhere= 1
End
ELSESET @.PType=''
IF(@.ClientISNOTNULL)AND(@.Client<>'')
BEGIN
IF @.l_SetWhere= 0
BEGIN
SET @.l_Client=' WHERE o.ClientID='+ @.Client
SET @.l_SetWhere= 1
END
ELSESET @.l_Client=' AND o.ClientID='+ @.Client
END
ELSESET @.l_Client=''
--Build the SQL SELECT Statement
SET @.l_Select=
'o.OrderID, o.FileNumber, o.OrderDate, o.ClientID, o.ClientFileNumber, o.PropertyTypeID, o.EstimatedValue, o.PurchaseValue,
o.LoanOfficer, o.ReportFee, o.FeeBillInd, o.FeeCollectInd, o.CollectAmt, o.Borrower, o.StreetAddrA, o.StreetAddrB, o.City, o.State, o.Zip,
o.ContactName, o.PhoneA, o.PhoneB, o.ApptDate, o.ApptTime, o.AppraiserID, o.InspectionDate, o.DateMailed, o.TrackingInfo, o.ReviewedBy,
o.StatusID, o.Comments, o.SpecialNotes, o.EmailInd, o.MgmtName, o.MgmtContactName, o.MgmtAddress, o.MgmtPhone, o.MgmtFax,
o.MgmtFee, o.MgmtNotes, o.LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc,
ot.ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq, pc.PriceDesc, pt.PropertyTypeDesc, l.LoginName AS AppraiserName'
SET @.l_From=
'Orders AS o LEFT OUTER JOIN
OrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOIN
OrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOIN
OrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOIN
OrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOIN
PriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOIN
PropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOIN
Logins AS l ON o.AppraiserID = l.LoginID'
Execute('SELECT TOP('+ @.PageSize+') '+ @.l_Select+' FROM '+ @.l_From+ @.l_PType+ @.l_Client)
No comments:
Post a Comment