Problem in Stored Procedure in SQL Server

Question

0.00/5 (No votes)

See more:

Hi guys,

when i try to create this Stored Procedure :

SQL

CREATE PROCEDURE dbo.uspSelectTopicsForEdit

    @SortDirection varchar(20)
as
select TopicID,TopicAddress,
(select sum(TopicViewNum) from TopicViews where TopicViews.TopicID=Topics.TopicID) as TopicViews,
(select count(Favorites.UserID) from Favorites where Favorites.TopicID=Topics.TopicID)as BookMarks
from Topics
ORDER BY
    CASE @SortDirection
        WHEN 'MostViewed' THEN TopicViews
        WHEN 'MostBookMarked' THEN BookMarks
        WHEN 'Oldest' THEN Topics.TopicID
    END
go

it gives me this errors:

Invalid column name 'TopicViews'

Invalid column name 'BookMarks'

What can i do please

Posted 12-May-11 22:21pm

MrLonely_2

Add a Solution

4 solutions

Solution 1

it's so simply...
TopicViews and BookMarks are tables...not columns...
you can order the output result only by columns

Posted 12-May-11 23:07pm

Nick Reshetinsky

Comments

Pete O'Hanlon 13-May-11 8:13am

If you look at his Sql, you will see they are also column aliases.

Nick Reshetinsky 13-May-11 11:26am

I see thanx

Pete O'Hanlon 13-May-11 16:01pm

No problem. I missed that on my first read through as well.

Solution 2

From what you've posted, TopicViews and BookMarks are table names. You need to modify your ORDER BY clause to use column (field) names e.g.

ORDER BY
  CASE @SortDirection
    WHEN 'MostViewed' THEN TopicViews.TopicViewsColumnName
    WHEN 'MostBokMarked' THEN BookMarks.BookMarksColumnName
    WHEN 'Oldest' THEN Topics.TopicID
  END

where you need to replace TopicViewsColumnName and BookMarksColumnName with the appropriate column names from your tables.

Posted 12-May-11 23:09pm

Geoff Williams

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Accepted Answer · 2011-05-12T23:14:00

These fields are calculated by you so the don't, in any real sense, exist for the user. What you need to do is reuse those values in your case statement.

SQL

CREATE PROCEDURE dbo.uspSelectTopicsForEdit

    @SortDirection varchar(20)
as
select TopicID,TopicAddress,
(select sum(TopicViewNum) from TopicViews where TopicViews.TopicID=Topics.TopicID) as TopicViews,
(select count(Favorites.UserID) from Favorites where Favorites.TopicID=Topics.TopicID)as BookMarks
from Topics
ORDER BY
    CASE @SortDirection
        WHEN 'MostViewed' THEN (select sum(TopicViewNum) from TopicViews where TopicViews.TopicID=Topics.TopicID)
        WHEN 'MostBookMarked' THEN (select count(Favorites.UserID) from Favorites where Favorites.TopicID=Topics.TopicID)
        WHEN 'Oldest' THEN Topics.TopicID
    END

In a situation like this, I would far prefer to use a stored function or handle the sorting on the client side if it's an appropriate business requirement, but this should get you going.

Why do I say handle the sorting at the client side? Well, if the user can change the sort direction, then your sample here requires you to go to the database again (which could return you different results). If you are sorting an in memory collection, then it's one less roundtrip you have to perform. There are caveats, of course, if you were performing paging then you'd want to do this at the server end, but you aren't doing the paging here so it's not an issue.

MrLonely_2 · Accepted Answer · 2011-05-14T03:36:00

Thanks all guys,

I am also found nice solution that:

SQL

CREATE PROCEDURE dbo.uspSelectTopicsForEdit

    @SortDirection varchar(20)
as
select * from (select TopicID,TopicAddress,
(select sum(TopicViewNum) from TopicViews where TopicViews.TopicID=Topics.TopicID) as TopicViews,
(select count(Favorites.UserID) from Favorites where Favorites.TopicID=Topics.TopicID)as BookMarks
from Topics) tmp
ORDER BY
    CASE @SortDirection
        WHEN 'MostViewed' THEN TopicViews
        WHEN 'MostBookMarked' THEN BookMarks
        WHEN 'Oldest' THEN TopicID
    END
go

Thanks every body

Problem in Stored Procedure in SQL Server

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0