IF ELSE in SQL Server stored procedure

5 pts.
Tags:
IF ELSE statement
SQL Server 2005
SQL Server development
SQL Server stored procedures
SQL statements
I am using this code which is giving error:
 if (@report_type = 'CM') Begin Select distinct Region,sum(ClientCount) as ClientTotal Into #NewTemp From ( End else if (@report_type = 'AM' ) Begin ( Select distinct Region,Area,sum(ClientCount) as ClientTotal Into #NewTemp1 From End else if (@report_type = 'BM' or @report_type ='UM' or @report_type ='AUM') Begin ( Select distinct Region,Area,Branch_cd,sum(ClientCount) as ClientTotal Into #NewTemp2 From End else if @report_type = 'EXECUTIVE' ( Begin Select distinct Region,Area,Branch_cd,ExecutiveName,sum(ClientCount) as ClientTotal Into #NewTemp3 From End Select distinct Region, A.Branch_cd,Area, ExecutiveName = A.MkExe, Full_name, ClientCount = Count(A.PartyCode), IncentiveAccount = (Case When ( Count(PartyCode) >= 1 And Count(PartyCode) <= 10) Then ( 100 * Count(PartyCode)) When ( Count(PartyCode) >= 11 And Count(PartyCode) <= 20) Then ( (1000) + (( Count(PartyCode) - 10) * 200)) When ( Count(PartyCode) >= 21 And Count(PartyCode) <= 1000) Then ( (3000) + ((Count(PartyCode) - 20) * 300)) End), TradeInitCount = (Select Count(FirstTrade) from ApprovedPartyCode Where Branch_cd >= @FromBranch And Branch_cd <= @ToBranch AND Region >= @FromRegion And Region <= @ToRegion AND Area >= @FromArea And Area <= @ToArea And MkExe = A.Mkexe And month(Mdate) >= @FromMonth And month(Mdate) <= @ToMonth And year(Mdate) >= @FromYear And year(Mdate) <= @ToYear ), TradeInitIncentive = 50 * Count(FirstTrade), DepositClients = (Select count(PartyCode) From ApprovedPartyCode Where Branch_cd >= @FromBranch And Branch_cd <= @ToBranch AND Region >= @FromRegion And Region <= @ToRegion AND Area >= @FromArea And Area <= @ToArea And MkExe = A.MkExe And month(Mdate) >= @FromMonth And month(Mdate) <= @ToMonth And year(Mdate) >= @FromYear And year(Mdate) <= @ToYear And ( (MarginAmount + MarginScripts) >= 5000 )) , TotalCashCollected = (Select isnull(Sum(Case When isnull(FirstCheque,0) <= isnull(SecondCheque,0) Then isnull(FirstCheque,0) Else isnull(SecondCheque,0) End),0) From ApprovedPartyCode Where Region >= @FromRegion And Region <= @ToRegion And Area >= @FromArea And Area <= @ToArea And Branch_cd >= @FromBranch And Branch_cd <= @ToBranch And MkExe = A.MkExe And Month(MDate) >= @FromMonth And Month(Mdate) <= @ToMonth And Year(MDate) >= @FromYear And Year(Mdate) <= @ToYear And ((FirstCheque * -1) >= 5000 Or (SecondCheque * -1) >= 5000)) From #TempD A,UserDet U With(nolock) Where U.Region >= @FromRegion And U.Region <= @ToRegion And U.Area >= @FromArea And U.Area <= @ToArea And A.Branch_cd >= @FromBranch And A.Branch_cd <= @ToBranch And MkExe >= @fromExceutive And MkExe <= @ToExecutive And Month(Mdate) >= @FromMonth And Month(Mdate) <= @ToMonth And Year(Mdate) >= @FromYear And Year(Mdate) <= @ToYear And U.Usercode Like '%' + MkExe + '%' Group By Region,Area,A.Branch_Cd,A.MkExe,Full_Name --Order By A.Branch_cd,A.MkExe,Region if @report_type = 'CM' Begin )B Group By Region End else if @report_type = 'AM' Begin )B Group By Area,Region End else if (@report_type = 'BM' or @report_type ='UM' or @report_type ='AUM') Begin )B Group By Branch_cd,Area,Region End else if @report_type = 'EXECUTIVE' Begin )B Group By Branch_cd,Area,Region,ExecutiveName End Instead of this I can Write if (@report_type = 'CM') Begin Select distinct Region,sum(ClientCount) as ClientTotal Into #NewTemp From ( Select distinct Region, A.Branch_cd,Area, ExecutiveName = A.MkExe, Full_name, ClientCount = Count(A.PartyCode), IncentiveAccount = (Case When ( Count(PartyCode) >= 1 And Count(PartyCode) <= 10) Then ( 100 * Count(PartyCode)) When ( Count(PartyCode) >= 11 And Count(PartyCode) <= 20) Then ( (1000) + (( Count(PartyCode) - 10) * 200)) When ( Count(PartyCode) >= 21 And Count(PartyCode) <= 1000) Then ( (3000) + ((Count(PartyCode) - 20) * 300)) End), TradeInitCount = (Select Count(FirstTrade) from ApprovedPartyCode Where Branch_cd >= @FromBranch And Branch_cd <= @ToBranch AND Region >= @FromRegion And Region <= @ToRegion AND Area >= @FromArea And Area <= @ToArea And MkExe = A.Mkexe And month(Mdate) >= @FromMonth And month(Mdate) <= @ToMonth And year(Mdate) >= @FromYear And year(Mdate) <= @ToYear ), TradeInitIncentive = 50 * Count(FirstTrade), DepositClients = (Select count(PartyCode) From ApprovedPartyCode Where Branch_cd >= @FromBranch And Branch_cd <= @ToBranch AND Region >= @FromRegion And Region <= @ToRegion AND Area >= @FromArea And Area <= @ToArea And MkExe = A.MkExe And month(Mdate) >= @FromMonth And month(Mdate) <= @ToMonth And year(Mdate) >= @FromYear And year(Mdate) <= @ToYear And ( (MarginAmount + MarginScripts) >= 5000 )) , TotalCashCollected = (Select isnull(Sum(Case When isnull(FirstCheque,0) <= isnull(SecondCheque,0) Then isnull(FirstCheque,0) Else isnull(SecondCheque,0) End),0) From ApprovedPartyCode Where Region >= @FromRegion And Region <= @ToRegion And Area >= @FromArea And Area <= @ToArea And Branch_cd >= @FromBranch And Branch_cd <= @ToBranch And MkExe = A.MkExe And Month(MDate) >= @FromMonth And Month(Mdate) <= @ToMonth And Year(MDate) >= @FromYear And Year(Mdate) <= @ToYear And ((FirstCheque * -1) >= 5000 Or (SecondCheque * -1) >= 5000)) From #TempD A,UserDet U With(nolock) Where U.Region >= @FromRegion And U.Region <= @ToRegion And U.Area >= @FromArea And U.Area <= @ToArea And A.Branch_cd >= @FromBranch And A.Branch_cd <= @ToBranch And MkExe >= @fromExceutive And MkExe <= @ToExecutive And Month(Mdate) >= @FromMonth And Month(Mdate) <= @ToMonth And Year(Mdate) >= @FromYear And Year(Mdate) <= @ToYear And U.Usercode Like '%' + MkExe + '%' Group By Region,Area,A.Branch_Cd,A.MkExe,Full_Name )
