asked on

Error compiling Store Procedure

Hi Experts,
I have the following store procedure

USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[procStatistics]    Script Date: 05/06/2015 00:19:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procStatistics]
(
	@intCategory	AS int,
	@strFilter		AS nvarchar(4000)
)
AS
BEGIN
	--DECLARE @strSQL AS nvarchar(MAX) = '';
	DECLARE @strSQL AS nvarchar(MAX);
	
if @strFilter = '' set @strfilter = 'ID > 0';
	
	IF 		@intCategory = 1	-- Source
		SET @strSQL = ' SELECT [Resume] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employeestbl
						WHERE ' + @strFilter +
					  ' GROUP BY [Resume]';
	ELSE IF @intCategory = 2	-- Hire Month
		SET @strSQL = ' SELECT [HireDate] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employeestbl
						WHERE ' + @strFilter +
					  ' GROUP BY [HireDate]';
	ELSE IF @intCategory = 3	--Employee status
		SET @strSQL = ' SELECT [Employeestatus] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employeestbl
						WHERE ' + @strFilter +
					  ' GROUP BY [Employeestatus]';
						
	EXECUTE sp_executesql @strSQL;				
END

Open in new window

that am trying to change for the following

USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[procStatistics]    Script Date: 05/06/2015 00:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procStatistics]
(
	@intCategory	AS int,
	@strFilter		AS nvarchar(4000)
)
AS
BEGIN
	--DECLARE @strSQL AS nvarchar(MAX) = '';
	DECLARE @strSQL AS nvarchar(MAX);
	
if @strFilter = '' set @strfilter = 'ID > 0';
	
	IF 		@intCategory = 1	-- Source
		SET @strSQL = ' SELECT  STUFF(CONVERT(varchar(30), DATEADD(MONTH, DATEDIFF(MONTH, 0, HireDate), 0), 7), 4, 5, '-') AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employeestbl
						WHERE ' + @strFilter +
					  ' Group by DATEADD(MONTH, DATEDIFF(MONTH, 0, datetime_column), 0)';
	ELSE IF @intCategory = 2	-- Hire Month
		SET @strSQL = ' SELECT [HireDate] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employeestbl
						WHERE ' + @strFilter +
					  ' GROUP BY [HireDate]';
	ELSE IF @intCategory = 3	--Employee status
		SET @strSQL = ' SELECT [Employeestatus] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employeestbl
						WHERE ' + @strFilter +
					  ' GROUP BY [Employeestatus]';
						
	EXECUTE sp_executesql @strSQL;				
END

Open in new window

and getting the error below.

Msg 8117, Level 16, State 1, Procedure procStatistics, Line 14
Operand data type varchar is invalid for subtract operator.

PS. the change is only in the first SQL.

ASKER CERTIFIED SOLUTION

Mark Bullock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

@Mark,

I was able to save the stored procedure with two apostrophes , however its now giving me an error when trying to exec it, for example.

exec [dbo].[procStatistics] 1,'State = 'NJ''
returns error
Incorrect syntax near 'NJ'.

and exec [dbo].[procStatistics] 1,'State = ''NJ'''
returns error
Invalid column name 'datetime_column'.

@Jim,
Thanks for the tips, really helpful for someone like me with no much experience with t-sql programming.
If you have additional tips please post them here..

Thanks,
Ben

bfuchs

ASKER

Never mind, I figured out already.

Thank you!