Error With Single Quotes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error With Single Quotes

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 04:40:42

Can Anyone tell me where exactly to keep single Quotes in the below Query which is stored in @Stmt..

DECLARE @STMT VARchar(100)
DECLARE @STR='A'
SET @STMT='select first_nm as ''FIrst NAME' 'FROM TEmployee WHERE first_NM like''%'@str'%'''
exec (@stmt)

Coz when i execute the above statments it is showing an error like this

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'FROM TEmployee WHERE first_NM like'%'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 05:02:10

[code]DECLARE @STMT VARCHAR(100),
@STR VARCHAR(10)

SET @str = 'A'
SET @STMT = 'SELECT first_nm AS [First NAME] FROM tEmployee WHERE first_NM LIKE ' + quotename('%' + @str + '%', '''')

print @stmt
exec (@stmt)
[/code]

E 12°55'05.63"
N 56°04'39.26"

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 05:10:50

Thanks For the Quick Reply.. Can u tell me what exactly is the use of quotename() function in this context..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 05:12:39

see this

http://doc.ddart.net/mssql/sql70/qua-quz.htm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 05:14:00

Also refer this top understand how sql server handles single quotes
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 05:23:58

Thanks Peso and Vishakh for the quick replies..
But i cant understand how do i apply it to my stored Procedure
. I'm getting an error like this -
Msg 102, Level 15, State 1, Procedure USP_Report_Skill_Matrix, Line 37
Incorrect syntax near ','.

ALTER PROCEDURE [EIS_User].[USP_Report_Skill_Matrix]
@Type VARCHAR(50),
@ID VARCHAR(50),
@User_NBR INT,
@Page_NBR INT
AS
/**************************************************************************************************************************************************************
* Procedure Name : [EIS_User].[USP_Report_Skill_Matrix]
*
* Date : 10/23/2008
*
* Author : Swathi. G
*
* Parameters : @Type - Type Number Based on the Selection in Skill Matrix Report
* @User_NBR - User Who logged in
* @Page_NBR - Page Number
*
*
* Purpose : To Get Employee Supervisor Ratings based on Selectiom
*
* Change History:
* Date Programmer Reason
* -------------------- ------------------- -------------------------
* 2008-10-23 Swathi.G Initial Version
***************************************************************************************************************************************************************/

BEGIN
SET NOCOUNT ON;

-- Select Data
DECLARE @Stmt VARCHAR(1000)
SET @Stmt='
SELECT e.Employee_NBR
,REPLACE(REPLACE(REPLACE(ISNULL(ec.Configuration_Val,e.First_NM + ',' + e.Last_NM),'First_Name',e.First_NM),'Last_Name',e.Last_NM),'Middle_Name',e.Middle_NM) AS Employee_NM
,s.Skill_NM
,sl.Skill_Level_NM
FROM TEmployee e
LEFT JOIN TEmployee_Department ed
ON e.Employee_NBR=ed.Employee_NBR
LEFT JOIN TEmployee_Title et
ON e.Employee_NBR=et.Employee_NBR
LEFT JOIN TEmployee_Skills es
ON e.Employee_NBR=es.Employee_NBR
LEFT JOIN TDepartment d
ON ed.Department_NBR=d.Department_NBR
LEFT JOIN TTitle t
ON et.Title_NBR=t.Title_NBR
LEFT JOIN TSkill s
ON es.Skill_NBR=s.Skill_NBR
LEFT JOIN TSkill_Level sl
ON es.Supervisor_Rating=sl.Skill_Level_NBR
LEFT JOIN (SELECT cv.Configuration_Val
FROM TConfiguration c
INNER JOIN TConfiguration_Value cv
ON c.Configuration_NBR = cv.Configuration_NBR
WHERE (c.Configuration_NM = 'Employee Name')
)ec
ON 1=1

WHERE 1= CASE WHEN ','+@Type+',' LIKE '%,'+CAST(e.Employee_NBR as varchar(10)) +',%' AND @ID='Employee' THEN 1
WHEN ed.Department_NBR=@Type AND @ID='Department' THEN 1
WHEN et.Title_NBR=@Type AND @ID='Title' THEN 1
WHEN es.Skill_NBR=@Type AND @ID='Skill' THEN 1
ELSE 0
END
AND (e.Active_FG=0)
AND (ed.Active_FG=0)
AND (et.Active_FG=0)
AND (es.Active_FG=0)'

EXEC (@Stmt)

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 05:37:57

No need for dynamic SQL at all...

SET NOCOUNT ON

SELECT		e.Employee_NBR,
		REPLACE(REPLACE(REPLACE(ISNULL(ec.Configuration_Val, e.First_NM + ',' + e.Last_NM), 'First_Name', e.First_NM), 'Last_Name', e.Last_NM), 'Middle_Name', e.Middle_NM) AS Employee_NM,
		s.Skill_NM,
		sl.Skill_Level_NM
FROM		tEmployee AS e
LEFT JOIN	tEmployee_Department AS ed ON ed.Employee_NBR = e.Employee_NBR
			AND ed.Active_FG = 0
LEFT JOIN	tEmployee_Title AS et ON et.Employee_NBR = e.Employee_NBR
			AND et.Active_FG = 0
LEFT JOIN	tEmployee_Skills AS es ON es.Employee_NBR = e.Employee_NBR
			AND es.Active_FG = 0
LEFT JOIN	tDepartment AS d ON d.Department_NBR = ed.Department_NBR
LEFT JOIN	tTitle AS t ON t.Title_NBR = et.Title_NBR
LEFT JOIN	tSkill AS s ON s.Skill_NBR = es.Skill_NBR
LEFT JOIN	tSkill_Level AS sl ON sl.Skill_Level_NBR = es.Supervisor_Rating
CROSS JOIN	(
			SELECT		cv.Configuration_Val
			FROM		tConfiguration AS c
			INNER JOIN	tConfiguration_Value AS cv ON cv.Configuration_NBR = c.Configuration_NBR
			WHERE		c.Configuration_NM = 'Employee Name'
		) AS ec
WHERE		CASE
			WHEN ',' + @Type + ',' LIKE '%,' + CAST(e.Employee_NBR AS VARCHAR(10)) + ',%' AND @ID = 'Employee' THEN 1
			WHEN ed.Department_NBR = @Type AND @ID = 'Department' THEN 1
			WHEN et.Title_NBR = @Type AND @ID = 'Title' THEN 1
			WHEN es.Skill_NBR = @Type AND @ID = 'Skill' THEN 1
			ELSE 0
		END = 1
		AND e.Active_FG = 0

E 12°55'05.63"
N 56°04'39.26"

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 05:57:57

Hi peso the procedure got executed successfully. but i would be getting more than one employee_nbr as input.
When i execute the procedure like this

exec usp_ex '1,97,881,882','Employee',81,5

It is showing the following error

can u please help me out in this..
Thanks for the same

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 05:58:40

sorry i dint post the error.. this is the error

Msg 245, Level 16, State 1, Procedure usp_ex, Line 10
Conversion failed when converting the varchar value '1,97,881,882' to data type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 06:10:10

Show your code. it seems like you've declared the parameter as int. or havent casted the field to varchar in WHERE

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-27 : 06:10:32

If you have implemented Peso's suggestion into your stored procedure then it will be different from the one you posted earlier.

If you post the new text of the SP it will be a lot easier for us to help you. (especially as line 10 must be very different from the line 10 in your previous example).

Regards,

-------------
Charlie

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 06:15:50

Sure..
I'm executing SP like this

exec usp_ex '1,97,881,882','Employee',81,5

this is the SP

alter procedure [usp_ex]
@Type VARCHAR(50),
@ID VARCHAR(50),
@User_NBR INT,
@Page_NBR INT
as
begin
SET NOCOUNT ON

SELECT e.Employee_NBR,
REPLACE(REPLACE(REPLACE(ISNULL(ec.Configuration_Val, e.First_NM + ',' + e.Last_NM), 'First_Name', e.First_NM), 'Last_Name', e.Last_NM), 'Middle_Name', e.Middle_NM) AS Employee_NM,
s.Skill_NM,
sl.Skill_Level_NM
FROM TEmployee AS e
LEFT JOIN tEmployee_Department AS ed ON ed.Employee_NBR = e.Employee_NBR
AND ed.Active_FG = 0
LEFT JOIN tEmployee_Title AS et ON et.Employee_NBR = e.Employee_NBR
AND et.Active_FG = 0
LEFT JOIN tEmployee_Skills AS es ON es.Employee_NBR = e.Employee_NBR
AND es.Active_FG = 0
LEFT JOIN tDepartment AS d ON d.Department_NBR = ed.Department_NBR
LEFT JOIN tTitle AS t ON t.Title_NBR = et.Title_NBR
LEFT JOIN tSkill AS s ON s.Skill_NBR = es.Skill_NBR
LEFT JOIN tSkill_Level AS sl ON sl.Skill_Level_NBR = es.Supervisor_Rating
CROSS JOIN (
SELECT cv.Configuration_Val
FROM tConfiguration AS c
INNER JOIN tConfiguration_Value AS cv ON cv.Configuration_NBR = c.Configuration_NBR
WHERE c.Configuration_NM = 'Employee Name'
) AS ec
WHERE CASE
WHEN ','+@Type+',' LIKE '%,'+CAST(e.Employee_NBR as varchar(10)) +',%' AND @ID='Employee' THEN 1
WHEN ed.Department_NBR = @Type AND @ID = 'Department' THEN 1
WHEN et.Title_NBR = @Type AND @ID = 'Title' THEN 1
WHEN es.Skill_NBR = @Type AND @ID = 'Skill' THEN 1
ELSE 0
END = 1
AND e.Active_FG = 0
end

The error SHown is

Msg 245, Level 16, State 1, Procedure usp_ex, Line 12
Conversion failed when converting the varchar value '1,97,881,882' to data type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 06:24:29

may be this

alter procedure [usp_ex]
@Type VARCHAR(50),
@ID VARCHAR(50),
@User_NBR INT,
@Page_NBR INT
as
begin
SET NOCOUNT ON

SELECT e.Employee_NBR,
REPLACE(REPLACE(REPLACE(ISNULL(ec.Configuration_Val, e.First_NM + ',' + e.Last_NM), 'First_Name', e.First_NM), 'Last_Name', e.Last_NM), 'Middle_Name', e.Middle_NM) AS Employee_NM,
s.Skill_NM,
sl.Skill_Level_NM
FROM TEmployee AS e
LEFT JOIN tEmployee_Department AS ed ON ed.Employee_NBR = e.Employee_NBR
AND ed.Active_FG = 0
LEFT JOIN tEmployee_Title AS et ON et.Employee_NBR = e.Employee_NBR
AND et.Active_FG = 0
LEFT JOIN tEmployee_Skills AS es ON es.Employee_NBR = e.Employee_NBR
AND es.Active_FG = 0
LEFT JOIN tDepartment AS d ON d.Department_NBR = ed.Department_NBR
LEFT JOIN tTitle AS t ON t.Title_NBR = et.Title_NBR
LEFT JOIN tSkill AS s ON s.Skill_NBR = es.Skill_NBR
LEFT JOIN tSkill_Level AS sl ON sl.Skill_Level_NBR = es.Supervisor_Rating
CROSS JOIN (
SELECT cv.Configuration_Val
FROM tConfiguration AS c
INNER JOIN tConfiguration_Value AS cv ON cv.Configuration_NBR = c.Configuration_NBR
WHERE c.Configuration_NM = 'Employee Name'
) AS ec
WHERE CASE
WHEN ','+@Type+',' LIKE '%,'+CAST(e.Employee_NBR as varchar(10)) +',%' AND @ID='Employee' THEN 1
WHEN ','+@Type+',' LIKE '%,'+CAST(ed.Department_NBR as varchar(10)) +',%' AND @ID = 'Department' THEN 1
WHEN ','+@Type+',' LIKE '%,'+CAST(et.Title_NBR as varchar(10)) +',%' AND @ID = 'Title' THEN 1
WHEN ','+@Type+',' LIKE '%,'+CAST(es.Skill_NBR  as varchar(10)) +',%' AND @ID = 'Skill' THEN 1ELSE 0
END = 1
AND e.Active_FG = 0
end

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 06:35:56

Thanks a Lot Vishakh.. Atlast Its Working For me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 06:47:33

quote:
Originally posted by swathigardas

Thanks a Lot Vishakh.. Atlast Its Working For me

welcome

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 06:50:55

well, i just have one concern in the above SP..
will there be any difference if i give the condtion where ed.Active_FG=0 while joining with Temployee_Department
and if it is given at the end.
Coz i have given the condition at the end when it doesnt show any record

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 06:59:44

If you put WHERE at the end for OUTER JOINed tables, you will negate that effect and the JOINs will behave as INNER JOINs.

E 12°55'05.63"
N 56°04'39.26"

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-27 : 07:07:30

Wow.. I dint know this till date... Thanks Peso...
I'm learning lots as a member of this site

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 07:15:47