SImple code error

9 Sep 2005 1:02 PM

Tim::..

Why do I get an error on this????

Incorrect syntax near @sortdir

Thanks

...Code..
CREATE PROCEDURE PageDetails
(

@OfficeName   nvarchar(10),
@sortCriteria nvarchar(255),
@sortdir nvarchar(255)

)
AS

SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
O.offName, O.officeID
FROM tblOfficePageContent R
   INNER JOIN tblOffice O ON
      R.officeID = O.officeID
   INNER JOIN tblPageContent P ON
      R.pageID = P.pageID
AND O.offName = @OfficeName
ORDER BY str(@sortCriteria) @sortdir
GO

9 Sep 2005 1:08 PM

RioDD

"GO" is not used in stored procedures. Delete it

Show quote

"Tim::.." wrote:

> Why do I get an error on this????
>
> Incorrect syntax near @sortdir
>
>
> Thanks
>
> ..Code..
> CREATE PROCEDURE PageDetails
> (
>
> @OfficeName   nvarchar(10),
> @sortCriteria nvarchar(255),
> @sortdir nvarchar(255)
>
> )
> AS
>
> SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
> O.offName, O.officeID
> FROM tblOfficePageContent R
>    INNER JOIN tblOffice O ON
>       R.officeID = O.officeID
>    INNER JOIN tblPageContent P ON
>       R.pageID = P.pageID
> AND O.offName = @OfficeName
> ORDER BY str(@sortCriteria) @sortdir
> GO

9 Sep 2005 1:18 PM

RioDD

Also you need "RETURN" at the end of stored procedure...

Show quote

"RioDD" wrote:

> "GO" is not used in stored procedures. Delete it
>
> "Tim::.." wrote:
>
> > Why do I get an error on this????
> >
> > Incorrect syntax near @sortdir
> >
> >
> > Thanks
> >
> > ..Code..
> > CREATE PROCEDURE PageDetails
> > (
> >
> > @OfficeName   nvarchar(10),
> > @sortCriteria nvarchar(255),
> > @sortdir nvarchar(255)
> >
> > )
> > AS
> >
> > SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
> > O.offName, O.officeID
> > FROM tblOfficePageContent R
> >    INNER JOIN tblOffice O ON
> >       R.officeID = O.officeID
> >    INNER JOIN tblPageContent P ON
> >       R.pageID = P.pageID
> > AND O.offName = @OfficeName
> > ORDER BY str(@sortCriteria) @sortdir
> > GO

9 Sep 2005 1:28 PM

ML

No pun intended, but I suggest you take another good look at that first post
and maybe a peek or two in Books Online.

IMHO the only obvious fault is a missing comma.

SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
O.offName, O.officeID
FROM tblOfficePageContent R
   INNER JOIN tblOffice O ON
      R.officeID = O.officeID
   INNER JOIN tblPageContent P ON
      R.pageID = P.pageID
AND O.offName = @OfficeName
ORDER BY str(@sortCriteria), @sortdir     -- and there it is now

Sorting criteria must be separated by columns.

However, if @sortdir should hold the direction the values must be ordered
by, then Jose's solution is the correct one.

ML

9 Sep 2005 1:19 PM

Jose G. de Jesus Jr MCP, MCDBA

hi tim,

it seems to me that you want a dynamic sql
try this

CREATE PROCEDURE PageDetails
@OfficeName nvarchar(10),
@sortCriteria nvarchar(255),
@sortdir nvarchar(255)

AS
declare @test nvarchar(4000)
set @test='SELECT P.pageID, P.description, P.header, P.content,
P.modificationDate,
O.offName, O.officeID
FROM tblOfficePageContent R
   INNER JOIN tblOffice O ON
      R.officeID = O.officeID
   INNER JOIN tblPageContent P ON
      R.pageID = P.pageID
AND O.offName = @OfficeName ORDER BY' +str(@sortCriteria)+','+str(@sortdir)

EXEC(@test)

--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

Show quote

"Tim::.." wrote:

> Why do I get an error on this????
>
> Incorrect syntax near @sortdir
>
>
> Thanks
>
> ..Code..
> CREATE PROCEDURE PageDetails
> (
>
> @OfficeName   nvarchar(10),
> @sortCriteria nvarchar(255),
> @sortdir nvarchar(255)
>
> )
> AS
>
> SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
> O.offName, O.officeID
> FROM tblOfficePageContent R
>    INNER JOIN tblOffice O ON
>       R.officeID = O.officeID
>    INNER JOIN tblPageContent P ON
>       R.pageID = P.pageID
> AND O.offName = @OfficeName
> ORDER BY str(@sortCriteria) @sortdir
> GO

9 Sep 2005 2:09 PM

Jose G. de Jesus Jr MCP, MCDBA

CREATE PROCEDURE PageDetails
@OfficeName nvarchar(10),
@sortCriteria nvarchar(255),
@sortdir nvarchar(255)

AS
declare @test nvarchar(4000)
set @test='SELECT P.pageID, P.description, P.header, P.content,
P.modificationDate,
O.offName, O.officeID
FROM tblOfficePageContent R
   INNER JOIN tblOffice O ON
      R.officeID = O.officeID
   INNER JOIN tblPageContent P ON
      R.pageID = P.pageID
AND O.offName = @OfficeName ORDER BY'
+rtrim(@sortCriteria)+','+rtrimr(@sortdir)

EXEC(@test)

--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

Show quote

"Jose G. de Jesus Jr MCP, MCDBA" wrote:

> hi tim,
>
> it seems to me that you want a dynamic sql
> try this
>
> CREATE PROCEDURE PageDetails
> @OfficeName nvarchar(10),
> @sortCriteria nvarchar(255),
> @sortdir nvarchar(255)
>
>
> AS
> declare @test nvarchar(4000)
> set @test='SELECT P.pageID, P.description, P.header, P.content,
> P.modificationDate,
> O.offName, O.officeID
> FROM tblOfficePageContent R
>    INNER JOIN tblOffice O ON
>       R.officeID = O.officeID
>    INNER JOIN tblPageContent P ON
>       R.pageID = P.pageID
> AND O.offName = @OfficeName ORDER BY' +str(@sortCriteria)+','+str(@sortdir)
>
>
> EXEC(@test)
>
>
> --
> thanks,
>
> ------------------------------------
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
>
> "Tim::.." wrote:
>
> > Why do I get an error on this????
> >
> > Incorrect syntax near @sortdir
> >
> >
> > Thanks
> >
> > ..Code..
> > CREATE PROCEDURE PageDetails
> > (
> >
> > @OfficeName   nvarchar(10),
> > @sortCriteria nvarchar(255),
> > @sortdir nvarchar(255)
> >
> > )
> > AS
> >
> > SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
> > O.offName, O.officeID
> > FROM tblOfficePageContent R
> >    INNER JOIN tblOffice O ON
> >       R.officeID = O.officeID
> >    INNER JOIN tblPageContent P ON
> >       R.pageID = P.pageID
> > AND O.offName = @OfficeName
> > ORDER BY str(@sortCriteria) @sortdir
> > GO

9 Sep 2005 1:50 PM

Rakesh

In ORDER BY you cannot use variables. While executing, you will get following
error
Error: The SELECT item identified by the ORDER BY number 1 contains a
variable as part of the expression identifying a column position. Variables
are only allowed when ordering by an expression referencing a column name.

Therefore, you will have to use dynamic query.

Try code below

CREATE PROCEDURE PageDetails
(
@OfficeName   nvarchar(10),
@sortCriteria nvarchar(255),
@sortdir nvarchar(255)
)
AS

print(' SELECT P.pageID, P.description, P.header, P.content,
P.modificationDate,
O.offName, O.officeID
FROM tblOfficePageContent R
   INNER JOIN tblOffice O ON
      R.officeID = O.officeID
   INNER JOIN tblPageContent P ON
      R.pageID = P.pageID
AND O.offName = ''' + @OfficeName + '''
ORDER BY ' + @sortCriteria + ', ' + @sortdir + ') ')
GO

Rakesh

Show quote

"Tim::.." wrote:

> Why do I get an error on this????
>
> Incorrect syntax near @sortdir
>
>
> Thanks
>
> ..Code..
> CREATE PROCEDURE PageDetails
> (
>
> @OfficeName   nvarchar(10),
> @sortCriteria nvarchar(255),
> @sortdir nvarchar(255)
>
> )
> AS
>
> SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
> O.offName, O.officeID
> FROM tblOfficePageContent R
>    INNER JOIN tblOffice O ON
>       R.officeID = O.officeID
>    INNER JOIN tblPageContent P ON
>       R.pageID = P.pageID
> AND O.offName = @OfficeName
> ORDER BY str(@sortCriteria) @sortdir
> GO

9 Sep 2005 2:26 PM

Hari Prasad

Hi,

This is not because of GO or RETURN. This error is coming because of below 2
issues

1. No STR function. You have to USE CONVERT(CHAR,COlumn)

2. Directly you can not use variables (@sortCriteria) in ORDER BY Clause. In
this case use Dynamic SQL.

See the modified procedure.

CREATE PROCEDURE PageDetails
(
@OfficeName   nvarchar(10),
@sortCriteria nvarchar(255),
@sortdir nvarchar(255)
)
AS

exec(' SELECT P.pageID, P.description, P.header, P.content,
P.modificationDate,
O.offName, O.officeID
FROM tblOfficePageContent R
   INNER JOIN tblOffice O ON
      R.officeID = O.officeID
   INNER JOIN tblPageContent P ON
      R.pageID = P.pageID
AND O.offName = @OfficeName
ORDER BY convert(char,@sortCriteria) ')
GO

Thanks
Hari
SQL Server MVP

Show quote

"Tim::.." <myatix_at_hotmail.com> wrote in message
news:6BF53C99-1426-4654-9FF0-E04278F73B6B@microsoft.com...
> Why do I get an error on this????
>
> Incorrect syntax near @sortdir
>
>
> Thanks
>
> ..Code..
> CREATE PROCEDURE PageDetails
> (
>
> @OfficeName   nvarchar(10),
> @sortCriteria nvarchar(255),
> @sortdir nvarchar(255)
>
> )
> AS
>
> SELECT P.pageID, P.description, P.header, P.content, P.modificationDate,
> O.offName, O.officeID
> FROM tblOfficePageContent R
>   INNER JOIN tblOffice O ON
>      R.officeID = O.officeID
>   INNER JOIN tblPageContent P ON
>      R.pageID = P.pageID
> AND O.offName = @OfficeName
> ORDER BY str(@sortCriteria) @sortdir
> GO