quote:

---

Originally posted by john20

Hi All,

I am creating SP using toad editor and it is giving me some syntex error as i am very new to toad.
Procedure syntex is:

CREATE PROCEDURE [dbo].[AdvSearch]
@UserID int,
@SearchStr nvarchar(50)
AS
BEGIN


SET NOCOUNT ON;


Select
d.*
From
[User] d inner join
Permission a on d.UserID = a.UserID

Where
d.Name like @SearchStr + '%'
AND
a.ID IN (Select ID From Children Where ChildID=@UserID )
AND
a.See > 0

END

can you please tell me where i need to make changes.

Thanks in advance

-john

---

quote:

---

Originally posted by john20

Hi Thanks for your reply.

did the changes it is giving me error on line 1.

Below are the errors:

ERROR line 1, col 18, ending_line 1, ending_col 18, Found '[', Invalid identifier: [

ERROR line 1, col 19, ending_line 1, ending_col 21, Found 'dbo', Expecting: . -or- TIMESTAMP -or- WRAPPED -or- ( -or- AUTHID -or- AS IS

-john

---

quote:

---

Originally posted by john20

Hi thanks for replying.

I have changed the code now it is giving below error:

PLS-00201: identifier 'NVARCHAR' must be declared

how to declare that?

below is the code:

CREATE OR REPLACE PROCEDURE UserPerm (UserID NUMBER,Searchstr nvarchar)

IS

BEGIN

Select
d.*
From
Tree d inner join
TreeACL a on d.DataID = a.DataID
Where
d.Name like SearchStr + '%'
AND
a.RightID IN (Select ID From Children Where ChildID=UserID )
AND
a.See > 0;


END UserPerm;

/

-john

---