[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

How would you modify a Stored Procedure statement using SQL Server 2005 based on a specific value of an INPUT PARAMETER? in a C# WIndows application

In the stored procedure that follows used in a C# Windows application, how would you modify the stored procedure based on the value of @RptYear?

The stored procedure's value should remain as is unless the value of @RptYear = 1111.

When @RptYear = 1111, then the ORDER BY value should be modified to read:

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;

----------------------------------

Default values for the stored procedure are as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[procDRange0To3Mod]
@RptYear int
AS

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,
         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
 
YZlatCommented:
use dynamic sql. try something like that:

ALTER PROCEDURE [dbo].[procDRange0To3Mod]
@RptYear int 
AS

DECLARE @sql AS NVARCHAR(1000)

DECLARE @orderbyclause AS NVARCHAR(500)

SET @sql = '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 '

IF  @RptYear = '1111' 
BEGIN
 SET @orderbyclause = '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' 
END
ELSE
BEGIN
SET @orderbyclause = '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'
END
SET @sql=@sql + @orderbyclause

EXECUTE(@sql)

Open in new window

0
 
informaniacCommented:
Just to add that it is better to use sp_executesql instead of EXECUTE to improve performance
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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