Stored Procedure - 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 2000 Forums

SQL Server Development (2000)

Stored Procedure

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-09-10 : 08:55:15

Hello,

I have a strod procedure like the following

CREATE PROCEDURE sp_ParentListSort
@Mode NVarchar(10)=Null,
@odBy NVarchar(500)=Null,
@SchoolID nvarchar(6)=Null,
@RClass int=Null

AS
Begin
Declare @sql as nvarchar(2000)
Select @sql='SELECT PtCode as ParentCode,SFatherName as FathersName,PreResPhone as ResidenceNumber,PreFOffPhone as OfficeNumber,PreFMobile as Mobile,DesignationName as Designation
FROM dbo.Parent INNER JOIN
dbo.Nation ON dbo.Parent.PreCountryID = dbo.Nation.NCode INNER JOIN
dbo.Designation ON dbo.Parent.PreFDesignationID = dbo.Designation.DesignationID Order By ' + @odBy
exec (@sql)
End

In this stored procedure I have to check SchoolID also, like..

Create Procedure....
.
.
dbo.Designation ON dbo.Parent.PreFDesignationID = dbo.Designation.DesignationID where SchoolID=@SchoolID Order By ' + @odBy

exec (@sql)
End

How could I do that?

Thanks
Ceema

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-10 : 09:18:31

[code]
CREATE PROCEDURE sp_ParentListSort
@Mode NVarchar(10)=Null,
@odBy NVarchar(500)=Null,
@SchoolID nvarchar(6)=Null,
@RClass int=Null

AS
Begin
Declare @sql as nvarchar(2000)
Select @sql='SELECT PtCode as ParentCode,SFatherName as FathersName,PreResPhone as ResidenceNumber,PreFOffPhone as OfficeNumber,PreFMobile as Mobile,DesignationName as Designation
FROM dbo.Parent INNER JOIN
dbo.Nation ON dbo.Parent.PreCountryID = dbo.Nation.NCode INNER JOIN
dbo.Designation ON dbo.Parent.PreFDesignationID = dbo.Designation.DesignationID
Where where SchoolID= '+ @SchoolID + ' Order By ' + @odBy
exec (@sql)
End

[/code]

Chirag

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-09-10 : 09:28:04

Hello chiragkhabaria,

This is giving the error

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.

Regards
Ceema

quote:

Originally posted by chiragkhabaria


CREATE PROCEDURE sp_ParentListSort
@Mode NVarchar(10)=Null,
@odBy NVarchar(500)=Null,
@SchoolID nvarchar(6)=Null,
@RClass int=Null

AS
Begin
Declare @sql as nvarchar(2000)
Select @sql='SELECT PtCode as ParentCode,SFatherName as FathersName,PreResPhone as ResidenceNumber,PreFOffPhone as OfficeNumber,PreFMobile as Mobile,DesignationName as Designation
FROM dbo.Parent INNER JOIN
dbo.Nation ON dbo.Parent.PreCountryID = dbo.Nation.NCode INNER JOIN
dbo.Designation ON dbo.Parent.PreFDesignationID = dbo.Designation.DesignationID 
Where where SchoolID= '+ @SchoolID + ' Order By ' + @odBy
exec (@sql)
End

Chirag

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-09-10 : 09:30:55

Hello,

I tried with modified query after removing one unnecessary where clause,but it's showing error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ab0'

ab0----> is schoolid

Ceema

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-09-10 : 09:42:56

Hello,

Solved the problem with a slight modification

Select @sql='SELECT Parent.PtCode as ParentCode,SFatherName as FathersName,PreResPhone as ResidenceNumber,PreFOffPhone
as OfficeNumber,PreFMobile as Mobile,DesignationName as Designation
FROM dbo.Parent INNER JOIN
dbo.Student ON dbo.Parent.SchoolID = dbo.Student.SchoolID AND dbo.Parent.PtCode = dbo.Student.PtCode INNER JOIN
dbo.Nation ON dbo.Parent.PreCountryID = dbo.Nation.NCode INNER JOIN
dbo.Designation ON dbo.Parent.PreFDesignationID = dbo.Designation.DesignationID
Where dbo.Student.SchoolID='''+ @SchoolID +''' Order By ' + @odBy

Thank you chiragkhabaria
Ceema

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-10 : 20:37:46

Print the result of @sql and post it here

Madhivanan

Failing to plan is Planning to fail

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-09-11 : 02:17:00

quote:

Originally posted by ceema

Hello chiragkhabaria,

This is giving the error

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.

Regards
Ceema

quote:

Originally posted by chiragkhabaria


CREATE PROCEDURE sp_ParentListSort
@Mode NVarchar(10)=Null,
@odBy NVarchar(500)=Null,
@SchoolID nvarchar(6)=Null,
@RClass int=Null

AS
Begin
Declare @sql as nvarchar(2000)
Select @sql='SELECT PtCode as ParentCode,SFatherName as FathersName,PreResPhone as ResidenceNumber,PreFOffPhone as OfficeNumber,PreFMobile as Mobile,DesignationName as Designation
FROM dbo.Parent INNER JOIN
dbo.Nation ON dbo.Parent.PreCountryID = dbo.Nation.NCode INNER JOIN
dbo.Designation ON dbo.Parent.PreFDesignationID = dbo.Designation.DesignationID 
Where where SchoolID= '+ @SchoolID + ' Order By ' + @odBy
exec (@sql)
End

Chirag

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources