Pass Multi valued variable in SP

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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:

SQL

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)+'|%')

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

Regards
Dhana

Posted 27-Oct-13 20:22pm

DhanaNat

Add a Solution

Comments

DhanaNat 28-Oct-13 2:50am

Could u please explain me , @v didnt declared anywhere and what it means?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

coded007 · Answer 1 · 2013-10-27T20:41:00

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

SQL

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)

SQL

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

SQL

AND PrdVarVendorType IN (Select * FROM UserDefinedFunction)

Pass Multi valued variable in SP

1 solution

Solution 1

Add your solution here

Preview 0

Pass Multi valued variable in SP

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us