Cursors - Using a Variable in the Where Clause

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)

Cursors - Using a Variable in the Where Clause

Author

Topic

james_yoder@hotmail.com
Starting Member

9 Posts

Posted - 2003-10-02 : 09:43:59

I am developing a Stored Procedure where one of the inputs will be the name of the column that will be used or order the results of a query. I would like to use the variable in a CURSOR. Problem is when I run this code in SQL Query Analyzer, I get an error.

Here is the code :

DECLARE @SortColumn VarChar(25)
SET @SortColumn = 'PhotoKeyID'
DECLARE Gallery_Cursor CURSOR READ_ONLY FOR SELECT * FROM v_Gallery ORDER BY @SortColumn
OPEN Gallery_Cursor
Select @@Cursor_Rows AS CursorRows
Close Gallery_Cursor
DEALLOCATE Gallery_Cursor

Here is the Error Message

Server: Msg 1008, Level 15, State 1, Line 6
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.

I know it's the use of the Variable that's giving me the problem, but I understand I can use variables in the Cursor statement. (It works if I use a variable in the WHERE clause)

Any help will be appreciated.

James

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-02 : 11:48:18

have a look at DYNAMIC SQL

james_yoder@hotmail.com
Starting Member

9 Posts

Posted - 2003-10-02 : 12:06:13

I have been looking at Dynamic SQL, but Still don't seem to be getting it here is what I have :

DECLARE @Statement nVarChar(1000)
DECLARE @SortColumn nVarChar(35)
SET @SortColumn = 'PhotoName'
SET @Statement = 'SELECT * FROM v_Gallery ORDER BY ' + @SortColumn
DECLARE Gallery_Cursor CURSOR SCROLL FOR SELECT sp_executeSQL @StatementOPEN Gallery_Cursor
Select @@Cursor_Rows AS CursorRows
Close Gallery_Cursor
DEALLOCATE Gallery_Cursor

Not sure what's wrong with this statement but It's not working. Can someone point out what I'm doing wrong?

Thanks

James

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-02 : 13:09:35

Well, for one thing you don't need an ORDER BY clause if you are only getting a row count. Second, you don't need a cursor to get that either:

SELECT Count(*) FROM v_Gallery

Third, since you don't have a WHERE clause on that table, you can get the row count this way:

SELECT rows FROM sysindexes WHERE id=object_id('v_Gallery') AND indid<2

Fourth, if you provided a sample and not the actual code you want to write, please don't do this in the future. Describe as much of your ACTUAL process that you are using. Simplifying an example for the sake of demonstration often leads to people coming up with solutions that don't work because "Well, I forgot to mention that I also do this....so your solution won't work."

Fifth, search SQL Team for "dynamic ORDER BY" and you'll find a link or two that describe a few methods of accomplishing it.

james_yoder@hotmail.com
Starting Member

9 Posts

Posted - 2003-10-02 : 14:19:03

OK Robvolk,
Here is what I'm After :
1. As stored procedure that will take the following inputs :
a. ColumnName variable that will be used to sort the records in my table.
b. PageSize variable that will deterimine the number of records to be returned from the stored procedure. (sub-set of the table being queried.)
c. PageNumber variable to tell what page to return.
2. The Stored procedure will return :
a. The total number of pages that are in the original table (TableRows/PageSize)
b. A ResultSet containing the page indicated by the PageNumber. This result set will have PageSize number of records in it.

3. What should happen in the Stored Procedure :
a. #of pages calculated by taking the Original table row count and div that by the PageSize.
b. Order the original table by the indicated SP input variable with the ColumnnName.
c. Extract Records N through Q where :
N = (PageSize * PageNumber-1) + 1
Q = N + PageSize

Seems like more noise then what you really need, that's why I didn't include it. I tried to boil it down to the one Core statement that is giving me issues.

James

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-02 : 14:45:04

Search this site for "paging" and you'll find plenty of solutions...

Owais

Make it idiot proof and someone will make a better idiot

james_yoder@hotmail.com
Starting Member

9 Posts

Posted - 2003-10-05 : 05:03:16

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources