SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Searh syntax setting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leon1958
Starting Member

India
4 Posts

Posted - 07/01/2013 :  07:18:30  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/01/2013 :  07:19:57  Show Profile  Reply with Quote

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

it should be two ' rather than a "

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 07/01/2013 :  07:40:03  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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/

Edited by - lionofdezert on 07/01/2013 07:41:01
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 07/01/2013 :  07:43:34  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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/

Edited by - lionofdezert on 07/01/2013 07:44:27
Go to Top of Page

leon1958
Starting Member

India
4 Posts

Posted - 07/03/2013 :  12:46:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000