Issue with comma seperated list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Issue with comma seperated list

Author

Topic

atlachar123456
Starting Member

33 Posts

Posted - 2011-10-18 : 12:41:24

how can we pass a string with Comma seperated as a parameter to the report?I mn we need to pass this string to the procedure
In my procedure @superProductType is a parameter(multiselect parameter) which has the values 'coede,uhsdg,kiahd,jgrtea,lmxbfas,kdnhd,kskue'...etc 'ALL'is also included in the list
can any one suggest me with the best code how to write?in the procedure
--Handling 'ALL' when using multi select for 'SuperProductType'--already written a code for this in the procedure
IF CHARINDEX('ALL',@superProductType)=1
BEGIN
SELECT @superProductType = 'ALL'
END

Now i want the code how to pass a string with Comma seperated as a parameter to the report

atlaaaaaaaa

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-18 : 13:22:42

Why not a table valued parameter?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-18 : 13:40:51

I assume you are using something like SSRS that builds those strings for you, so you cannot use table values paramters?

There are lots of options. You can use Dynamic SQL. You can get/make a split/prase fuction to break the string apart and use an IN clause or a JOIN to the function. You could insert the values from the function into a temp table or table variable and do an IN or a JOIN to that table, etc..

atlachar123456
Starting Member

33 Posts

Posted - 2011-10-18 : 14:01:50

quote:
Originally posted by Lamprey

I assume you are using something like SSRS that builds those strings for you, so you cannot use table values paramters?

There are lots of options. You can use Dynamic SQL. You can get/make a split/prase fuction to break the string apart and use an IN clause or a JOIN to the function. You could insert the values from the function into a temp table or table variable and do an IN or a JOIN to that table, etc..

atlaaaaaaaa

atlachar123456
Starting Member

33 Posts

Posted - 2011-10-18 : 14:05:38

quote:
Originally posted by Lamprey

I assume you are using something like SSRS that builds those strings for you, so you cannot use table values paramters?

There are lots of options. You can use Dynamic SQL. You can get/make a split/prase fuction to break the string apart and use an IN clause or a JOIN to the function. You could insert the values from the function into a temp table or table variable and do an IN or a JOIN to that table, etc..

Can you please tell me the code how to write this comma seperation and inserting into temp table and joining this function to temp table

atlaaaaaaaa

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-18 : 14:12:57

Here is the code for a split function, note this one is limited to 8000 character string:

 CREATE FUNCTION dbo.SplitString8KToVarchar
(
    @String VARCHAR(8000), 
    @Delimiter CHAR(1) = ','
)
--
-- Based on the Function my Jeff Moden: http://www.sqlservercentral.com/articles/Tally+Table/72993/
--
RETURNS TABLE WITH SCHEMABINDING AS

RETURN
    --"Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
    -- enough to cover VARCHAR(8000)
    WITH Tens(N) AS 
    (
         SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
         SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
         SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ), --10E+1 or 10 rows
    Hundreds(N) AS (SELECT 1 FROM Tens a CROSS JOIN Tens b), --10E+2 or 100 rows
    Thousands(N) AS (SELECT 1 FROM Hundreds a CROSS JOIN Hundreds b), --10E+4 or 10,000 rows max
    Tally(N) AS (SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@String,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Thousands
                ),
    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM Tally t
                  WHERE (SUBSTRING(@String,t.N,1) = @Delimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT Position = ROW_NUMBER() OVER(ORDER BY s.N1),
        Value   = SUBSTRING(@String,s.N1,ISNULL(NULLIF(CHARINDEX(@Delimiter,@String,s.N1),0)-s.N1,8000))
   FROM cteStart s
;

Here is the code that you can use to populate a table variable. You might want to use a temp table due to the lack of statistics on table variables, but you get the idea:

DECLARE @InputString VARCHAR(8000);
SET @InputString = 'coede,uhsdg,kiahd,jgrtea,lmxbfas,kdnhd,kskue';

DECLARE @SplitTable TABLE (ID INT, Value VARCHAR(8000));

INSERT @SplitTable
SELECT Position, Value
FROM dbo.SplitString8KToVarchar(@InputString, DEFAULT);

SELECT *
FROM @SplitTable

atlachar123456
Starting Member

33 Posts

Posted - 2011-10-19 : 16:01:56

Hi Lamprey,
Is this function works in sybase database,we are using sybase database does CTE works in sybase?I am not sure

atlaaaaaaaa

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-19 : 16:07:58

SQLTeam is a Microsoft SQL Server website. For Sybase questions try http://dbforums.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-10-21 : 04:43:00

quote:
Originally posted by DonAtWork

Why not a table valued parameter?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Yes if the version is 2008 or later

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 04:45:25

quote:
Originally posted by madhivanan

quote:
Originally posted by DonAtWork

Why not a table valued parameter?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Yes if the version is 2008 or later

Madhivanan

Failing to plan is Planning to fail

In any case not sure if its supported in Sybase

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

atlachar123456
Starting Member

33 Posts

Posted - 2011-10-21 : 09:33:54

Actually we are using sql server 2005 for reporting services and database is syabse

atlaaaaaaaa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 09:37:11

quote:
Originally posted by atlachar123456

Actually we are using sql server 2005 for reporting services and database is syabse

atlaaaaaaaa

even then procedure you write on backend should be Sybase procedure. Thats why it was suggested to try on sybase forum.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources