TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
-----------------
Reply
Answers (
6
)
stored procedure returns Null value
download Linked Reports and Batch Reports