December 6, 2012 at 3:52 pm
So I spent most of the dya searching for an answer, which I did find. As the title states, I was trying to find a method to pass multiple values to a SP.
I found the following which works great when I have one parameter.
CREATE PROCEDURE usp_UK_Products (@Product NVARCHAR(MAX))
AS
DECLARE @xml XML
DECLARE @Productlist_table TABLE (Product NVARCHAR(15))
SET @xml = N'<root><r>' + replace(@Product, ',', '</r><r>') + '</r></root>'
INSERT INTO @Productlist_table
SELECT c.value('.', 'nvarchar(15)')
FROM @xml.nodes('//root/r') AS a(c)
SELECT BugID, BugTitle, CompanyName, CurrentOwner, Product, ProblemArea
FROM View_UK_TicketsDetails
WHERE Product IN (SELECT Product FROM @Productlist_table)
Like I said, this method works great when having only one parameter. However, I need help on to make it work when having multple parameters. I did some tweaking today by duplicating some of the code and renaming it, but it's a no go for me.
Looking to add another parameter called CompanyName.
Hopefully someone here can assist.
Thanks!
December 6, 2012 at 4:10 pm
DarthBurrito (12/6/2012)
So I spent most of the dya searching for an answer, which I did find. As the title states, I was trying to find a method to pass multiple values to a SP.I found the following which works great when I have one parameter.
CREATE PROCEDURE usp_UK_Products (@Product NVARCHAR(MAX))
AS
DECLARE @xml XML
DECLARE @Productlist_table TABLE (Product NVARCHAR(15))
SET @xml = N'<root><r>' + replace(@Product, ',', '</r><r>') + '</r></root>'
INSERT INTO @Productlist_table
SELECT c.value('.', 'nvarchar(15)')
FROM @xml.nodes('//root/r') AS a(c)
SELECT BugID, BugTitle, CompanyName, CurrentOwner, Product, ProblemArea
FROM View_UK_TicketsDetails
WHERE Product IN (SELECT Product FROM @Productlist_table)
Like I said, this method works great when having only one parameter. However, I need help on to make it work when having multple parameters. I did some tweaking today by duplicating some of the code and renaming it, but it's a no go for me.
Looking to add another parameter called CompanyName.
Hopefully someone here can assist.
Thanks!
It looks like you are parsing a comma seperated list?? Have a look at Jeff Moden's Delimited Split 8k. (link in my signature) if you can post some sample data and the expected output of the split and function we here on the forum could probably help out a little more than a point in a good direction.
i would also look at the EXISTS clause, Gail Shaw has a great write up on it here http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
December 6, 2012 at 7:14 pm
We are in the SQL 2008 forum right?
You can try something like the following. It may not be perfect (can't test because I don't have the underlying View_UK_TicketsDetails table) but it should be pretty close.
CREATE TYPE ProductListTable (Product NVARCHAR(15))
GO
CREATE PROCEDURE usp_UK_Products (@Product NVARCHAR(MAX))
AS
DECLARE @Product
AS ProductListTable
SELECT BugID, BugTitle, CompanyName, CurrentOwner, Product, ProblemArea
FROM View_UK_TicketsDetails
WHERE Product IN (SELECT Product FROM @Product)
GO
DECLARE @ProductList
AS ProductListTable
INSERT INTO @ProductList
SELECT '101' UNION ALL SELECT '102' UNION ALL SELECT '102'
EXECUTE usp_UK_Products @ProductList
For more information on using Table Valued Parameters you can consult BOL: http://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 7, 2012 at 7:17 am
Let me add that I'm using this SP for a SSRS report. As the code I provided stands, the SP has one parameter called Products. So the parameter accepts multiple values, I found the code I provided and tweaked it to what I need, which worked great for only one parameter.
Now, I would like to add a second, maybe a third parameter. This is where I'm running into a wall.
December 7, 2012 at 8:28 am
DarthBurrito (12/7/2012)
Let me add that I'm using this SP for a SSRS report. As the code I provided stands, the SP has one parameter called Products. So the parameter accepts multiple values, I found the code I provided and tweaked it to what I need, which worked great for only one parameter.Now, I would like to add a second, maybe a third parameter. This is where I'm running into a wall.
Presumably I am misreading what you are asking for, but from what you have said:
CREATE PROCEDURE usp_UK_Products
(@Product NVARCHAR(MAX), @CompanyName NVarchar(500))
as
...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2012 at 9:37 am
You can pass as many parameters as you want in an XML parameter, including tables of data.
The real key is being able to parse the XML parameter using the built-in XML functions.
xml Data Type Methods
http://technet.microsoft.com/en-us/library/ms190798.aspx
Without knowing your XML schema, I can't offer much more help, but there are number of articles posted here and other places that may be helpful. Google is your friend.
December 7, 2012 at 11:35 am
DarthBurrito (12/7/2012)
Let me add that I'm using this SP for a SSRS report. As the code I provided stands, the SP has one parameter called Products. So the parameter accepts multiple values, I found the code I provided and tweaked it to what I need, which worked great for only one parameter.Now, I would like to add a second, maybe a third parameter. This is where I'm running into a wall.
SSRS provides a built-in capability to auto-expand the list of values:
SELECT ...
FROM ...
WHERE
column_name IN (@Products_Parameter)
SSRS automatically properly explodes the multi-valued parameter to a valid IN list.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply