February 24, 2011 at 6:02 am
Hi All,
Is there a way in creating stored procedure where you set parameters to accept multiple values, eg you want to set a parameter @DEPARTMENT, where when you execute the procs, you will have the option to enter more than one department name to generate the report
February 24, 2011 at 6:13 am
As you're posting it in the SQL 2008 forum, I'll assume that's the version you're running.
In which case, Table Valued Parameters suit this requirement nicely:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
If you're not using 2008 yet, your options are either have a fixed number of values allowed and create different parameters for each one which your front end will pass the values to, or send in a delimited list as a string then split the string out in the stored procedure.
February 24, 2011 at 6:28 am
Hi could you please explain the last part of your answer about the delimited list as a string
Thanks
February 24, 2011 at 7:08 am
Relatively easy concept... have a character defined that acts as a separator.
EG. ¦,;: could all be used
IT,HR,Sales.... etc
You will then need to LOOP through, pulling a value off each time as you know each value is separated by specific character.
February 24, 2011 at 7:21 am
Hi Thanks,
Can I please send you the query so you modify it for me?
Thanks
The query is as follows
USE [HQRMS]
GO
CREATE PROCEDURE [dbo].[sp_getSummarySalesReport_By_DeptTest]
(@DATEFROM AS DATETIME,@DATETO AS DATETIME,@DEPARTMENT AS NVARCHAR(50))
AS
SELECT DEPARTMENT.NAME AS DEPARTMENT,CATEGORY.NAME AS CATEGORY,ITEM.DESCRIPTION AS DESCRIPTION,STRS.ID,([TRANSACTION].TIME) AS TIME,
STRS.[NAME] AS STORENAME,SREGNS.NAME AS REGION, SUM(TRANSACTIONENTRY.QUANTITY)
AS QUANTITY,SUM(TRANSACTIONENTRY.PRICE * TRANSACTIONENTRY.QUANTITY)
AS TOTAL
FROM TRANSACTIONENTRY
INNER JOIN [TRANSACTION] WITH(NOLOCK)
ON TRANSACTIONENTRY.TRANSACTIONNUMBER = [TRANSACTION].TRANSACTIONNUMBER AND TRANSACTIONENTRY.STOREID = [TRANSACTION].STOREID
INNER JOIN BATCH WITH(NOLOCK)
ON [TRANSACTION].BATCHNUMBER = BATCH.BATCHNUMBER AND [TRANSACTION].STOREID = BATCH.STOREID
LEFT JOIN ITEM WITH(NOLOCK)
ON TRANSACTIONENTRY.ITEMID = ITEM.ID
LEFT JOIN DEPARTMENT WITH(NOLOCK)
ON ITEM.DEPARTMENTID = DEPARTMENT.ID
LEFT JOIN CATEGORY WITH(NOLOCK)
ON ITEM.CATEGORYID = CATEGORY.ID
LEFT JOIN SUPPLIER WITH(NOLOCK)
ON ITEM.SUPPLIERID = SUPPLIER.ID
LEFT JOIN CUSTOMER WITH(NOLOCK)
ON [TRANSACTION].CUSTOMERID = CUSTOMER.ID
LEFT JOIN REASONCODE AS REASONCODEDISCOUNT WITH(NOLOCK)
ON TRANSACTIONENTRY.DISCOUNTREASONCODEID = REASONCODEDISCOUNT.ID
LEFT JOIN REASONCODE AS REASONCODETAXCHANGE WITH(NOLOCK)
ON TRANSACTIONENTRY.TAXCHANGEREASONCODEID = REASONCODETAXCHANGE.ID
LEFT JOIN REASONCODE AS REASONCODERETURN WITH(NOLOCK)
ON TRANSACTIONENTRY.RETURNREASONCODEID = REASONCODERETURN.ID
LEFT JOIN STRS
ON [TRANSACTION].STOREID = STRS.ID
LEFT JOIN SREGNS
ON STRS.SREGNID=SREGNS.ID
LEFT JOIN CASHIER WITH(NOLOCK)
ON [TRANSACTION].CASHIERID = CASHIER.ID AND STRS.ID = CASHIER.STOREID
WHERE CONVERT(DATETIME,[TRANSACTION].TIME,103) BETWEEN CONVERT(DATETIME,@DATEFROM,103) AND CONVERT(DATETIME,@DATETO,103)
AND STRS.[NAME] IS NOT NULL AND @DEPARTMENT IN(SELECT DEPARTMENT.NAME FROM DEPARTMENT)
GROUP BY DEPARTMENT.NAME,CATEGORY.NAME,ITEM.DESCRIPTION,STRS.ID,STRS.NAME,SREGNS.NAME,[TRANSACTION].TIME
February 24, 2011 at 7:33 am
Well as stated, if SQL 2008, I would be looking at the Table Valued Parameters as suggested.
Sorry, but this is your work, I am not going to do it, how else do you expect to learn.
February 24, 2011 at 7:38 am
OK will try as you have suggested and give you a feed back,
but it is 2005 I am using so what do you suggest
Thanks
February 24, 2011 at 7:39 am
BTW you may want to do some reading on naming (best) practises....
sp_ tut tut :hehe:
February 24, 2011 at 7:48 am
Well you did post in the SQL2008 area, hence the advice given.
It depends on the requirement. If you only need to cater for 3-4 departments, then I would go for additional parameters, if more than that, I would go for the separated string and split out the values in the procedure.
February 24, 2011 at 8:35 am
No I have about 10 departments
February 24, 2011 at 10:21 am
Here is why using the sp_ prefix for your proc names is a bad idea:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 24, 2011 at 3:50 pm
I have a function that may help you...of course, you'd have to use dynamic SQL (which has it's own caveats)
It can be used nicely to return a set of values for the IN keyword in the WHERE clause
Ex: SELECT dbo.fx_FormatArrayText('IT|HR|ACCOUNTING|SALES','|', 1)
Returns:
('IT','HR','ACCOUNTING','SALES')
If your @DEPARTMENT parameter has the list of departments then you can easily add that to your WHERE clause...something like:
DECLARE @SQL varchar(1000)
DECLARE @DeptWhere varchar(100)
SET @DeptWhere = dbo.fx_FormatArrayText(@DEPARTMENT,',', 1)
SET @DeptWhere = ' AND S.DEPT IN ' + @DeptWhere
SET @SQL = '
SELECT
D.NAME
...
...
FROM TRANSACTIONENTRY TR
INNER JOIN [TRANSACTION] TX WITH(READUNCOMMITTED) ON
TR.TRANSACTIONNUMBER = TX.TRANSACTIONNUMBER
AND TR.STOREID = TX.STOREID
...
...
WHERE
CONVERT(DATETIME,TX.TIME,103) ...
AND S.[NAME] IS NOT NULL
' + RTRIM(@DeptWhere) + '
GROUP BY
D.NAME, C.NAME, I.DESCRIPTION, S.ID,
S.NAME, SR.NAME, TX.TIME '
If you need to check your query just do a quick PRINT(@SQL), you can copy and paste that into another query window to check it.
Here's the code for the scalar UDF:
/* ############################################################################################################### */
--FORMAT DELIMITED STRING
/* ############################################################################################################### */
CREATE FUNCTION fx_FormatArrayText (
@String varchar(1500),
@Delimiter char(1),
@NumberQuotes int = 1)
RETURNS varchar(1500) AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Convert delimited text within a string into parenthesized values (quotes optional)
Department:DB&R
Created For:
----------------------------------------------------------------------------------------------------------------
NOTES:@Delimiter - Tells function the delimiter to parse the text with
@NumberQuotes - How many quotes you wisdh to have in the OUTPUT string
----------------------------------------------------------------------------------------------------------------
Created On:10/20/2005
Create By:Serge Mirault
----------------------------------------------------------------------------------------------------------------
Modified On:
Modified By:
Changes:
1.
----------------------------------------------------------------------------------------------------------------
SELECT dbo.fx_FormatArrayText('IT|HR|ACCOUNTING|SALES','|', 1)
*/
BEGIN
DECLARE @Quote varchar(10)
SET @Quote = ''
/* ######################################### START MAIN FUNCTION HERE ########################################## */
IF @NumberQuotes >= 1
BEGIN
SET @Quote = SPACE(@NumberQuotes)
SET @Quote = REPLACE(@Quote, ' ', '''')
END
IF @Delimiter = ' '
BEGIN
/* Eliminate double spaces in text string */
WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', ' ')
END
END
ELSE
BEGIN
/* Eliminate all spaces in text string */
WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', '')
END
END
/* Convert supplied delimiter with open quotes, comma, and close quotes */
SET @String = REPLACE(@String, @Delimiter, @Quote + ',' + @Quote)
/* Add opening and closing quotes and parentheses */
SET @String = '(' + @Quote + @String + @Quote + ')'
/* ########################################## END MAIN END HERE ########################################### */
RETURN @String
END
/*
SELECT dbo.fx_FormatArrayText('IT|HR|ACCOUNTING|SALES','|', 1)
*/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 24, 2011 at 4:26 pm
opc.three (2/24/2011)
Here is why using the sp_ prefix for your proc names is a bad idea:
Hm, nice article find, OPC. Thank you.
To the OP: You can also have a look on this site for the delimitedsplit8k function. A link to one of the versions is here:
http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589
There's a more current version that Jeff developed but I don't have the link handy.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply