June 30, 2010 at 1:45 pm
i have a stored procedure where i want to use parameters, i want the user to be able to select more than one value in the parameter
what would my syntax be
i would also like the user to select ALL records
June 30, 2010 at 2:32 pm
If you're using SQL Server 2008 you could pass the values as a table variable parameter.
An example is given here.
Regarding "ALL records": it depends how you pass the data to the procedure: if you "convert" ALL into a list of all paramters prior to calling the proc ten it shouldn't matter. But if you want to pass the value "ALL" to the procedure and return every row you could either use an IF ELSE clause to call two different queries within your sp or use a CASE statement. Hard to tell which version would work best for your scenario based on the info provided so far...
June 30, 2010 at 2:56 pm
I do this frequently.
I'll declare the input parameter as
@ParameterName VARCHAR(4000)
Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".
In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx
Hope that helps.
Chris
June 30, 2010 at 3:34 pm
chris-736523 (6/30/2010)
I do this frequently.I'll declare the input parameter as
@ParameterName VARCHAR(4000)
Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".
In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx
Hope that helps.
Chris
Cool idea. Never thought this way before. SQLServer allows multiple parameters? Should be one input/output parameter only. Comma separated string on input and the same on output (first item would be returned value):-D
If you want to pass multiple independant values pass it as individual appropriately typed parameters.
If you want to pass multiple occurances of the above, do it in XML or, in SQL2008 as parameter of a table type, as advised by Lutz.
June 30, 2010 at 4:27 pm
chris-736523 (6/30/2010)
I do this frequently.I'll declare the input parameter as
@ParameterName VARCHAR(4000)
Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".
In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx
Hope that helps.
Chris
I don't recommend using the script mentioned by Chris. There are solutions available that perform MUCH better than a *caugh* while loop.
The best one I've seen so far is by Jeff Moden and posted here. The basic concept is described in the TallyTable article referenced in my signature. As you might notice the code changed between the article and the post I referenced. The reason is rather simple: to make a fast solution even faster.:-D (@Jeff: Yet another advert to your script and article. Raachinggg! :-D)
July 1, 2010 at 5:53 am
thank you, these are my parameters
ALTER PROCEDURE [dbo].[CR_CUSTVOL]
(@SGYR INT)
--@REGION INT,
--@ACCOUNT varchar(20))
--@SEASON Varchar(2),
--@SALESPERSON INT,
--@STORE INT)
this is my where statement, i dont know the exact syntax, im not a newbie, but still learning from all you pros.
WHERE SGYR=@SGYR
AND REGION = @REGION
AND SEASON = @season
AND SALESPERSON_CODE = @SALESPERSON
AND STORE = @STORE
do i need to declare all somewhere, use like instead of = to retrieve multiple values instead of a discrete one?
thanks:-D
July 1, 2010 at 7:52 am
You can use the following in your WHERE clause:
WHERE (SGYR = @SGYR OR @SGYR IS NULL)
AND (REGION = @REGION OR @REGION IS NULL)
AND (SEASON = @season OR @season IS NULL)
AND (SALESPERSON_CODE = @SALESPERSON OR @SALESPERSON IS NULL)
AND (STORE = @STORE OR @STORE IS NULL)
You will need to declare all parameters.
All records will be returned if all input parameres are set to NULL. Parameters set to non-null value (and any combination of such) will be used for filtering.
PLEASE NOTE: The above syntax does look elegant but it will guarantee table or clusterd index scan, which negatively affect the query performance. You should test if its performance is acceptable in your case.
July 1, 2010 at 8:36 am
The table i am quering has over 70000 records, so all is acceptable on some. different thought
i want to return the data set based upon the parameter selection - how do i begin the next set
AS
IF @DIVISION IN('1','2','3')
BEGIN
SELECT
STATUS,
SALESPERSON_CODE,
SALESPERSON_NAME,
DIVISION,
ACCOUNT,
STORE,
SEASON,
SGYR AS YR,
SGDSC AS GRP,
REGION,
CUST_NAME,
SELL_GRPNAME,
PRICE,
(CASE WHEN PRICE>=SYPRC1 THEN SYPRC1 END) AS REG_PRICE,
(CASE WHEN PRICE=SYOFFP THEN SYOFFP END) AS O_P_PRICE,
(CASE WHEN PRICE<SYPRC1 AND PRICE<>SYOFFP THEN PRICE END) AS SALES_PRICE,
SUM(CASE WHEN DIVISION='MISSY' THEN UNITS END) AS MISSY,
SUM(CASE WHEN DIVISION='WOMENS' THEN UNITS END)AS WOMENS,
SUM(CASE WHEN DIVISION='PETITE' THEN UNITS END) AS PETITE,
SUM(CASE WHEN DIVISION='MISSY' AND PRICE>=SYPRC1 THEN UNITS END) AS REG_MISSY_UNITS,
SUM(CASE WHEN DIVISION='MISSY' AND PRICE=SYOFFP AND SYOFFP <>SYPRC1 THEN UNITS END) AS OP_MISSY_UNITS,
SUM(CASE WHEN DIVISION='MISSY' AND PRICE<SYPRC1 AND PRICE<>SYOFFP THEN UNITS END) AS SP_MISSY_UNITS,
SUM(CASE WHEN DIVISION='WOMENS' AND PRICE>=SYPRC1 THEN UNITS END) AS REG_WOMENS_UNITS,
SUM(CASE WHEN DIVISION='WOMENS' AND PRICE=SYOFFP AND SYOFFP <>SYPRC1 THEN UNITS END) AS OP_WOMENS_UNITS,
SUM(CASE WHEN DIVISION='WOMENS' AND PRICE<SYPRC1 AND PRICE<>SYOFFP THEN UNITS END) AS SP_WOMENS_UNITS,
SUM(CASE WHEN DIVISION='PETITE' AND PRICE>=SYPRC1 THEN UNITS END) AS REG_PETITE_UNITS,
SUM(CASE WHEN DIVISION='PETITE' AND PRICE=SYOFFP AND SYOFFP <>SYPRC1 THEN UNITS END) AS OP_PETITE_UNITS,
SUM(CASE WHEN DIVISION='PETITE' AND PRICE<SYPRC1 AND PRICE<>SYOFFP THEN UNITS END) AS SP_PETITE_UNITS
FROM
(SELECT ORSTAT STATUS,ORSTYL STYLE,ORCLOR,ORNUMD ORDER_NO,ORSEQD ORDER_LINE,
ORSIZ1,ORSIZ2, ORSIZ3, ORSIZ4, ORSIZ5, ORSIZ6, ORSIZ7,ORSIZ8,ORSIZ9, ORACCT ACCOUNT,ORSTRP STORE,ORSEAA SEASON,
ORSMN SALESPERSON_CODE,SMNNAM SALESPERSON_NAME,CMROUT REGION, CMBNAM CUST_NAME,B.SGYR,B.SGDSC,GRDESC SELL_GRPNAME,ORPRTY,
CASE WHEN ORPRTY IN (1,2) then 'MISSY'
WHEN ORPRTY IN (6,7) THEN 'PETITE'
WHEN ORPRTY IN (3,4) THEN 'WOMENS'
END DIVISION,
ORPRCE PRICE,SYPRC1,SYOFFP,
(CASE WHEN ORPRCE>=SYPRC1 THEN SYPRC1 END) AS REG_PRICE,
(CASE WHEN ORPRCE=SYOFFP AND SYOFFP<>SYPRC1 THEN SYOFFP END) AS O_P_PRICE,
(CASE WHEN ORPRCE<SYPRC1 AND ORPRCE<>SYOFFP THEN ORPRCE END) AS SALES_PRICE,
ORSIZ1+ORSIZ2+ORSIZ3+ ORSIZ4+ ORSIZ5+ ORSIZ6+ ORSIZ7+ORSIZ8+ORSIZ9 UNITS
FROM OORDDETL
JOIN OORDHDRB
ON ORNUMD=ORNUMB
JOIN OORDHDRA
ON ORNUMD=ORNUMA
JOIN SALESPER
ON ORSMN=SMNCOD
JOIN STYLEMST
ON ORSEAA=SYSEA
AND ORSTYL=SYSTYL
AND ORCLOR=SYCOLR
JOIN CUSTMSTR
ON ORACCT=CMBILL
AND 000=CMSTOR
JOIN
(SELECT
SGCOD, SGYR, SGDSC, SEASON, SEA_COLUMN
FROM
(SELECT SGCOD, SGYR, SGDSC, SGSE01 SEASON, 'SGSE01' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE02 SEASON, 'SGSE02' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE03 SEASON, 'SGSE03' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE04 SEASON , 'SGSE04' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE05 SEASON, 'SGSE05' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE06 SEASON, 'SGSE06' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE07 SEASON, 'SGSE07' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE08 SEASON, 'SGSE08' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE09 SEASON, 'SGSE09' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE10 SEASON, 'SGSE10' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE11 SEASON, 'SGSE11' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE12 SEASON, 'SGSE12' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE13 SEASON, 'SGSE13' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE14 SEASON, 'SGSE14' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE15 SEASON, 'SGSE15' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE16 SEASON, 'SGSE16' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE17 SEASON, 'SGSE17' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE18 SEASON, 'SGSE18' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE19 SEASON, 'SGSE19' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE20 SEASON, 'SGSE20' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE21 SEASON, 'SGSE21' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE22 SEASON, 'SGSE22' SEA_COLUMN FROM SELLGRPA
UNION
SELECT SGCOD, SGYR, SGDSC, SGSE23 SEASON, 'SGSE23' SEA_COLUMN FROM SELLGRPA
)SELL_GRP
WHERE SEASON<>' '
) B
ON
ORSEAA=SEASON
JOIN GRPTAB
ON SEASON=GRSEA
AND SGYR=GRYR
AND ORSTAT IN ('','S')) SALES
WHERE UNITS<>0 --AND ACCOUNT='6360' AND SGYR='10'
AND
SGYR = @YEAR AND
ACCOUNT= @account AND
SALESPERSON_CODE=@SALESPERSON_NUMBER AND
REGION= @REGION
AND DIVISION=@DIVISION
GROUP BY STATUS,
PRICE,SALESPERSON_CODE,SALESPERSON_NAME,DIVISION,ACCOUNT,
STORE, SEASON, SGYR ,SGDSC,
REGION, CUST_NAME,SELL_GRPNAME, SYPRC1,SYOFFP
order by store
OR
@DIVISION ='1'
BEGIN
SELECT
STATUS,
July 1, 2010 at 9:07 am
I don't understand your question. Are you asking how to use IF ... ELSE in SQL or what?
Just in case: http://msdn.microsoft.com/en-us/library/ms182587.aspx
July 1, 2010 at 9:12 am
lmu92 (6/30/2010)
chris-736523 (6/30/2010)
I do this frequently.I'll declare the input parameter as
@ParameterName VARCHAR(4000)
Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".
In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx
Hope that helps.
Chris
I don't recommend using the script mentioned by Chris. There are solutions available that perform MUCH better than a *caugh* while loop.
The best one I've seen so far is by Jeff Moden and posted here. The basic concept is described in the TallyTable article referenced in my signature. As you might notice the code changed between the article and the post I referenced. The reason is rather simple: to make a fast solution even faster.:-D (@Jeff: Yet another advert to your script and article. Raachinggg! :-D)
lmu92,
I tested both versions in question above. Both return more than 1000 values in less than 1 second. If you want to split hairs, can you provide an example of what clearly shows the "MUCH better" performance please? I'm interested in the difference from your perspective *COUGH* (from up there on your very tall horse).
🙂 Now I'm just being argumentative and difficult, which I find quite fun.
I agree that while loops are not the best way to code, and thank you for the example of better code, regardless of performance gains (or possible lack thereof). You should really do something about that cough though. 🙂
Chris
July 1, 2010 at 12:28 pm
thanks but i figured it out. i dont need a book, but thanks for the input
July 1, 2010 at 2:12 pm
chris-736523 (7/1/2010)
...lmu92,
I tested both versions in question above. Both return more than 1000 values in less than 1 second. If you want to split hairs, can you provide an example of what clearly shows the "MUCH better" performance please? I'm interested in the difference from your perspective *COUGH* (from up there on your very tall horse).
🙂 Now I'm just being argumentative and difficult, which I find quite fun.
I agree that while loops are not the best way to code, and thank you for the example of better code, regardless of performance gains (or possible lack thereof). You should really do something about that cough though. 🙂
Chris
Testing against 1000 rows might not be enough to see the difference. And, more important, might not represent the number of rows to be processed in most of the databases. Testing against a million rows with random character a string length of more than 7k character will definitely show the difference.
Instead of providing the test code I'd rather point you at the Tally Table article like I did before. This excellent article includes a test setup for the million row test as well as the method on how to measure it.
A common answer is "I neither have to split a string of such a length nor that large number of rows." My answer usually is: "But wouldn't it be nice to have a solution in place if you're faced with such a solution? Why would you use a solution that probably need to be redesigned to deal with an increased data volume in the first place?"
Just think about what the two phrases "use a while loop" and "it'll take a while" have in common 😀
Regarding that cough: there's nothing I can do about. But there's something you can do: stop using a while loop *cough* 😉
So, instead of taking a performance discussion down to a personal level (like you did by mentioning the horse stuff) you might want to do some reading (e.g. the article I mentioned earlier).
As a side note: when dealing with 1000 rows only, I wouldn't even consider to measure it in seconds....
July 2, 2010 at 12:33 am
lmu92 (6/30/2010)
The best one I've seen so far is by Jeff Moden and posted here.
Interesting. Even though I am credited on that post with a couple of optimisations, it is not my routine of choice. It performs very well for smaller sets but a SQLCLR routine wins in the majority of cases.
Performance comparisons: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html
SQLCLR code by Adam Machanic: http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx
As far as the original requirement in this thread is concerned, passing a delimited string is just one of many options - and not necessarily a great one.
The whole topic of passing arrays/lists around and writing queries with dynamic search conditions is explained in depth on Erland Sommarskog's site: http://www.sommarskog.se/
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 2, 2010 at 1:21 am
Paul White NZ (7/2/2010)
...Interesting. Even though I am credited on that post with a couple of optimisations, it is not my routine of choice. It performs very well for smaller sets but a SQLCLR routine wins in the majority of cases.
...
From my experience, CLR implementation outplays SQL UDF in most of cases (if not all). In T-SQL you cannot do as much optimization for the given task on the lower level as in C# (especially string manipulations). I doubt that any reasonable UDF can be faster than its proper CLR imlementaion (excl. functions which require other table lookups).
I am aware that Jeff is not big fun of CLR's (he did mentioned it in one of the thread), but preferences change over the time. I remember, long ago, I didn't like using JOIN keywords instead of =, *= and =* 😀
July 2, 2010 at 9:12 am
Paul White NZ (7/2/2010)
lmu92 (6/30/2010)
The best one I've seen so far is by Jeff Moden and posted here.Interesting. Even though I am credited on that post with a couple of optimisations, it is not my routine of choice. It performs very well for smaller sets but a SQLCLR routine wins in the majority of cases.
Performance comparisons: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html
SQLCLR code by Adam Machanic: http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx
As far as the original requirement in this thread is concerned, passing a delimited string is just one of many options - and not necessarily a great one.
The whole topic of passing arrays/lists around and writing queries with dynamic search conditions is explained in depth on Erland Sommarskog's site: http://www.sommarskog.se/
Paul
I probably should rephrase it to "The best one I've seen so far using T-SQL..." 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply