Stored Procedure Quotes Issue 1

[kermitforney]

Having a simple issue that I can't solve. When a parameter is passed via the stored procedure I have created, the value passed (varchar) has the correct quotes around when viewed via SQL Profiler. Somehow the quotes are removed when the parameter is used in the body of the procedure, so I recieve a syntax error.

Any ideas . . .

Ex.

exec stpGetJob 
@lngJobID=4,@lngTerrID=-1
,@strAssignedRecruiter=N'Christina Caveat'

 

[jbenson001]

what is the error you get?

 

[kermitforney]

Simple

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Caveat'.

 

[jbenson001]

can you post the SP?

 

[kermitforney]

ALTER PROCEDURE [dbo].[stpGetJobRequisitions]

@lngJobStatusID int,
@lngTerritoryID int,
@strAssignedRecruiter varchar(50)

AS

DECLARE @strSQL nvarchar(4000)
DECLARE @strSelect nvarchar(1000)
DECLARE @strFrom nvarchar(2000)
DECLARE @strWhere nvarchar(500)

SET @strSelect = 'SELECT lngJobReqID, strPosition, CONVERT(varchar, datRequested, 1) AS datRequested, strRegionName, strTerritoryName, strAssignedRecruiter, '
SET @strSelect = @strSelect + 'tblJobRequisitionStatus.strDescription AS strStatus, ' + char(39) + 'JobPostingDetail.aspx?lngJobReqID=' + char(39)
SET @strSelect = @strSelect + '+ CONVERT(varchar, lngJobReqID) AS strURL, CONVERT(varchar, datToBeFilled, 1) AS datToBeFilled, strGeographicalLoc, strComments '

SET @strFrom = 'FROM tblJobRequisitions INNER JOIN tblTerritories ON tblTerritories.lngTerritoryID = tblJobRequisitions.lngTerritoryID '
SET @strFrom = @strFrom + 'INNER JOIN tblRegions ON tblRegions.lngRegionID = tblTerritories.lngRegionID INNER JOIN '
SET @strFrom = @strFrom + 'tblJobRequisitionStatus ON tblJobRequisitionStatus.lngJobStatusID = tblJobRequisitions.lngStatusID '

SET @strWhere = ''

IF @lngJobStatusID > 0 OR @lngTerritoryID > -1
BEGIN
	IF @lngTerritoryID > 160
		SET @strWhere = 'WHERE tblTerritories.lngTerritoryID = ' + CONVERT(nvarchar, @lngTerritoryID) + ' '
	ELSE IF @lngTerritoryID = 92
		SET @strWhere = 'WHERE tblTerritories.lngRegionID IN (' + CONVERT(nvarchar, @lngTerritoryID) + ', 90) '
	ELSE IF @lngTerritoryID > 0
		SET @strWhere = 'WHERE tblTerritories.lngRegionID = ' + CONVERT(nvarchar, @lngTerritoryID) + ' '

	IF @lngJobStatusID > 0 AND @lngJobStatusID < 4
		IF LEN(@strWhere) = 0
			SET @strWhere = 'WHERE lngJobStatusID = ' + CONVERT(nvarchar, @lngJobStatusID)
		ELSE
			SET @strWhere = @strWhere + 'AND lngJobStatusID = ' + CONVERT(nvarchar, @lngJobStatusID)
	ELSE IF @lngJobStatusID = 4
		IF LEN(@strWhere) = 0
			SET @strWhere = 'WHERE lngJobStatusID < 3'
		ELSE
			SET @strWhere = @strWhere + 'AND lngJobStatusID < 3 '
END

IF (@strAssignedRecruiter <> 'ALL') AND @lngJobStatusID > 0 OR @lngTerritoryID > -1
	SET @strWhere = @strWhere + ' AND tblJobRequisitions.strAssignedRecruiter = ' + @strAssignedRecruiter
ELSE IF @lngJobStatusID > 0 AND @lngJobStatusID < 4
	SET @strWhere = @strWhere + ' AND tblJobRequisitions.strAssignedRecruiter = ' + @strAssignedRecruiter
ELSE IF @strAssignedRecruiter <> 'ALL'	
	SET @strWhere = @strWhere + ' WHERE tblJobRequisitions.strAssignedRecruiter = ' + @strAssignedRecruiter
		

SET @strSQL = @strSelect + @strFrom + @strWhere + ' ORDER BY lngJobReqID DESC'
--PRINT @strsql
EXEC sp_executeSQL @strSQL

 

[markros]

Why do you need dynamic SQL here at all? To me this problem doesn't require a dynamic SQL - there is nothing really dynamic here.

 

[kermitforney]

Previous programmer, i'm only a tech consult. I have suggested getting rid of it, but it's not my decision. )

 

[jbenson001]

Since this is dynamic sql, you will have to use print statments throughout the SP to see where it is going wrong.

 

[kermitforney]

I used the print statements and that is where I found that the @strAssignedRecruiter is without quotes in the final @strSql variable.

Just can't figure out why.

 

[markros]

Of course it would be without quotes, see here

SET @strWhere = @strWhere + ' AND tblJobRequisitions.strAssignedRecruiter = ' + @strAssignedRecruiter
ELSE IF @lngJobStatusID > 0 AND @lngJobStatusID < 4
    SET @strWhere = @strWhere + ' AND tblJobRequisitions.strAssignedRecruiter = ' + @strAssignedRecruiter
ELSE IF @strAssignedRecruiter <> 'ALL'    
    SET @strWhere = @strWhere + ' WHERE tblJobRequisitions.strAssignedRecruiter = ' + @strAssignedRecruiter

You're not putting quotes (look at Quotename() function in BOL) - so, where would they come from?

 

[kermitforney]

THanks, Mark!

Worked like a charm. Why does SQL Server remove the quotes if the variable is passed with quotes?

In SQL Profiler the variable is passed with quotes.

 

[jbenson001]

Sql Server does not remove quotes. Quotes are just to tell sql server that you are passing in a string value.

 

[markros]

SQL Server didn't remove them. But since you were constructing dynamic SQL, you need to place them around character fields and dates.

 

[kermitforney]

Ohhhh, got it Marc. I had never seen this behaviour before, but I can understand your explanation.

Thanks, again.

 

[atilley]

At the end of the proc where you're building the WHERE clause, you need to put quotes back onto @strAssignedRecruiter in order to use it in your built SQL statement.

This should do it:

SET @strWhere = @strWhere + ' AND tblJobRequisitions.strAssignedRecruiter = ''' + @strAssignedRecruiter + ''''