Spliting a comma delimited parameter from Crystal

Question

Post reply

Spliting a comma delimited parameter from Crystal

LegoAddict

SSC Veteran

Points: 202
More actions
July 14, 2013 at 3:40 pm

#100158

Hi there,
I'm new to SQL and to Crystal reports. I know that to pass a multiple value parameter to a stored procedure in SQL I need to arrange it into a comma delimited string. I've done that with Crystals join command but I'm having trouble understanding how to split the comma delimited parameter so I can use it in a where IN clause or an If statement (which ever is better). I want this procedure to do the same thing for 3 of the values in the parameter and nothing for the rest of them.
My code so far is below:
Thanks for any suggestions!
create PROCEDURE PeopleHistory @Para1 VARCHAR(20), @Para2 VARCHAR(20) as
IF (@Para2 = 'Old’)
select People.FirstName, People.LastName, Company.Title, Experience.Title from People
LEFT JOIN Experience ON People.ID = Experience.PeopleID
LEFT JOIN Company ON Experience.CompanyID = Company.ID
WHERE People.ID = @Para1
EXCEPT
select People.FirstName, People.LastName, Company.Title, Experience.Title from People
LEFT JOIN Experience ON People.CurrentExperienceID = Experience.ID
LEFT JOIN Company ON Experience.CompanyID = Company.ID
WHERE People.ID = @Para1
ELSE IF (@Para2 = ‘Unkown')
select People.FirstName, People.LastName, Company.Title, Experience.Title from People
LEFT JOIN Experience ON People.ID = Experience.PeopleID
LEFT JOIN Company ON Experience.CompanyID = Company.ID
WHERE People.ID = @Para1
EXCEPT
select People.FirstName, People.LastName, Company.Title, Experience.Title from People
LEFT JOIN Experience ON People.CurrentExperienceID = Experience.ID
LEFT JOIN Company ON Experience.CompanyID = Company.ID
WHERE People.ID = @Para1
ELSE IF (@Para2 = ‘unkown2’)
select People.FirstName, People.LastName, Company.Title, Experience.Title from People
LEFT JOIN Experience ON People.ID = Experience.PeopleID
LEFT JOIN Company ON Experience.CompanyID = Company.ID
WHERE People.ID = @Para1
EXCEPT
select People.FirstName, People.LastName, Company.Title, Experience.Title from People
LEFT JOIN Experience ON People.CurrentExperienceID = Experience.ID
LEFT JOIN Company ON Experience.CompanyID = Company.ID
WHERE People.ID = @Para1
ELSE IF (@Para2 is null)
Do Nothing

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Does this article helps you?

http://www.sqlservercentral.com/articles/T-SQL/63003/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

LegoAddict SSC Veteran Points: 202 More actions · Answer 2

Luis Cazares (7/14/2013)
Does this article helps you?
http://www.sqlservercentral.com/articles/T-SQL/63003/

Thank you Luis!

I'm not sure if it does. I understand the logic but my parameter won't have the '1st comma' and I'm not 100% sure how I use this to then run my clauses against.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You could as well try to use the 8k Splitter presented here.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

It will return you a table from which you can do a join or semi-join (IN or EXISTS). 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

LegoAddict SSC Veteran Points: 202 More actions · Answer 4

Luis Cazares (7/14/2013)
You could as well try to use the 8k Splitter presented here.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
It will return you a table from which you can do a join or semi-join (IN or EXISTS). 😉

Got it working! Took me a bit to realize that I just had to copy/paste the tally code and then call it as a function but I got it! Awesome stuff, thank you Luis!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

I'm glad I could help. It might not have been possible without the great articles from Jeff. 🙂

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Luis Cazares (7/15/2013)
I'm glad I could help. It might not have been possible without the great articles from Jeff. 🙂

You're too kind. :blush: Thanks, Luis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LegoAddict SSC Veteran Points: 202 More actions · Answer 7

Jeff Moden (7/15/2013)
Luis Cazares (7/15/2013)
I'm glad I could help. It might not have been possible without the great articles from Jeff. 🙂
You're too kind. :blush: Thanks, Luis.

Thank you Jeff!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

LegoAddict (7/16/2013)
Jeff Moden (7/15/2013)
Luis Cazares (7/15/2013)
I'm glad I could help. It might not have been possible without the great articles from Jeff. 🙂
You're too kind. :blush: Thanks, Luis.
Thank you Jeff!

No problem and thank you for the feedback. Glad the article helped and that you got your problem solved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)