May 31, 2013 at 1:54 am
Hi, I have a problem that I have struggled with for a couple of hours now, without being able to solve it.
I have a report, that returns funding for a particular customer. The customer name is passed to the underlying procedure as a parameter, @customer. So far everything is fine.
But here comes the tricky part. In my customer database, there have been some errors in the registration,
so one of my customers have been registered with two different names, lets say customer A and customer B.
And in my procedure I have a condition saying that customerName = @customer.
What I want to do is check the customer parameter, and if it is A or B, then my condition should return the data from both customers, not only A or B.
One way would to create two @customer parameters in the report, and let the condition be
customerName = @customerA OR customerName = @customerB but I would rather not use that approach.
May 31, 2013 at 5:08 am
I guess it depends on whether it's a known customer or not and how many there are. If it's just the one customer then a hard coded case statement adding the additional customer field to the where clause based on the value of the incoming @customer however that means hard coding data into the stored procedure which is never good.
I'm not sure why you wouldn't want to change the stored procedure. You could add a second parameter with a default value of '' and then have a case statement which adds the second parameter to the where clause if it's not '' . That way you don't need to change the calling code apart from when you need to pass in a second customer name.
Or add a flag column to the customer table in the DB which links the customers together, then your stored procs can check this flag column for an additional id to pull out. That way no matter which customer name is used you'll be able to extract the other one without having hard coded values in stored procedures.
regards,
Paul
May 31, 2013 at 7:47 am
What is your primary key for customer? It seems like you should be passing in the CustomerID instead of the customer name. What happens when you have two customers with the same name?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply