如何编写此存储过程 [英] how to write this stored procedure

问题描述

我有这个存储过程:

Hi,

I have this stored procedure:

CREATE PROCEDURE dbo.uspSelectAllTopicID

    @SortType char
AS
if(@SortType='A')
    begin
        select TopicID from  Topics where TopicDelete =0 order by TopicID
    end
else if(@SortType='B')
    begin
        select TopicID from  Topics where TopicDelete =1 order by TopicID
    end
else if(@SortType='C')
    begin
        select TopicID from  Topics order by TopicID
    end
GO

但是所有的区别仅在短语中，因此我尝试将其写为:

but all differece is in where phrase only, so i tried to write it as:

CREATE PROCEDURE dbo.uspSelectAllTopicID

    @SortType char
AS
select TopicID from  Topics
if(@SortType='A')
    begin
        where TopicDelete =0
    end
else if(@SortType='B')
    begin
        where TopicDelete =1
    end
order by TopicID
GO

但这给我一个错误
那么，什么是正确的呢?

but it give me an error
So, what is the right?

推荐答案

还有更优雅的方法，但是tyhis应该起作用:

There are more elegant ways, but tyhis should work:

declare @whereClause varchar(64)
set @whereClaus = ''
if (@SortType='A')
    begin 
        set @whereClause = 'where TopicDelete=0 '
    end
else if (@SortType='B')
    begin
        set @whereClause = 'where TopicDelete=1 ' 
    end
declare @sql varchar(1024)
set @sql = 'select TopicID from Topics ' + @whereClause + 'order by TopicID'
exec(@sql)

您可以通过将排序类型作为整数值从代码本身传递并直接使用它来尝试以下操作吗?

创建过程dbo.uspSelectAllTopicID
@SortType int
AS

从Topics中选择TopicID，其中TopicDelete = @ SortType按TopicID排序

GO

Hi,

Can you try the following by passing the sort type as integer value from the code itself and directly using it

CREATE PROCEDURE dbo.uspSelectAllTopicID
@SortType int
AS

select TopicID from Topics where TopicDelete =@SortType order by TopicID

GO

使用存储过程sp_executesql此页面上的第二个示例为您找到了:
http://msdn.microsoft.com/de-de/library/ms188001.aspx [ ^ ]

Use the stored procedure sp_executesql the second example on this page here is spot on for you:
http://msdn.microsoft.com/de-de/library/ms188001.aspx[^]

CREATE PROCEDURE dbo.uspSelectAllTopicID
    @SortType char
AS
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @IntVal tinyint;

if(@SortType='A')
    begin
        SET @IntVal = 0;
    end
else if(@SortType='B')
    begin 
        SET @IntVal = 1;
    end

SET @SQLString = N'select TopicID from  Topics where TopicDelete = @level order by TopicID';
SET @ParmDefinition = N'@level tinyint';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVal;
GO

这篇关于如何编写此存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！