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 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.