Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Spliting a comma delimited parameter from Crystal


Spliting a comma delimited parameter from Crystal

Author
Message
LegoAddict
LegoAddict
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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
LegoAddict
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
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
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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
LegoAddict
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
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
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
LegoAddict
LegoAddict
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search