SQL Server中存储过程中的问题 [英] Problem in Stored Procedure in SQL Server

问题描述

大家好，

当我尝试创建此存储过程时:

Hi guys,

when i try to create this Stored Procedure :

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

它给了我这个错误:

无效的列名称"TopicViews"

无效的列名书签"


我该怎么办

it gives me this errors:

Invalid column name ''TopicViews''

Invalid column name ''BookMarks''


What can i do please

推荐答案

这些字段是由您计算出来的，因此对于用户而言，不存在任何实际含义.您需要做的是在case语句中重用这些值.

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.

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.

就这么简单...
TopicViews和BookMarks是表...不是列...
您只能按列对输出结果进行排序

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

根据您发布的内容，TopicViews和BookMarks是表名.您需要修改ORDER BY子句以使用列(字段)名称，例如

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

您需要用表中的相应列名称替换TopicViewsColumnName和BookMarksColumnName.

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

这篇关于SQL Server中存储过程中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！