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.
LVL 5
bfuchsAsked:
Who is Participating?
 
Mark BullockQA Engineer IIICommented:
I think the nested apostrophes cause your problem.
'-'

Open in new window


Try escaping the apostrophes by using two apostrophes like this:
''-''

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Couple of handy one-liners:

1.

A good first step for troubleshooting is to add this -->  SELECT @strSQL <-- before your execute, then execute.  When the query T-SQL appears in the results tab, copy-paste it into another window, and execute.  It'll still throw an error, but now you have T-SQL to work with.  

2.

Msg 8117, Level 16, State 1, Procedure procStatistics, Line 14  <--  Double-click on this line and the cursor will jump to wherever Line 14 is that threw the error.  Tell us what line this is, as line 14 in your query window will not be the same as line 14 in this question's code block.
0
 
bfuchsAuthor Commented:
@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
0
 
bfuchsAuthor Commented:
Never mind, I figured out already.

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.