Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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;
0
zimmer9
Asked:
zimmer9
1 Solution
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now