jtrapat1
asked on
Building Dynamic Query with ORDER BY and WHERE Clause
I would like to rewrite the attached stored procedure as a dynamic sql statement instead of using the included IF ELSE logic.
My question is - Can this be done?
How much of my query would change?
For example - I need to convert the start and end dates coming in from 6-characters to a valid date format;
Also, I have complicated statements inside my WHERE clause - i.e., the UNIONS that I need for the user to select from the Reporting Services GUI;
And lastly, the ORDER BY which is a selection passed into the stored procedure from a drop down list selection.
(by the way, I couldnt get this ORDER BY working - its outside each loop - it should work....)
Can this be done;
Or should I keep the following code and get the ORDER BY working?
Thanks
John
My question is - Can this be done?
How much of my query would change?
For example - I need to convert the start and end dates coming in from 6-characters to a valid date format;
Also, I have complicated statements inside my WHERE clause - i.e., the UNIONS that I need for the user to select from the Reporting Services GUI;
And lastly, the ORDER BY which is a selection passed into the stored procedure from a drop down list selection.
(by the way, I couldnt get this ORDER BY working - its outside each loop - it should work....)
Can this be done;
Or should I keep the following code and get the ORDER BY working?
Thanks
John
USE [finOSCdata]
GO
/****** Object: StoredProcedure [dbo].[uspGetTransDetails] Script Date: 05/10/2009 18:04:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[uspGetTransDetails]
(
--required parameters
@startdate SMALLDATETIME,
@enddate SMALLDATETIME,
-----optional parameters
@transcode CHAR(3)=NULL,
@agency_code CHAR(5)=NULL,
@fundcode CHAR(10)=NULL,
@pcode CHAR(7)=NULL,
@ccode CHAR(6)=NULL,
@variable CHAR(2)=NULL,
@OrderBY CHAR(25)
)
AS
IF @OrderBY=13
--default order by is sort by document number
--this stored procedure is needed to join as a lookup table to bring back
--all of the required fields for the financial accounting report
--
--
BEGIN
SELECT DISTINCT a.M161TransactionCode, UPPER(b.[Description]) AS desc1,
a.M161DepartmentCode, a.[M161Ident],
a.M161CostCenter, a.M161Variable ,
a.M161FiscalYear, a.M161ObjectGroupCharged,
a.m161lastupdated,
a.M161OriginatingAgencyCode,a.M161BatchNumber,
a.M161DocumentNumber,a.M161ContractOriginatingAgency,
a.M161LineNumber,a.M161FullPartialIndicator,
a.M161POContractNumber,
convert(decimal(12,2), a.M161TransactionAmount) as amt,
a.M161TransactionAmountSign,a.[M161FundCode],
UPPER(a.[M161FundCode]) AS afund_code,
a.[M161FundCodeSegregation],
a.[M161ReportsToFund],
SUBSTRING(m161segregationorganizationcode,1,5) AS seg_code,
SUBSTRING(m161appropriationorganizationcode,1,5) AS app_code,
SUBSTRING(m161costcenterorganization,1,5) AS cc_code,
convert(varchar(10), cast(Left(a.m161lastupdated, 2) + '/' + substring(a.m161lastupdated, 3, 2) + '/' + right(a.m161lastupdated,2) as datetime), 121) as M161LastUpdated,
a.m161transactioncode + ' - ' + rtrim(b.[Description]) as tcode
FROM [OSC_m161] a
INNER JOIN
[OSCTransactionCodes] b
ON b.[TransactionCode]=a.[M161TransactionCode]
--get fund_code parameter
--
INNER JOIN
(SELECT m161ident, dcol
FROM (
SELECT m161ident,m161fundcode AS dcol FROM osc_m161
UNION ALL
SELECT m161ident,[M161FundCodeSegregation] FROM osc_m161
UNION ALL
SELECT m161ident,m161reportstofund FROM osc_m161) temp) d
ON d.m161ident = a.m161ident
--add where clause here
--transaction code
WHERE (a.M161TransactionCode=UPPER(@transcode) OR (@transcode IS NULL))
AND
--get agency_code parameter
--
((@agency_code IN (a.m161originatingagencycode,
a.m161contractoriginatingagency,
SUBSTRING(a.m161appropriationorganizationcode,1,5),
SUBSTRING(a.m161segregationorganizationcode,1,5),
SUBSTRING(a.m161costcenterorganization,1,5)))
OR (@agency_code IS NULL) )
--
AND
--
--start date
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) >= @startdate
--+ right(m161lastupdated,2) as datetime), 121) >= '2009-03-11'
AND
--end date
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) <= @enddate
--+ right(m161lastupdated,2) as datetime), 121) <= '2009-03-11'
--get fundcode parameter
--
AND ((UPPER(dcol) LIKE '%' + UPPER(rtrim(@fundcode))) OR (@fundcode IS NULL) OR (LEN(@fundcode)=0))
--
--get pocontractnum parameter
--
AND ((a.M161POContractNumber) LIKE + UPPER(rtrim(@pcode) +'%') OR (@pcode IS NULL) OR (LEN(@pcode)=0))
--get ccode parameter
AND ((a.M161CostCenter = UPPER(rtrim(@ccode))) OR (@ccode IS NULL) OR (LEN(@ccode)=0))
--test for variable parameter - two character
--
AND (((a.M161Variable) = UPPER(rtrim(@variable))) OR (@variable IS NULL) OR (LEN(@variable)=0))
--default order by document number
ORDER BY a.M161DocumentNumber;
END
--
ELSE
--
IF @OrderBY=5
--order by fifth column - which is cost center
--
BEGIN
--this stored procedure is needed to join as a lookup table to bring back
SELECT DISTINCT a.M161TransactionCode, UPPER(b.[Description]) AS desc1,
a.M161DepartmentCode, a.[M161Ident],
a.M161CostCenter, a.M161Variable ,
a.M161FiscalYear, a.M161ObjectGroupCharged,
a.m161lastupdated,
a.M161OriginatingAgencyCode,a.M161BatchNumber,
a.M161DocumentNumber,a.M161ContractOriginatingAgency,
a.M161LineNumber,a.M161FullPartialIndicator,
a.M161POContractNumber,
convert(decimal(12,2), a.M161TransactionAmount) as amt,
a.M161TransactionAmountSign,a.[M161FundCode],
UPPER(a.[M161FundCode]) AS afund_code,
a.[M161FundCodeSegregation],
a.[M161ReportsToFund],
SUBSTRING(m161segregationorganizationcode,1,5) AS seg_code,
SUBSTRING(m161appropriationorganizationcode,1,5) AS app_code,
SUBSTRING(m161costcenterorganization,1,5) AS cc_code,
convert(varchar(10), cast(Left(a.m161lastupdated, 2) + '/' + substring(a.m161lastupdated, 3, 2) + '/' + right(a.m161lastupdated,2) as datetime), 121) as M161LastUpdated,
a.m161transactioncode + ' - ' + rtrim(b.[Description]) as tcode
--,
FROM [OSC_m161] a
INNER JOIN
[OSCTransactionCodes] b
ON b.[TransactionCode]=a.[M161TransactionCode]
--get fund_code parameter
INNER JOIN
(SELECT m161ident, dcol
FROM (
SELECT m161ident,m161fundcode AS dcol FROM osc_m161
UNION ALL
SELECT m161ident,[M161FundCodeSegregation] FROM osc_m161
UNION ALL
SELECT m161ident,m161reportstofund FROM osc_m161) temp) d
ON d.m161ident = a.m161ident
--add where clause here
--transaction code
WHERE a.M161TransactionCode=UPPER(@transcode) OR (@transcode IS NULL)
AND
--get agency_code parameter
--
((@agency_code IN (a.m161originatingagencycode,
a.m161contractoriginatingagency,
SUBSTRING(a.m161appropriationorganizationcode,1,5),
SUBSTRING(a.m161segregationorganizationcode,1,5),
SUBSTRING(a.m161costcenterorganization,1,5)))
OR (@agency_code IS NULL) )
--
AND
--start date
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) >= @startdate
--+ right(m161lastupdated,2) as datetime), 121) >= '2009-03-11'
AND
--end date
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) <= @enddate
--+ right(m161lastupdated,2) as datetime), 121) <= '2009-03-11'
--AND ((UPPER(dcol) ='32323'))
AND ((UPPER(dcol) LIKE '%' + UPPER(rtrim(@fundcode))) OR (@fundcode IS NULL) OR (LEN(@fundcode)=0))
--
--get pocontractnum parameter
--
AND ((a.M161POContractNumber) LIKE + UPPER(rtrim(@pcode) +'%') OR (@pcode IS NULL) OR (LEN(@pcode)=0))
--
AND ((a.M161CostCenter = UPPER(rtrim(@ccode))) OR (@ccode IS NULL) OR (LEN(@ccode)=0))
--test for variable parameter - two character
--
AND (((a.M161Variable) = UPPER(rtrim(@variable))) OR (@variable IS NULL) OR (LEN(@variable)=0))
ORDER BY a.M161CostCenter,a.M161Variable;
END
--
ELSE IF @OrderBY=10
BEGIN
--
SELECT DISTINCT a.M161TransactionCode, UPPER(b.[Description]) AS desc1,
a.M161DepartmentCode, a.[M161Ident],
a.M161CostCenter, a.M161Variable ,
a.M161FiscalYear, a.M161ObjectGroupCharged,
a.m161lastupdated,
a.M161OriginatingAgencyCode,a.M161BatchNumber,
a.M161DocumentNumber,a.M161ContractOriginatingAgency,
a.M161LineNumber,a.M161FullPartialIndicator,
a.M161POContractNumber,
convert(decimal(12,2), a.M161TransactionAmount) as amt,
a.M161TransactionAmountSign,a.[M161FundCode],
UPPER(a.[M161FundCode]) AS afund_code,
a.[M161FundCodeSegregation],
a.[M161ReportsToFund],
SUBSTRING(m161segregationorganizationcode,1,5) AS seg_code,
SUBSTRING(m161appropriationorganizationcode,1,5) AS app_code,
SUBSTRING(m161costcenterorganization,1,5) AS cc_code,
convert(varchar(10), cast(Left(a.m161lastupdated, 2) + '/' + substring(a.m161lastupdated, 3, 2) + '/' + right(a.m161lastupdated,2) as datetime), 121) as M161LastUpdated,
a.m161transactioncode + ' - ' + rtrim(b.[Description]) as tcode
--,
FROM [OSC_m161] a
INNER JOIN
[OSCTransactionCodes] b
ON b.[TransactionCode]=a.[M161TransactionCode]
--get fund_code parameter
INNER JOIN
(SELECT m161ident, dcol
FROM (
SELECT m161ident,m161fundcode AS dcol FROM osc_m161
UNION ALL
SELECT m161ident,[M161FundCodeSegregation] FROM osc_m161
UNION ALL
SELECT m161ident,m161reportstofund FROM osc_m161) temp) d
ON d.m161ident = a.m161ident
--add where clause here
--transaction code
WHERE a.M161TransactionCode=UPPER(@transcode) OR (@transcode IS NULL)
AND
--get agency_code parameter
--
((@agency_code IN (a.m161originatingagencycode,
a.m161contractoriginatingagency,
SUBSTRING(a.m161appropriationorganizationcode,1,5),
SUBSTRING(a.m161segregationorganizationcode,1,5),
SUBSTRING(a.m161costcenterorganization,1,5)))
OR (@agency_code IS NULL) )
--
AND
--start date
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) >= @startdate
--+ right(m161lastupdated,2) as datetime), 121) >= '2009-03-11'
AND
--end date
convert(varchar(10), cast(Left(m161lastupdated, 2)
+ '/' + substring(m161lastupdated, 3, 2) + '/'
+ right(m161lastupdated,2) as datetime), 121) <= @enddate
--+ right(m161lastupdated,2) as datetime), 121) <= '2009-03-11'
--AND ((UPPER(dcol) ='32323'))
AND ((UPPER(dcol) LIKE '%' + UPPER(rtrim(@fundcode))) OR (@fundcode IS NULL) OR (LEN(@fundcode)=0))
--
--get pocontractnum parameter
--
AND ((a.M161POContractNumber) LIKE + UPPER(rtrim(@pcode) +'%') OR (@pcode IS NULL) OR (LEN(@pcode)=0))
--
AND ((a.M161CostCenter = UPPER(rtrim(@ccode))) OR (@ccode IS NULL) OR (LEN(@ccode)=0))
--test for variable parameter - two character
--
AND (((a.M161Variable) = UPPER(rtrim(@variable))) OR (@variable IS NULL) OR (LEN(@variable)=0))
ORDER BY a.m161lastupdated
--ORDER BY CASE (@OrderBy)
--WHEN 13 THEN a.M161DocumentNumber
------WHEN 10 THEN a.m161lastupdated
------WHEN 5 THEN a.M161CostCenter --,a.M161Variable'
--END
END
ASKER
acperkins-
thanks for the quick response-
Do you see any way I can get my ORDER BY working?
Maybe I should have posted to the Reporting Services topic?
Please respond.
Thanks
John
thanks for the quick response-
Do you see any way I can get my ORDER BY working?
Maybe I should have posted to the Reporting Services topic?
Please respond.
Thanks
John
Is m161lastupdated a datetime column? If it is varchar can we assume it is in mm/dd/yy format?
Is your database case sensitive? If not is there any reason you are doing this sort of stuff:
UPPER(dcol) LIKE '%' + UPPER(rtrim(@fundcode))
Is dcol varchar? If so then there may not be any need for the RTRIM
The same goes for M161POContractNumber, M161CostCenter and M161Variable.
Is your database case sensitive? If not is there any reason you are doing this sort of stuff:
UPPER(dcol) LIKE '%' + UPPER(rtrim(@fundcode))
Is dcol varchar? If so then there may not be any need for the RTRIM
The same goes for M161POContractNumber, M161CostCenter and M161Variable.
ASKER
acperkins-
I appreciate the response-
All of my data was dumped from a cobol-based model 204 system into sql server tables as character data-
The date field-is in the database table as: 010209
So I have to convert to match my input parameters of start and end date-
I dont think sql server 2005 is case sensitive but I wanted to make sure I would get matches.
Ill remove them for readability.
I will check the others as well.
I appreciate the response-
All of my data was dumped from a cobol-based model 204 system into sql server tables as character data-
The date field-is in the database table as: 010209
So I have to convert to match my input parameters of start and end date-
I dont think sql server 2005 is case sensitive but I wanted to make sure I would get matches.
Ill remove them for readability.
I will check the others as well.
See if this works for you:
USE [finOSCdata]
GO
/****** Object: StoredProcedure [dbo].[uspGetTransDetails] Script Date: 05/10/2009 18:04:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[uspGetTransDetails]
--required parameters
@startdate SMALLDATETIME,
@enddate SMALLDATETIME,
-----optional parameters
@transcode CHAR(3)=NULL,
@agency_code CHAR(5)=NULL,
@fundcode CHAR(10)=NULL,
@pcode CHAR(7)=NULL,
@ccode CHAR(6)=NULL,
@variable CHAR(2)=NULL,
@OrderBY CHAR(25)
AS
SELECT DISTINCT
a.M161TransactionCode,
UPPER(b.[Description]) AS desc1,
a.M161DepartmentCode, a.[M161Ident],
a.M161CostCenter,
a.M161Variable,
a.M161FiscalYear,
a.M161ObjectGroupCharged,
a.m161lastupdated,
a.M161OriginatingAgencyCode,
a.M161BatchNumber,
a.M161DocumentNumber,
a.M161ContractOriginatingAgency,
a.M161LineNumber,
a.M161FullPartialIndicator,
a.M161POContractNumber,
convert(decimal(12,2), a.M161TransactionAmount) as amt,
a.M161TransactionAmountSign,a.[M161FundCode],
UPPER(a.[M161FundCode]) AS afund_code,
a.[M161FundCodeSegregation],
a.[M161ReportsToFund],
LEFT(m161segregationorganizationcode, 5) AS seg_code,
Left(m161appropriationorganizationcode, 5) AS app_code,
Left(m161costcenterorganization, 5) AS cc_code,
CONVERT(varchar(10), CONVERT(datetime, Left(a.m161lastupdated, 2) + substring(a.m161lastupdated, 3, 2) + right(a.m161lastupdated, 2), 112), 121) as M161LastUpdated,
a.m161transactioncode + ' - ' + rtrim(b.[Description]) as tcode
FROM [OSC_m161] a
INNER JOIN [OSCTransactionCodes] b ON b.[TransactionCode]=a.[M161TransactionCode]
--get fund_code parameter
--
INNER JOIN (
SELECT m161ident, dcol
FROM (
SELECT m161ident,
m161fundcode AS dcol
FROM osc_m161
UNION ALL
SELECT m161ident,
[M161FundCodeSegregation]
FROM osc_m161
UNION ALL
SELECT m161ident,
m161reportstofund
FROM osc_m161) temp) d ON d.m161ident = a.m161ident
--add where clause here
--transaction code
WHERE (a.M161TransactionCode = @transcode OR @transcode IS NULL)
--get agency_code parameter
AND (@agency_code IN (a.m161originatingagencycode,
a.m161contractoriginatingagency,
LEFT(a.m161appropriationorganizationcode, 5),
LEFT(a.m161segregationorganizationcode, 5),
LEFT(a.m161costcenterorganization, 5))
OR @agency_code IS NULL)
--start date
AND CONVERT(datetime, Left(m161lastupdated, 2) + SUBSTRING(m161lastupdated, 3, 2) + RIGHT(m161lastupdated,2), 112) BETWEEN @startdate AND @enddate
--get fundcode parameter
AND (dcol LIKE '%' + @fundcode OR @fundcode IS NULL OR LEN(@fundcode) = 0)
--
--get pocontractnum parameter
AND (a.M161POContractNumber LIKE + @pcode + '%' OR @pcode IS NULL OR LEN(@pcode) = 0)
--get ccode parameter
AND (a.M161CostCenter = @ccode OR @ccode IS NULL OR LEN(@ccode) = 0)
--test for variable parameter - two character
AND (a.M161Variable = @variable OR @variable IS NULL OR LEN(@variable) = 0)
--default order by document number
ORDER BY
CASE @OrderBY
WHEN 13 THEN a.M161DocumentNumber
WHEN 5 THEN a.M161CostCenter + a.M161Variable
WHEN 10 THEN RIGHT(m161lastupdated,2) + LEFT(a.m161lastupdated, 4)
ELSE NULL
END
ASKER
ac - i will test this tomorrow am-
I can tell you that from my testing tonight, the distinct keyword would cause this error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
But I will test what you have suggested in about 7 hours-
Talk soon
I can tell you that from my testing tonight, the distinct keyword would cause this error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
But I will test what you have suggested in about 7 hours-
Talk soon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it will work but it is very complex.
Actually on second thoughts the idea about using the VIEW is not feasible, unless you also remove the WHERE clause. The only other option is to convert to a UDF and pass in the parameters. The only problem with that is that you may get a bit of a performance hit.
ASKER
Thanks for the tips-
I got it working by removing the Order By from the sql query and using the sorting on the Reporting Services side.
John
I got it working by removing the Order By from the sql query and using the sorting on the Reporting Services side.
John
Yes. Although if you asked "Should it be done?" I would have to say no.
>>Or should I keep the following code and get the ORDER BY working?<<
That would be my approach.