存储过程混淆 [英] Stored Procedure Confusion

问题描述

我正在尝试创建一个存储过程，它将检查表中的列表名称和相应的(布尔值/位)值，即使其中一个记录的值为真，存储过程也应返回真，如果不是，则返回假.

I am trying to create a stored procedure which will check for a list names in a table and there corresponding (boolean/bit) values, even if one of record has value as true, stored procedure should return True, if not then return false.

这是桌子，

Table Name - FruitCrate
Column A (VarChar (Max)) - FruitName
Column B (bit)  -  Eatable

现在我想要一个存储过程，我可以提供一个 List FruitNames 并检查它们中的任何一个是否可以食用，然后返回 true 否则返回 false.

Now I want a stored procedure whom I can provide a List FruitNames and it checks if any of them is eatable then return true otherwise false.

不知道如何开始，因为从不发送列表作为存储过程的参数.

编辑

这是我正在尝试但出现语法错误的方法，

This is what I am trying but getting syntax error,

Create PROCEDURE [dbo].[ProcedureName]
(
    @FruitNames varchar(max)
)
AS

Select * From    
(SELECT * FROM  FruitCrate WHERE FruitName IN (' +@FruitNames+ '))

WHERE FruitCrate.Eatable= 1

错误**

... ')' 附近的语法不正确.... 关键字附近的语法不正确哪里".

... Incorrect syntax near ')'.... Incorrect syntax near the keyword 'WHERE'.

**

推荐答案

第 1 步:您可以将您的fruitNames 以逗号分隔(或任何其他不在任何水果名称中的分隔符，例如'!' 或 '|') 字符串

Step 1: You can send your fruitNames as a comma separated (or any other delimiter which is not in any fruit names, ex '!' or '|') string

第 2 步:您需要一个表值 T-Sql 函数来拆分分隔字符串 (比如这个)(比如表列名是myColumn)

Step 2: You need a Table Valued T-Sql Function to split a delimited string (Such as this) (say, table column name is myColumn)

CREATE FUNCTION  Split (@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (myColumn nvarchar(500))
AS
BEGIN

    --Function body
    RETURN @Results
END

第 3 步:编写一个存储过程，将拆分后的水果名称与表进行比较.

Step 3: Write a stored procedure to compare splitted fruitenames with the table.

CREATE PROCEDURE CheckEatables
   @fruitName nVarchar(4000) --Change the length as required
AS
BEGIN
    SELECT FruitName, COALESCE(Eatable,0) Eatable
    FROM FruitCrate fc JOIN dbo.Split(@fruitNames,',') fn
                   ON fc.FruitName = fn.myColumn
END

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