Question

我想通过一个参数的多个数值。 2005年

Answer 1

您可以选择xml类输入变量,然后不包装这些元素和 gr。例如:

DECLARE @XMLData xml

DECLARE 
    @Code varchar(10),
    @Description varchar(10)

SET @XMLData = 
 
    <SomeCollection>
      <SomeItem>
        <Code>ABCD1234</Code>
        <Description>Widget</Description>
      </SomeItem>
    </SomeCollection>
 

SELECT 
    @Code = SomeItems.SomeItem.value( Code[1] ,  varchar(10) ),
    @Description = SomeItems.SomeItem.value( Description[1] ,  varchar(100) )
FROM @XMLDATA.nodes( //SomeItem ) SomeItems (SomeItem)

SELECT @Code AS Code, @Description AS Description

结果:

Code        Description
==========  ===========
ABCD1234    Widget

Answer 2

页: 1 发挥职能:

ALTER  FUNCTION [dbo].[CSVStringsToTable_fn] ( @array VARCHAR(8000) )
RETURNS @Table TABLE ( value VARCHAR(100) )
AS 
    BEGIN
        DECLARE @separator_position INTEGER,
            @array_value VARCHAR(8000)  

        SET @array = @array +  , 

        WHILE PATINDEX( %,% , @array) <> 0 
            BEGIN
                SELECT  @separator_position = PATINDEX( %,% , @array)
                SELECT  @array_value = LEFT(@array, @separator_position - 1)

                INSERT  @Table
                VALUES  ( @array_value )

                SELECT  @array = STUFF(@array, 1, @separator_position,   )
            END
        RETURN
    END

摘自:

DECLARE @LocationList VARCHAR(1000)
SET @LocationList =  1,32 

SELECT  Locations 
FROM    table
WHERE   LocationID IN ( SELECT   CAST(value AS INT)
                           FROM     dbo.CSVStringsToTable_fn(@LocationList) )

页: 1

SELECT  Locations
FROM    table loc
        INNER JOIN dbo.CSVStringsToTable_fn(@LocationList) list
            ON CAST(list.value AS INT) = loc.LocationID

如果你试图将一个从SSRS到PROC的多价值清单,那是非常有益的。

<>strong>Edited: to show that You may need to CAST - However, to control what is sent in the CSV list

Answer 3

只是建议。你在2005年服务器中确实能够这样做。至少没有直截了当的方法。你们必须使用CSV或XML或64或JSON基地。然而,我强烈劝阻你这样做,因为所有这些错误都容易发生,并产生真正的大问题。

如果您能够转换到服务器2008年,你可以使用表价值的参数(Reference1 ,。

If you cannot I d suggest you to consider the necessity of doing it in stored procedure, i.e. do you really want (should/must) to perform the sql action using SP. If you are solving a problem just use Ad hoc query. If you want to do so in education purposes, ~~you might try~~ don t even try the above mentioned things.

Answer 4

可以通过下列多种方式实现这一目标:

Passing CSV list of strings as an argument to a (N)VARCHAR parameter, then parsing it inside your SP, check here.
首先是创设一个<>XML>,以显示<>/strong>,然后将其作为XML数据型号。您需要将XML纳入到特殊工作方案中,您可能需要PEPLY操作员参加,网址是:check,网址是。

在《战略》之外建立一个“<>temp table,在此处插入多个数值,不需要任何段落。然后,在SP内使用“温床”,http://sqlwithmanoj.com/09/passing-multipledynamic- Values-to-stored-procedures-Functions-part3-by-using-table/”rel=“nofollow noreferer”栏。

如果你在2008年及以上尝试TVPs(可估价的参数),并将其作为准绳通过,check 载于。

友情链接