Searh syntax setting - 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)

Searh syntax setting

Author

Topic

leon1958
Starting Member

4 Posts

Posted - 2013-07-01 : 07:18:30

Hi

Help please!!

I am getting the following error when trying to create this procedure.

Msg 207, Level 16, State 1, Procedure getparent, Line 12
Invalid column name '.%'.

I am pasting the procedure below

CREATE PROCEDURE getparent @newclass varchar(50) as
declare @parentid int
declare @parentclass varchar(50)

/*
Gets the parent subject for the subject with the class
passed as parameter
*/

SELECT @parentclass=MAX(class)
FROM subject
WHERE @newclass LIKE RTRIM(ltrim(class))+".%"

select subjectid
from subject
where class = @parentclass

GO

Thank you

SunPower

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 07:19:57

[code]
CREATE PROCEDURE getparent @newclass varchar(50) as
declare @parentid int
declare @parentclass varchar(50)

/*
Gets the parent subject for the subject with the class
passed as parameter
*/

SELECT @parentclass=MAX(class)
FROM subject
WHERE @newclass LIKE RTRIM(ltrim(class))+ ''.%''

select subjectid
from subject
where class = @parentclass

GO
[/code]
it should be two ' rather than a "

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2013-07-01 : 07:40:03

CREATE PROCEDURE getparent @newclass varchar(50) as
declare @parentid int
declare @parentclass varchar(50)

/*
Gets the parent subject for the subject with the class
passed as parameter
*/

SELECT @parentclass=MAX(class)
FROM subject
WHERE @newclass LIKE RTRIM(ltrim(class))+ '.%'

select subjectid
from subject
where class = @parentclass

GO

--------------------------
http://connectsql.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2013-07-01 : 07:43:34

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE getparent @newclass varchar(50) as
declare @parentid int
declare @parentclass varchar(50)

/*
Gets the parent subject for the subject with the class
passed as parameter
*/

SELECT @parentclass=MAX(class)
FROM subject
WHERE @newclass LIKE RTRIM(ltrim(class))+".%"
select subjectid
from subject
where class = @parentclass

GO

--------------------------
http://connectsql.blogspot.com/

leon1958
Starting Member

4 Posts

Posted - 2013-07-03 : 12:46:46

Thank you very much Lion of the Desert.

The command you gave me

SET QUOTED_IDENTIFIER OFF

WORKS.

Thank you again.

quote:
Originally posted by lionofdezert

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE getparent @newclass varchar(50) as
declare @parentid int
declare @parentclass varchar(50)

/*
Gets the parent subject for the subject with the class
passed as parameter
*/

SELECT @parentclass=MAX(class)
FROM subject
WHERE @newclass LIKE RTRIM(ltrim(class))+".%"
select subjectid
from subject
where class = @parentclass

GO

--------------------------
http://connectsql.blogspot.com/

SunPower

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources