Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Spliting a comma delimited parameter from Crystal Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:42 AM
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
Post #1473403
Posted Sunday, July 14, 2013 3:54 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 4,043, Visits: 9,190
Does this article helps you?
http://www.sqlservercentral.com/articles/T-SQL/63003/



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473405
Posted Sunday, July 14, 2013 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:42 AM
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.
Post #1473408
Posted Sunday, July 14, 2013 4:31 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 4,043, Visits: 9,190
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473411
Posted Monday, July 15, 2013 3:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:42 AM
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!
Post #1473912
Posted Monday, July 15, 2013 4:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 4,043, Visits: 9,190
I'm glad I could help. It might not have been possible without the great articles from Jeff.


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473919
Posted Monday, July 15, 2013 10:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 35,770, Visits: 32,434
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. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1473968
Posted Tuesday, July 16, 2013 5:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:42 AM
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. Thanks, Luis.


Thank you Jeff!
Post #1474073
Posted Tuesday, July 16, 2013 7:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 35,770, Visits: 32,434
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. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1474124
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse