CODEWITHSUNDEEP
sql
Webster Alexander III
Webster Alexander III

Reputation: 69

SQL/Sybase database not returning results

I am passing a variable to the database that contains a list of companies... the var is passing but the database is not returning a result. How do I pass the list? and what kind of Where statement would I use?

Upvotes: 0

Views: 231

Answers (2)

KouryouChairudo
KouryouChairudo

Reputation: 31

If we are passing a comma seperated list to a stored procedure to retrieve a number of records that have one of these strings as a value in a field we use a SQL function. This function returns a table wich can be used to filter the data.

This is our function (you should execute the create before using it)

CREATE FUNCTION [dbo].[GetTableFromString]
(
    @string NVARCHAR(4000),
    @separator CHAR
)
RETURNS @resultTable TABLE (string NVARCHAR(255))
AS
  BEGIN
    DECLARE @myString NVARCHAR(255)
    IF (LEN(@string) > 0)
      BEGIN
        DECLARE @start INT,
                @charIndex INT
        SET     @start = 1
        SET     @charIndex = CHARINDEX(@separator, @string, @start) -- Get the position of the first seperator

        WHILE ( @charIndex >= 0 )
          BEGIN
            IF @charIndex = 0 -- No seperator found, take the whole string and insert it in the result table
              BEGIN
                SELECT  @myString = SUBSTRING(@string, @start, LEN(@string) - @start + 1)
                SET     @charIndex = -1
              END
            ELSE
              BEGIN
                SELECT  @myString = SUBSTRING(@string, @start, CHARINDEX(@separator, @string, @start) - @start)
                SET     @start = CHARINDEX(@separator, @string, @start) + 1 -- Set the start position of the char after the seperator
                SET     @charIndex = CHARINDEX(@separator, @string, @start) -- Get the position of the next seperator
              END

            INSERT INTO @ResultTable (string) VALUES (@myString)
          END
      END

    RETURN 
  END

This is how the function then can be used:

SELECT  YourField1, 
        YourField2, 
        ... 
FROM    YourTableName 
WHERE   YourFieldx In ( SELECT  string 
                        FROM    dbo.GetTableFromString('IBM,WalMart,KMart', ','))

Upvotes: 2

abe_crabbers
abe_crabbers

Reputation: 201

I think you need to post what you're doing.

It sounds almost as if you had a VARCHAR variable containing a comma separated list to a stored procedure, in which case the SP would need to use dynamic sql, but I can't tell.

Upvotes: 0

Related Questions