-2

How to execute stored procedure by select in instead of = by vb.net and mssql stored procedure

Dim sSQL As String
Dim objConn As SqlConnection
Dim objcmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds = New DataSet()

sSQL = "getinvoice"
objConn = utility.getconnect
objcmd = New SqlCommand(sSQL, objConn)
objcmd.CommandType = CommandType.StoredProcedure
objcmd.Parameters.Add("@invoiceid", SqlDbType.VarChar)
objcmd.Parameters.Item("@invoiceid").Value = "1,5,13,18"    '<-- problem
da = New SqlDataAdapter(objcmd)
da.Fill(ds)

ALTER PROCEDURE [dbo].[getinvoices]
@invoiceid varchar(50)
AS
BEGIN
select * from invoice where invoiceid in @invoiceid     '<-- problem
END
CDspace
  • 2,639
  • 18
  • 30
  • 36
fsze88hoho
  • 3
  • 1

1 Answers1

1

I think you need to use dynamic query. Change the procedure as 

ALTER PROCEDURE [dbo].[getinvoices]
    @invoiceid varchar(50)
    AS
    BEGIN
    declare @sql nvarchar(4000)
    set @sql =' select * from invoice where invoiceid in  ( '+ @invoiceid     +')' 
    EXEC (@sql)
    END

Or Use a split function to split the values out into a table variable (there's a lot of split functions out there, quick search will throw one up).

 ALTER PROCEDURE [dbo].[getinvoices] @invoiceid VARCHAR(50)
AS 
    BEGIN
        SELECT  *
        FROM    invoice
        WHERE   invoiceid IN ( SELECT   *
                               FROM     dbo.splitstring(@invoiceid) )

    END

Check here for Split string function

Shiju Shaji
  • 1,682
  • 17
  • 24
  • there is no function dbo.splitstring exist is that needs to create by myself?How? – fsze88hoho Jul 07 '17 at 09:17
  • Yes you have to .. Check this link https://stackoverflow.com/questions/10914576/t-sql-split-string – Shiju Shaji Jul 07 '17 at 09:41
  • That's the worse kind of splitter you can use @ShijuShaji. Read this: http://www.sqlservercentral.com/articles/Tally+Table/72993/ – S3S Jul 07 '17 at 14:38