Solved

Building Dynamic Query with ORDER BY and WHERE Clause

Posted on 2009-05-10
10
226 Views
Last Modified: 2012-05-06
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
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

Open in new window

0
Comment
Question by:jtrapat1
  • 5
  • 4
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24350259
>>Can this be done?<<
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.
0
 

Author Comment

by:jtrapat1
ID: 24350268
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24350342
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.
0
 

Author Comment

by:jtrapat1
ID: 24350367
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24351008
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

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:jtrapat1
ID: 24351143
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
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24353807
>>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.<<
Good point.  I very rarely use DISTINCT so I had forgotten about that.  You have a choice:
1. Add the missing column(s) to the SELECT list or
2. (Better still) Leave it as you had it with the IF statement for each ORDER BY
3. Put the whole mess in a VIEW (without the ORDER BY) and then have something like this:
IF @OrderBy = 13
    SELECT <Name all the columns here> From YourView ORDER BY a.M161DocumentNumber
ELSE IF @OrderBy = 5
    SELECT <Name all the columns here> From YourView ORDER BY a.M161CostCenter, a.M161Variable
ELSE IF @OrderBy = 10
    SELECT <Name all the columns here> From YourView ORDER BY M161LastUpdated  -- This is the alias not the column

0
 

Expert Comment

by:shidduch
ID: 24355635
it will work but it is very complex.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24356809
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.
0
 

Author Closing Comment

by:jtrapat1
ID: 31579983
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now