在SP中传递多值变量 [英] Pass Multi valued variable in SP

问题描述

你可以帮我吗



我需要通过va，vb， IN CLAUSE中的vc，vd，ve，vv值通过商店程序



如何将多值变量传递给存储过程



例如：

Hi,

Can u pl help me

I need to pass va,vb,vc,vd,ve,vv values in IN CLAUSE via store procedure

how to pass Multivalued Variables to a Stored Procedure

Eg:

SELECT  DISTINCT    PrdVarCode[Product Code],PrdVarVendorType[Vendor Type],PrdChrActiveYN[Active Y/N],
                (SELECT     EWMVarWarehouseName
                 FROM       dbo.ESWarehouseMst (NOLOCK)
                 WHERE      EWMIntID=sh.WhIntEWMID)[WareHouse Name]
        FROM    dbo.CbzPrdMst (NOLOCK) pm INNER JOIN dbo.CbzStockonhand (NOLOCK) sh
                ON pm.PrdVarCode=sh.WhIntPrdCode
        WHERE   StockStatus=1 AND PrdVarVendorType IN(@VarPrdVendorType) AND PrdChrActiveYN='Y'
                AND WhIntEWMID IN (SELECT    EWMIntID
                                   FROM      dbo.ESWarehouseMst (NOLOCK)
                                   WHERE    (@VarEWMIntID) LIKE '%|'+CONVERT(VARCHAR,EWMIntID)+'|%')

这是我的sp的一部分，我需要在@VarPrdVendorType中传递va，vb，vc，vd，ve



问候

Dhana

this is my part of sp, i need to pass va,vb,vc,vd,ve in @VarPrdVendorType

Regards
Dhana

推荐答案

将此代码块附加到存储过程中，以便它可以解决您的问题

Append this block of code to your stored procedure so that it will solves your problem

CREATE  Table #TempTable
(
    Val Varchar(max)
)

 DECLARE @LenString int

      WHILE len( @VarPrdVendorType ) > 0
         BEGIN

            SELECT @LenString =
               (CASE charindex( ',', @v )
                   WHEN 0 THEN len( @v )
                   ELSE ( charindex( ',', @v ) -1 )
                END
               )

            INSERT INTO #Temptable
               SELECT substring( @VarPrdVendorType, 1, @LenString )

            SELECT @VarPrdVendorType =
               (CASE ( len( @v ) - @LenString )
                   WHEN 0 THEN ''
                   ELSE right( @VarPrdVendorType, len( @v ) - @LenString - 1 )
                END
               )
         END

和代码块为

and Code block as

Quote：

AND PrdVarVendorType IN（@VarPrdVendorType）

AND PrdVarVendorType IN(@VarPrdVendorType)

AND PrdVarVendorType IN (Select Val From #Temptable)

你可以在用户定义的函数中使用上面的代码，这样你就可以使用每个地方和代码看起来像

you can use above code in user defined function so that you can use every where and code looks like

AND PrdVarVendorType IN (Select * FROM UserDefinedFunction)

这篇关于在SP中传递多值变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！