I want to group by Region but the length of code will increase, so is it possible to write code using my option?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The following will definitely work

USE [MAINDB]
GO
/****** Object:  StoredProcedure [dbo].[Invoice_Sum]    Script Date: 03/06/2009 15:25:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Invoice_Sum]
@Num int AS 
SET NOCOUNT ON 

IF @Num=1 
Begin
	SELECT "Sales" AS ReportName, Director AS Field1, SupportTeam AS Field2, Count(OrderNumber) AS ActivityCount, Sum(Revenue) AS Revenue, Sum(COGS) AS COGS, Sum([Revenue]-[COGS]) AS Margin
	FROM Invoices
	WHERE OrderEntryDate>='1/1/2007'
	GROUP BY Director, SupportTeam
	ORDER BY Director, SupportTeam
End 

else IF @Num=2
Begin
	SELECT "Manufacturer" AS ReportName, MfgCode AS Field1, MfgName AS Field2, Count(OrderNumber) AS ActivityCount, Sum(Revenue) AS Revenue, Sum(COGS) AS COGS, Sum([Revenue]-[COGS]) AS Margin
	FROM Invoices
	WHERE OrderEntryDate>='1/1/2007'
	GROUP BY MfgCode, MfgName
	ORDER BY MfgName
End 

else If @Num=3
Begin 
	SELECT "Contract" AS ReportName, ContractNumber AS Field1, ContractName AS Field2, Count(OrderNumber) AS ActivityCount, Sum(Revenue) AS Revenue, Sum(COGS) AS COGS, Sum([Revenue]-[COGS]) AS Margin
	FROM Invoices
	WHERE OrderEntryDate>='1/1/2007'
	GROUP BY ContractNumber, ContractName
	ORDER BY ContractName
End 

No you can’t have logic like that. You should put each version into a separate stored procedure and call the correct stored procedure based on which conditions are met.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following