带有复选框的SQL SERVER示例

Question

需要您对复选框的帮助。 这里是我的代码：

CREATE PROCEDURE stad_list 

@inp_location_code numeric (3,0) = -1 
@inp_item_code_bgn numeric (5,0) = -1 
@inp_item_code_end numeric (5,0) = -1 
@inp_shipped numeric (2,0) = 0, --checkbox, if it is checked then returns the items in table which were shipped (value in table is 1) 
@inp_check numeric (2,0) = 0, --checkbox, if it is checked then returns data which is still in process of checking for availability in warehouse (value in table is 0) 
@inp_not_ship numeric(2,0) = 0 --checkbox, if it is checked then returns data which had not been shipped during some problems (valuein table is 2) 

AS 

BEGIN 
    SET NOCOUNT ON; 

    DECLARE @SELECT as varchar (3000) = '' 
    DECLARE @WHERE as varchar (1000) = '' 

    IF (@inp_location_code != -1) 
     SET @WHERE = @WHERE + ' WHERE [dbo].item.location =' + CONVERT(varchar,@inp_location_code) 

    IF (@inp_item_code_bgn != -1) 
     SET @WHERE = @WHERE + ' AND [dbo].item.code>=' + CONVERT(varchar, @inp_item_code_bgn) 

    IF (@inp_item_code_end != -1) 
     SET @WHERE = @WHERE + ' AND [dbo].item.code<=' + CONVERT(varchar, @inp_item_code_end) 

    IF (@inp_shipped = 1) 
     SET @WHERE = @WHERE + ' AND [dbo].item.ship = 1' 

    IF (@inp_check = 1) 
     SET @WHERE = @WHERE + ' AND [dbo].item.ship = 0' 

    IF (@inp_not_ship = 1) 
     SET @WHERE = @WHERE + ' AND [dbo].item.ship = 2' 



    SET @SELECT = @SELECT + ' 
     SELECT 
      [dbo].item.name as ''NAME'', 
      [dbo].item.location as ''LOCATION'', 
      [dbo].item.code as ''CODE'', 
      [dbo].item.ship as ''STATUS'' 
     FROM [dbo].item ' 
     + @WHERE 


    EXEC (@SELECT) 

    RETURN @@ROWCOUNT 

END 

它，当我只检查一个复选框工作正常，但不能得到任何数据，同时它的2进行检查。那么，如果我想检查两个复选框，该怎么办？有什么建议么？

这里是我的表

Id  Item_code Item_name Item_location Item_ship 
1  14789  Juice  Tokyo   0 
2  14785  Boots  Tokyo   2 
3  98744  Hat   Osaka   0 
4  36987  Socks  Kyoto   1 

而且有三个复选框

• 发货

• 检查

• 不发货

如果我托运发货后输出会是这样：

Id  Item_code Item_name Item_location Item_ship 
    4  36987  Socks  Kyoto   1 

，但我想为前这样做。当我检查发货和检查，然后输出将是：

Id  Item_code Item_name Item_location Item_ship 
    1  14789  Juice  Tokyo   0 
    3  98744  Hat   Osaka   0 
    4  36987  Socks  Kyoto   1 

Answer 1

使用临时表作为参数

IF (@inp_shipped = 1) OR (@inp_check = 1) OR (@inp_not_ship = 1) 
BEGIN 
    IF OBJECT_ID('tempdb.dbo.#Parameters') IS NOT NULL DROP TABLE dbo.#Parameters 
    SELECT * 
    INTO #Parameters 
    FROM (
     SELECT CASE WHEN @inp_shipped = 1 THEN 1 END AS Ship 
     UNION ALL 
     SELECT CASE WHEN @inp_check = 1 THEN 0 END 
     UNION ALL 
     SELECT CASE WHEN @inp_not_ship = 1 THEN 2 END 
     ) p 
    WHERE p.Ship IS NOT NULL  
    SET @WHERE = @WHERE + ' AND [dbo].item.ship IN (SELECT ship FROM #Parameters)' 
END 

为了获得更好的性能表（由使用索引查找操作），如果你有在船上列的索引，需要在临时表上创建索引：

CREATE INDEX x ON #Parameters(ship) 

Answer 2

你可以结合你的选择到这样的结构：

declare @orcheck varchar(255) 
set @orcheck = 'AND 1=2 (' 

IF (@inp_shipped = 1) 
SET @orcheck = @orcheck + ' OR [dbo].item.ship = 1' 

IF (@inp_check = 1) 
SET @orcheck = @orcheck + ' OR [dbo].item.ship = 0' 

IF (@inp_not_ship = 1) 
SET @orcheck = @orcheck + ' OR [dbo].item.ship = 2' 

set @orcheck = @orcheck + ')' 

这样，当您选择了全部3个复选框，在WHERE条款是这样的：

AND (1=2 
    OR [dbo].item.ship = 1 
    OR [dbo].item.ship = 0 
    OR [dbo].item.ship = 2 
) 

而当没有什么选择，这将是：

AND (1=2 
) 

这是你所期望的，我相信。