Danish Habib

Danish Habib

  • NA
  • 694
  • 244k

Stored procedure Syntax error

Sep 9 2015 1:21 AM
When i execute this store procedure it gives error, I am using two select statement with union in that having same parameters 
 
---------
USE [planrbmeDb2]
GO
/****** Object: StoredProcedure [dbo].[GetDetailss] Script Date: 9/9/2015 10:00:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
--EXEC GetDetailss 1,5
ALTER PROCEDURE [dbo].[GetDetailss]
@QuarterNAme varchar(200),
@YearNAme varchar(200)
AS
Declare @QuarterID Tinyint
DECLARE @Query nvarchar(2000)
DECLARE @IsWhere bit
--SET @QuarterID=
--(
--Select Quarter_ID From Quarter QU Where Qu.Quarter_ID=@QuarterNAme
--)
BEGIN
SET @IsWhere = 0
SET @Query ='SELECT SUM(ISNULL(AN.QTotal,0)+ISNULL(AN.QT_ForMen,0)+ISNULL(AN.QT_ForWomen,0)+ISNULL(AN.QT_ForBoy,0)+ISNULL(AN.QT_ForGirl,0)) AS Achived,Y.Year_Name,AN.Year,D.DistrictName As district From AnswersNew AS AN
Inner Join Districts AS D ON D.DistrictId=AN.DistrictId
INNER JOIN Quarter AS QTt ON QTt.Quarter_ID=AN.Quarter
INNER JOIN Year as Y ON Y.Year_ID=AN.Year
Where 1=1
union all
SELECT SUM(ISNULL(QT.QuestionTarget,0)+ISNULL(QT.QT_FormMen,0)+ISNULL(QT.QT_ForWomen,0)+ISNULL(QT.QT_ForGirl,0)+ISNULL(QT.QT_ForBoy,0)) AS TARGETTOTAL
from QuestionTarget AS QT
Inner Join Districts AS D ON D.DistrictId=QT.DistrictId
INNER JOIN Quarter AS QTt ON QTt.Quarter_ID=QT.Quarter
INNER JOIN Year as Y ON Y.Year_ID=QT.Year
Where 1=1'
SET @IsWhere=1
IF @YearNAme IS NOT NULL
SET @Query=@Query + ' AND (AN.Year = ' + CONVERT(VARCHAR,@YearName) + ' ) '
IF @QuarterNAme IS NOT NULL
SET @Query=@Query + ' AND (AN.Quarter = ' + CONVERT(VARCHAR,@QuarterName) + ' )'
SET @Query = @Query + ' Group BY Y.Year_Name,AN.Year,D.DistrictName,'
EXECUTE (@Query)
print(@Query)
END
----------------- 

Answers (6)