Pass a parameter to be used in an "IN Statement"

[pmcdaniel]

In VB I am retrieving one to N number of values and would like to pass this to a Stored procedure as one parameter. Inside the SP I'd like to use the IN statement with this parameter.
VB Code:

StrDocumentNames = "'''" & Join(strDocument, "'', ''") & "'''"
objExec.Open "EXEC spAIGDeleteRecID '" & strEntryDateTime & "', " & StrDocumentNames

This builds this line:
EXEC spAIGDeleteRecID '3/19/2009 12:16:46 PM', '''MN20009'', ''MN20010'''

Here's a simple SP I'm trying to execute:

CREATE PROCEDURE spAIGDeleteRecID(@EntryDateTime  VARCHAR(25),
                                  @RecIDs         VARCHAR(500))   AS


Select *
From CovgData
Where Entry_Date_Time = @EntryDateTime
And rec_id IN (@RecIDs)

This returns zero results despite there being data available.

I've played around with the single quotes but everything other then the above errs.

Seems like what I'm trying to should work. Do I have to build a table in my SP or something and store the values?

Please help.

 

[RiverGuy]

Do a Google search for sql server split function

 

[fhlee]

Hi,
Just to try to eliminate silly mistakes like your @EntryDateTime parameter value.

Assuming Entry_Date_Time field is a DateTime field, it means that the data is accurate to the microseconds/miliseconds. As such, if the @EntryDateTime parameter you are passing in is a "general" time, then it will not match.

For example:
Entry_Date_Time data:
12/30/2009 8:31:21 am
12/30/2009 8:31:53 am
12/30/2009 8:23:12 am
12/30/2009 8:23:22 am
12/30/2009 8:18:17 am

Now, if you pass in @EntryDateTime parameter (VARCHAR) as '12/30/2009 8:30 am', it will not work. because there is no data SPECIFICALLY at 8:30:00 am.

Also, if your @EntryDateTime parameter (VARCHAR) is '12/30/2009', the query will also not return any data as there is no data matching '12/30/2009 12:00:00 am'

Not sure if this helps, just want avoid you doing wild goose chase

I am not able to give any suggestions unless I know the data content of Entry_Date_Time, @EntryDateTime and what you specifically want the output to be.



~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[pmcdaniel]

Thanks for the reply, fhlee. The @EntryDateTime parameter is not the issue. It's used in many of our SP. From my debugging process I know for a fact it's the @RecIDs parameter.

I am researching the Split function suggested by RiverGuy. That looks like the answer I'm looking for.... I'll update this thread when I know.

thanks again.

 

[markros]

The other idea would be to use XML and the third idea (for SQL Server 2008 - to pass the datatable itself).

Check this forum

http://forums.asp.net/54.aspx

your question is asked there periodically.

 

[pmcdaniel]

I looked up a Split function and eventually created this:

IF EXISTS (SELECT name FROM sysobjects WHERE type = 'FN' AND name = 'funcSplitString')
   DROP FUNCTION funcSplitString
GO

CREATE FUNCTION funcSplitString (@String NVARCHAR(4000), @Delimiter CHAR(1))
RETURNS @Results TABLE (Items NVARCHAR(4000))
AS

BEGIN
  DECLARE @Index INT
  DECLARE @Slice NVARCHAR(4000)
  SELECT @Index = 1

  IF @String IS NULL
    RETURN

  WHILE @Index != 0
  BEGIN
    SELECT @Index = CHARINDEX(@Delimiter, @String)
    IF (@Index != 0)
      SELECT @Slice = LEFT(@String, @Index - 1)
    ELSE
      SELECT @Slice = @String

    INSERT INTO @Results(Items) Values (@Slice)
    SELECT @String = RIGHT(@String, LEN(@String) - @Index)

    IF LEN(@String) = 0
      BREAK

  END
  RETURN
END
GO

Here's an example to call it:

DECLARE @Var VARCHAR(400);

Set @Var = 'test1,test2,test3,test4,test5,test5'
SELECT * FROM dbo.funcSplitString(@Var, ',')

It'll return the fields as if they are rows in a table.

Thanks for the suggestion to look up the Split function.

 

[RiverGuy]

No problem. It's one of those common UDF's that are in most SQL developer's toolkits. There's many versions out there on the web, some may be better than others, so make sure you test thoroughly.

 

[markros]

I use this one

http://www.berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split