August 15, 2014 at 12:25 pm
I keep getting the error below when I run the stored procedure at the bottom. I cannot figure out what is missing. I may have been looking at it too long. Any help will be appreciated.
Error: Msg 8155, Level 16, State 2, Line 1 No column name was specified for column 3 of 'A'.
Stored Procedure:
USE [DB1]
GO
/****** Object: StoredProcedure [dbo].[_Test_usp_Get_testa] Script Date: 08/15/2014 13:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC [_Test_usp_Get_testa] @Year = '2013', @EmployeeID = '1234567'
CREATE PROCEDURE [dbo].[_Test_usp_Get_testa]
(
@ActivityType nvarchar(500) = 'all',
@market nvarchar(500) = 'all',
@Office nvarchar(500) = 'all',
@LOS nvarchar(500) = 'all',
@StaffDescription nvarchar(500) = 'all',
@EmployeeID nvarchar(500) = 'all',
@Year nvarchar(500) = null,
@FiscalQuarter nvarchar(500)= 'all',
@FiscalMonth nvarchar(500) = 'all'
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(4000)
DECLARE @SELECT nvarchar(4000)
DECLARE @FROM nvarchar(4000)
DECLARE @WHERE nvarchar(4000)
DECLARE @GROUPBY nvarchar(4000)
--Add For Final Form
DECLARE @SELECTFINAL nvarchar(4000) = ''
DECLARE @GROUPBYFINAL nvarchar(4000) = ''
DECLARE @SUBSELECT0 nvarchar(4000)
DECLARE @SUBFROM0 nvarchar(4000)
DECLARE @SUBWHERE0 nvarchar(4000)
DECLARE @SUBSELECT1 nvarchar(4000)
DECLARE @SUBFROM1 nvarchar(4000)
DECLARE @SUBWHERE1 nvarchar(4000)
--DECLARE @SUBSELECT1 nvarchar(4000)
--DECLARE @SUBFROM1 nvarchar(4000)
--DECLARE @SUBWHERE1 nvarchar(4000)
--DECLARE @SUBSELECT nvarchar(4000)
--DECLARE @SUBFROM nvarchar(4000)
--DECLARE @SUBWHERE nvarchar(4000) = ''
--DECLARE @SUBWHERE2 nvarchar(4000)
----Add @SUBWHERE3
--DECLARE @SUBWHERE3 nvarchar(4000) = ''
SET @SELECT = 'Year(Startdate) '
SET @FROM = ' FROM [DB2].[dbo].[TableUsers] rf '
SET @WHERE = ' WHERE Year(Startdate) IN (' + dbo.fn_MVParam(@Year,',',0) + ')'
SET @GROUPBY = ' GROUP BY Year(Startdate)'
SET @SUBSELECT0 = 'SELECT (SELECT SUM(AmountGiven) '
SET @SUBFROM0 = ' FROM [DB1].[dbo].[TableInfo] '
SET @SUBWHERE0 = ' WHERE Year(date) = Year(Startdate) and EmployeeID = rf.EmployeeID'
SET @SUBWHERE0 = CASE WHEN @FiscalQuarter <> 'All' THEN @SUBWHERE0 + ' AND FiscalQuarter = rf.FiscalQuarter' else @SUBWHERE0 END
SET @SUBWHERE0 = CASE WHEN @FiscalMonth <> 'All' THEN @SUBWHERE0 + ' AND FiscalMonth = rf.FiscalMonth' else @SUBWHERE0 END
SET @SUBWHERE0 = CASE WHEN @ActivityType <> 'All' THEN @SUBWHERE0 + ' AND ActivityType IN (' + dbo.fn_MVParam(@ActivityType,',',0) + ')' else @SUBWHERE0 END
SET @SUBWHERE0 = CASE WHEN @market <> 'All' THEN @SUBWHERE0 + ' AND Market = rf.Market' else @SUBWHERE0 END
SET @SUBWHERE0 = CASE WHEN @Office <> 'All' THEN @SUBWHERE0 + ' AND Office = rf.OfficeDescription' else @SUBWHERE0 END
SET @SUBWHERE0 = CASE WHEN @LOS <> 'All' THEN @SUBWHERE0 + ' AND LOS = rf.LOS' else @SUBWHERE0 END
SET @SUBWHERE0 = CASE WHEN @StaffDescription <> 'All' THEN @SUBWHERE0 + ' AND StaffDescription = rf.StaffDescription' else @SUBWHERE0 END
SET @SUBSELECT0 = @SUBSELECT0 + @SUBFROM0 + @SUBWHERE0 + ') AS ''AmountGiven'''
SET @SUBSELECT1 = '(SELECT MAX(Date) '
SET @SUBFROM1 = ' FROM [DB1].[dbo].[TableInfo] '
--SET @SUBWHERE1 = ' WHERE Year(date) = Year(Startdate) and EmployeeID = rf.EmployeeID'
--SET @SUBWHERE1 = CASE WHEN @FiscalQuarter <> 'All' THEN @SUBWHERE1 + ' AND FiscalQuarter = rf.FiscalQuarter' else @SUBWHERE1 END
--SET @SUBWHERE1 = CASE WHEN @FiscalMonth <> 'All' THEN @SUBWHERE1 + ' AND FiscalMonth = rf.FiscalMonth' else @SUBWHERE1 END
--SET @SUBWHERE1 = CASE WHEN @ActivityType <> 'All' THEN @SUBWHERE1 + ' AND ActivityType IN (' + dbo.fn_MVParam(@ActivityType,',',0) + ')' else @SUBWHERE1 END
--SET @SUBWHERE1 = CASE WHEN @market <> 'All' THEN @SUBWHERE1 + ' AND Market = rf.Market' else @SUBWHERE1 END
--SET @SUBWHERE1 = CASE WHEN @Office <> 'All' THEN @SUBWHERE1 + ' AND Office = rf.OfficeDescription' else @SUBWHERE1 END
--SET @SUBWHERE1 = CASE WHEN @LOS <> 'All' THEN @SUBWHERE1 + ' AND LOS = rf.LOS' else @SUBWHERE1 END
--SET @SUBWHERE1 = CASE WHEN @StaffDescription <> 'All' THEN @SUBWHERE1 + ' AND StaffDescription = rf.StaffDescription' else @SUBWHERE1 END
SET @SUBSELECT1 = @SUBSELECT1 + @SUBFROM1 + ') AS ''EndDate'''
IF @FiscalQuarter IS NOT NULL
BEGIN
SET @GROUPBY = CASE WHEN @FiscalQuarter = 'All' THEN @GROUPBY
WHEN @FiscalQuarter <> 'All' THEN @GROUPBY + ', FiscalQuarter '
END
SET @SELECT = CASE WHEN @FiscalQuarter = 'All' THEN @SELECT
WHEN @FiscalQuarter <> 'All' THEN @SELECT + ',FiscalQuarter '
END
SET @WHERE = CASE WHEN @FiscalQuarter = 'All' THEN @WHERE
WHEN @FiscalQuarter <> 'All'THEN @WHERE + ' AND FiscalQuarter IN (' + dbo.fn_MVParam(@FiscalQuarter,',',0) + ')'
END
SET @GROUPBYFINAL = CASE WHEN @FiscalQuarter = 'All' THEN @GROUPBYFINAL
WHEN @FiscalQuarter <> 'All' THEN @GROUPBYFINAL + ', A.FiscalQuarter '
END
SET @SELECTFINAL = CASE WHEN @FiscalQuarter = 'All' THEN @SELECTFINAL
WHEN @FiscalQuarter <> 'All' THEN @SELECTFINAL + ', A.FiscalQuarter '
END
END
IF @FiscalMonth IS NOT NULL
BEGIN
SET @GROUPBY = CASE WHEN @FiscalMonth = 'All' THEN @GROUPBY
WHEN @FiscalMonth <> 'All' THEN @GROUPBY + ', FiscalMonth '
END
SET @SELECT = CASE WHEN @FiscalMonth = 'All' THEN @SELECT
WHEN @FiscalMonth <> 'All' THEN @SELECT + ',FiscalMonth '
END
SET @WHERE = CASE WHEN @FiscalMonth = 'All' THEN @WHERE
WHEN @FiscalMonth <> 'All'THEN @WHERE + ' AND FiscalMonth IN (' + dbo.fn_MVParam(@FiscalMonth,',',0) + ')'
END
SET @GROUPBYFINAL = CASE WHEN @FiscalMonth = 'All' THEN @GROUPBYFINAL
WHEN @FiscalMonth <> 'All' THEN @GROUPBYFINAL + ', A.FiscalMonth '
END
SET @SELECTFINAL = CASE WHEN @FiscalMonth = 'All' THEN @SELECTFINAL
WHEN @FiscalMonth <> 'All' THEN @SELECTFINAL + ', A.FiscalMonth '
END
END
IF @market IS NOT NULL
BEGIN
SET @GROUPBY = CASE WHEN @market = 'All' THEN @GROUPBY
WHEN @market <> 'All' THEN @GROUPBY + ', Market '
END
SET @SELECT = CASE WHEN @market = 'All' THEN @SELECT
WHEN @market <> 'All' THEN @SELECT + ',Market '
END
SET @WHERE = CASE WHEN @market = 'All' THEN @WHERE
WHEN @market <> 'All'THEN @WHERE + ' AND Market IN (' + dbo.fn_MVParam(@Market,',',0) + ')'
END
SET @GROUPBYFINAL = CASE WHEN @market = 'All' THEN @GROUPBYFINAL
WHEN @market <> 'All' THEN @GROUPBYFINAL + ', A.Market '
END
SET @SELECTFINAL = CASE WHEN @market = 'All' THEN @SELECTFINAL
WHEN @market <> 'All' THEN @SELECTFINAL + ', A.Market '
END
END
IF @Office IS NOT NULL
BEGIN
SET @GROUPBY = CASE WHEN @Office = 'All' THEN @GROUPBY
WHEN @Office <> 'All' THEN @GROUPBY + ', OfficeDescription '
END
SET @SELECT = CASE WHEN @Office = 'All' THEN @SELECT
WHEN @Office <> 'All' THEN @SELECT + ',OfficeDescription '
END
SET @WHERE = CASE WHEN @Office = 'All' THEN @WHERE
WHEN @Office <> 'All'THEN @WHERE + ' AND OfficeDescription IN (' + dbo.fn_MVParam(@Office,',',0) + ')'
END
SET @GROUPBYFINAL = CASE WHEN @Office = 'All' THEN @GROUPBYFINAL
WHEN @Office <> 'All' THEN @GROUPBYFINAL + ', A.OfficeDescription '
END
SET @SELECTFINAL = CASE WHEN @Office = 'All' THEN @SELECTFINAL
WHEN @Office <> 'All' THEN @SELECTFINAL + ', A.OfficeDescription '
END
END
IF @LOS IS NOT NULL
BEGIN
SET @GROUPBY = CASE WHEN @LOS = 'All' THEN @GROUPBY
WHEN @LOS <> 'All' THEN @GROUPBY + ', LOS '
END
SET @SELECT = CASE WHEN @LOS = 'All' THEN @SELECT
WHEN @LOS <> 'All' THEN @SELECT + ',LOS '
END
SET @WHERE = CASE WHEN @LOS = 'All' THEN @WHERE
WHEN @LOS <> 'All'THEN @WHERE + ' AND LOS IN (' + dbo.fn_MVParam(@LOS,',',0) + ')'
END
SET @GROUPBYFINAL = CASE WHEN @LOS = 'All' THEN @GROUPBYFINAL
WHEN @LOS <> 'All' THEN @GROUPBYFINAL + ', A.LOS '
END
SET @SELECTFINAL = CASE WHEN @LOS = 'All' THEN @SELECTFINAL
WHEN @LOS <> 'All' THEN @SELECTFINAL + ', A.LOS '
END
END
IF @StaffDescription IS NOT NULL
BEGIN
SET @GROUPBY = CASE WHEN @StaffDescription = 'All' THEN @GROUPBY
WHEN @StaffDescription <> 'All' THEN @GROUPBY + ', StaffDescription '
END
SET @SELECT = CASE WHEN @StaffDescription = 'All' THEN @SELECT
WHEN @StaffDescription <> 'All' THEN @SELECT + ',StaffDescription '
END
SET @WHERE = CASE WHEN @StaffDescription = 'All' THEN @WHERE
WHEN @StaffDescription <> 'All'THEN @WHERE + ' AND StaffDescription IN (' + dbo.fn_MVParam(@StaffDescription,',',0) + ')'
END
END
IF @EmployeeID IS NOT NULL
BEGIN
SET @GROUPBY = CASE WHEN @EmployeeID = 'All' THEN @GROUPBY
WHEN @EmployeeID <> 'All' THEN @GROUPBY + ', EmployeeID '
END
SET @SELECT = CASE WHEN @EmployeeID = 'All' THEN @SELECT
WHEN @EmployeeID <> 'All' THEN @SELECT + ', EmployeeID '
END
SET @WHERE = CASE WHEN @EmployeeID = 'All' THEN @WHERE
WHEN @EmployeeID <> 'All'THEN @WHERE + ' AND EmployeeID IN (' + dbo.fn_MVParam(@EmployeeID,',',0) + ')'
END
END
SET @SQLCMD = @SUBSELECT0 + ', ' + @SUBSELECT1 + ', ' + @SELECT + @FROM + @WHERE + @GROUPBY
SET @SQLCMD = 'SELECT SUM(A.AmountGiven) AS AmountGiven , A.year(startdate), A.EndDate' + @SELECTFINAL + ' FROM (' + @SQLCMD + ') A GROUP BY A.year(startdate), A.EndDate ' + @GROUPBYFINAL
SELECT @SQLCMD
--PRINT (@SQLCMD)
EXEC (@SQLCMD)
END
GO
August 15, 2014 at 12:38 pm
Might be no column name for:
A.EndDate' + @SELECTFINAL
in:
SET @SQLCMD = 'SELECT SUM(A.AmountGiven) AS AmountGiven , A.year(startdate), A.EndDate' + @SELECTFINAL + ' FROM (' + @SQLCMD + ') A GROUP BY A.year(startdate), A.EndDate ' + @GROUPBYFINAL
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 15, 2014 at 12:58 pm
The @SELECTFINAL adds in more valid fields to the select from the case statements. They are all straight column names so I dont believe that is it.
August 15, 2014 at 1:32 pm
Figured it out it was the first line in the select needed the AS SET @SELECT = 'Year(Startdate) as YearToDate'.
Thank You for your response Alvin it made me go back and look at them all.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply