How to convert a STORED PROCEDURE to dynamic sql based on the value of the input parameter using SQL Server 2005 in an application for C#?

How would you convert the following Stored Procedure into Dynamic SQL so that
if the value of the INPUT PARAMETER     @RptYear = 1111
then the ORDER BY clause would change to the following value:
-----------------------------------------
order by DollarTitle Desc, MthTitle, (OfficeNumber + CustomerNumber),
 
         CASE WHEN (GROUPING(MthTitle) = 1)  THEN char(255)  
              ELSE td.DollarTitle
         END  
       , CASE WHEN (GROUPING(OfficeNumber) = 1) THEN char(254)  
              ELSE td.MthTitle
         END  
       , AcctValue desc;
-----------------------------------------

ORIGINAL STORE PROCEDURE:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[procDRange0To3Mod]
@RptYear int
AS
If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblRangeMod' AND TYPE = 'U')
DELETE FROM tblRangeMod
INSERT INTO dbo.tblRangeMod
SELECT *
FROM tblRange
WHERE MthF = 0
If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblCust03' AND TYPE = 'U')
DELETE FROM tblCust03
INSERT INTO dbo.tblCust03
SELECT tblMthRangesMod.MthFrom, tblMthRangesMod.MthTitle, C.OfficeNumber, C.CustomerNumber, C.FirstName, C.LastName, C.StreetAddr1, C.StreetAddr2,
C.City, C.ResStateCode, C. Zip, CASE WHEN LEN(C.SSN) = 0 THEN C.TaxID ELSE C.SSN END AS [SSN/Tax ID], C.DateLost, C.RedFlag, C.DateOfBirth, P.IraCode,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
CONVERT(char(10), GETDATE() - Day(GETDATE()) + 1, 121) AS DFrom,    
CONVERT(char(10), DATEADD(Month, -[MthTo] + 1, GETDATE() - Day(GETDATE())  + 1), 121)  AS DateFromC,
tblMthRangesMod.MthTo,
CONVERT(char(10),DATEADD(MONTH, -MthFrom, CONVERT(varchar(8), GETDATE(), 102)+ '01'), 121) As DateToC
--INTO tblCust  
FROM tblMthRangesMod, tblCustomersNew As C INNER JOIN tblProductsNew As P ON C.CustomerNumber=P.CustomerNumber AND C.OfficeNumber=P.OfficeNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))  AND ([MthFrom] = 0)
GROUP BY tblMthRangesMod.MthFrom, tblMthRangesMod.MthTitle, C.OfficeNumber, C.CustomerNumber, C.FirstName, C.LastName, C.StreetAddr1, C.StreetAddr2, C.City, C.ResStateCode, C.Zip,
C.SSN, C.TaxID, C.DateLost, C.RedFlag, C.DateOfBirth, P.IraCode,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())), tblMthRangesMod.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()))
If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblSummMod' AND TYPE = 'U')
DELETE FROM tblSummMod
INSERT INTO dbo.tblSummMod
SELECT T.MthTitle, tblDollarRanges.DollarTitle, T.OfficeNumber, T.CustomerNumber, T.FirstName, T.LastName, T.StreetAddr1, T.StreetAddr2,
T.City, T.ResStateCode, T.Zip, [SSN/Tax ID], T.DateLost, T.RedFlag, T.DateOfBirth, T.IraCode, T.AcctValue, T.MthFrom
--INTO tblSummMod
FROM tblCust03 As T, tblDollarRanges
WHERE (((T.AcctValue)>=[DollarFrom] And (T.AcctValue)<=[DollarTo]))
GROUP BY T.OfficeNumber, T.CustomerNumber, T.FirstName, T.LastName, T.StreetAddr1, T.StreetAddr2, T.City, T.ResStateCode, T.Zip, [SSN/Tax ID], T.DateLost, T.RedFlag, T.DateOfBirth, T.IraCode,
T.AcctValue, T.MthFrom, T.MthTitle, tblDollarRanges.DollarTitle, tblDollarRanges.DollarFrom
ORDER BY MthFrom, DollarTitle DESC

select CASE WHEN (GROUPING(MthTitle) = 1)  THEN 'TOT:'
            WHEN (GROUPING(OfficeNumber) = 1) THEN 'SUB:'
       ELSE MthTitle
END AS MthTitle
     , DollarTitle, OfficeNumber, CustomerNumber, FirstName, LastName, StreetAddr1, City,
ResStateCode, DateLost
, sum(AcctValue) AcctValue
from tblSummMod td
group by MthTitle, DollarTitle, OfficeNumber, CustomerNumber, FirstName, LastName, StreetAddr1, City,
ResStateCode, DateLost with rollup
having   (GROUPING(OfficeNumber) = 1 and GROUPING(DollarTitle) = 0)
       or GROUPING(DollarTitle)  
        + GROUPING(CustomerNumber)
        + GROUPING(FirstName)
        + GROUPING(LastName)  
        + GROUPING(StreetAddr1)  
        + GROUPING(City)
        + GROUPING(ResStateCode)
        + GROUPING(DateLost) = 0
        or GROUPING(MthTitle) = 1  
--order by DollarTitle Desc, MthTitle, (OfficeNumber + CustomerNumber),
order by DollarTitle Desc,
 
         CASE WHEN (GROUPING(MthTitle) = 1)  THEN char(255)  
              ELSE td.DollarTitle
         END  
       , CASE WHEN (GROUPING(OfficeNumber) = 1) THEN char(254)  
              ELSE td.MthTitle
         END  
       , AcctValue desc;
zimmer9Asked:
Who is Participating?
 
Christopher KileCommented:
I would simply build two SQL statements, each identical except for the ORDER BY clause, then if @ReportYear = 111 I'd execute the one with the new ORDER BY clause, otherwise I'd execute the one with the old ORDER BY clause.  No dynamic SQL is required.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.