zimmer9
asked on
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(Cas hBalance,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(M ONTH, -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.Custome rNumber AND C.OfficeNumber=P.OfficeNum ber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DA TEADD(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,DAT EADD(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.DollarTitl e, 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)>=[DollarFr om] 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.DollarTitl e, 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;
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)+
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(M
--INTO tblCust
FROM tblMthRangesMod, tblCustomersNew As C INNER JOIN tblProductsNew As P ON C.CustomerNumber=P.Custome
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DA
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,DAT
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblSummMod' AND TYPE = 'U')
DELETE FROM tblSummMod
INSERT INTO dbo.tblSummMod
SELECT T.MthTitle, tblDollarRanges.DollarTitl
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)>=[DollarFr
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.DollarTitl
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.