Procedure that receives indefined number of param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Procedure that receives indefined number of param

Author

Topic

fabioks
Starting Member

1 Post

Posted - 2006-09-27 : 18:22:28

Please, can someone help me !!!

I need to find a way for my stored procedure (SQL Server 2005) to receive an indefined number of parameters from my application (C#).

The number of parameters passed by my application is variable, so, I can't declare variables as usual.

I know thats not possible by array, but I heard that it is possible by object...is it true !? If not, is there another way to do that !? And I wished to do that in the procedure, I have the solution by the application, but my procedure would be executed a lot of times depending on how much parameter are...

Thanks in advance...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 01:34:08

Either use delimited string as parameter, or use XML.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 04:04:26

See:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

for how to handle/split a delimited list in a stored procedure.

I've just been doing one today FWIW:

We have a web page with a list of "options" for a product. Some require a Qty, some are just CheckBoxes. Trouble is the <INPUT type="text"> for the Qty ones always return a value (i.e. including empty) and the <INPUT type="checkbox"> ONLY return a value if they are checked.

So what I have done is:


<input type="checkbox" value="MyProductCode1" name="OPTION_CHECK" id="OPTION_CHECK" />
<input type="checkbox" value="MyProductCode2" name="OPTION_CHECK" id="OPTION_CHECK" />
...
<input type="text" name="OPTION_QTY" id="OPTION_QTY" value="" />
<input type="hidden" value="MyProductCode3" name="OPTION_PRODUCT" id="OPTION_PRODUCT" />

<input type="text" name="OPTION_QTY" id="OPTION_QTY" value="" />
<input type="hidden" value="MyProductCode4" name="OPTION_PRODUCT" id="OPTION_PRODUCT" />
...

and I then get 3 arrays in the REQUEST.FORM object:

OPTION_CHECK
OPTION_PRODUCT
OPTION_QTY

OPTION_CHECK has a list of the Product Codes that were checked - the un-checked ones are "missing"

OPTION_PRODUCT and OPTION_QTY have the same number of items, in the same order and contain one element for each product.

I pass these three arrays, as comma delimited lists, to my SProc and process them as follows:


CREATE PROCEDURE dbo.DNG_SP_SOP_ORDD_Customise_Save
	@OPTION_PRODUCT	varchar(8000) = NULL,	-- Product Code List
	@OPTION_QTY	varchar(8000) = NULL,	-- Qty List (synchronised with @OPTION_PRODUCT)
	@OPTION_CHECK	varchar(8000) = NULL,	-- Checkboxes - list of Product Codes checked
	...

DECLARE	@OptionList TABLE
(
	T_ProductCode	varchar(100) NOT NULL,
	T_Qty		int NOT NULL,
	PRIMARY KEY 
	(
		T_ProductCode
	)
)

-- NOTE: "MySplitFunction" returns a table with columns [Item] & [Value]
INSERT	@OptionList(T_ProductCode, T_Qty)
SELECT	[T_ProductCode] = LTrim(RTrim(P.Value)),
	[T_Qty] = COALESCE(NullIf(Q.Value, ''), 0)
FROM	dbo.MySplitFunction(@OPTION_PRODUCT, ',') AS P
	LEFT OUTER JOIN dbo.MySplitFunction(@OPTION_QTY, ',') AS Q
		ON Q.Item = P.Item	-- The two lists are synchronised, link them together
WHERE	NullIf(LTrim(RTrim(Q.Value)), '') IS NOT NULL
UNION ALL
SELECT	[T_ProductCode] = LTrim(RTrim(C.Value)),
	[T_Qty] = 1
FROM	dbo.MySplitFunction(@OPTION_CHECK, ',') AS C
WHERE	NullIf(LTrim(RTrim(C.Value)), '') IS NOT NULL

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources