Syntax/Structure Problem - 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

Transact-SQL (2000)

Syntax/Structure Problem

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-08-18 : 09:18:58

I am trying to write a new SP, but am unsure of how to properly write it. Here is what I have so far:

ALTER PROCEDURE Get_OpenTicketListing AS
(
	declare @OrderBy varchar(55)
)
SELECT
	ticketNumber, 
	[Date], 
	[Time], 
	techID, 
	caller, 
	dept, 
	pcode, 
	tycall
FROM
	calllog
WHERE
	status = 'Open' 
AND 
	dept <> '!' 
AND 
	dept <> '*' 
AND 
	caller <> '*'
IF (@OrderBy <> '') THEN
	ORDER BY
		ID
ELSE
	ORDER BY
		@OrderBy
END

Basically, I just want to grab some data and have a variable to customize the sort only when it is told to do so. Can someone help me get this one right, please?

- - - -
- Will -
- - - -

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-18 : 09:34:48

order by
case when @OrderBy = 'ticketNumber' then ticketNumber end ,
case when @OrderBy = 'Date' then '[Date]' end ,
.... ,
ID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-08-18 : 10:55:34

What about the declaration? Do I have that right? VS.Net was telling me I had a syntax error there. Here is the error and the SQL:

quote:
Incorrect syntax near the keyword 'declare'.
Line 4: Incorrect syntax near ')'.
The SELECT item indentified by the ORDER BY number 2 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.

ALTER PROCEDURE Get_OpenTicketListing AS
(
	declare @OrderBy varchar(55)
)
SELECT
	ticketNumber, 
	[Date], 
	[Time], 
	techID, 
	dept, 
	pcode, 
	tycall
FROM
	calllog
WHERE
	status = 'Open' 
AND 
	dept <> '!' 
AND 
	dept <> '*' 
AND 
	caller <> '*'
ORDER BY 
CASE WHEN @OrderBy = 'ticketNumber' then ticketNumber end ,
CASE WHEN @OrderBy = 'Date' then '[Date]' end ,
CASE WHEN @OrderBy = 'Time' then '[Time]' end ,
CASE WHEN @OrderBy = 'techID' then techID end ,
CASE WHEN @OrderBy = 'dept' then dept end ,
CASE WHEN @OrderBy = 'pcode' then pcode end ,
CASE WHEN @OrderBy = 'tycall' then tycall end ,
ELSE ID

If I remove the keyword 'declare', then I get the following error:

quote:
ADO error: Line 3: Incorrect syntax near '@OrderBy'.
Must declare the variable '@OrderBy'.

How can I get this to work?

- - - -
- Will -
- - - -

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-18 : 11:01:06

it's like this:

ALTER PROCEDURE Get_OpenTicketListing
(
declare @OrderBy varchar(55)
)

as

...

Go with the flow & have fun! Else fight the flow :)

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-08-18 : 11:22:27

Thank you so much for all of your help (both of you). :) Here is the final result that worked:

ALTER PROCEDURE Get_OpenTicketListing
(
	@OrderBy nvarchar(12)
)
AS
SELECT
	ticketNumber, 
	[Date], 
	[Time], 
	techID, 
	dept, 
	pcode, 
	tycall
FROM
	calllog
WHERE
	status = 'Open' 
AND 
	dept <> '!' 
AND 
	dept <> '*' 
AND 
	caller <> '*'
ORDER BY 
CASE WHEN @OrderBy = 'ticketNumber' THEN ticketNumber END,
CASE WHEN @OrderBy = 'Date' THEN [Date] END,
CASE WHEN @OrderBy = 'Time' THEN [Time] END,
CASE WHEN @OrderBy = 'techID' THEN techID END,
CASE WHEN @OrderBy = 'dept' THEN dept END,
CASE WHEN @OrderBy = 'pcode' THEN pcode END,
CASE WHEN @OrderBy = 'tycall' THEN tycall END,
CASE WHEN @OrderBy = '' THEN ID END

- - - -
- Will -
- - - -
http://www.servicerank.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources