March 4, 2011 at 8:30 am
Hi,
I have to create a stored procedure which takes input as
ID
description 1
description2
description3
.
.
description13
descriptions are the values user selected for the id.At max the description value would be for 5 i.e remaining 8 params will be null in mostly all cases.
Does this sp needs 14 input params or can I have some sort of array,as I dont know how many values would be there.
This sp will insert into table a
Table A
id
id_input
description
There would be multiple rows for description in table A.Please help me out with suggestion as to how to create it
Thanks
March 4, 2011 at 8:36 am
Is there a max of 13 descriptions or is the number of descriptions dynamic?
_______________________________________________________________
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/
March 4, 2011 at 8:54 am
You have two choices.
1. Use all 14 parameters, set them to NULL as a default, and insert them or process them as needed.
2. Use some encoding like XML in a parameter, but the processing gets a little harder, and you are adding overhead.
I would recommend #1. Is it that hard in development just to include the parameters? You don't need to send them all from the client if they don't exist.
March 4, 2011 at 9:07 am
Depending on what you're going to do with them, and if you're using SQL 2008 (as per the forum you posted in), you could probably use a single table-variable parameter, and have that handle all the complexity for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2011 at 9:15 am
There are 13 choices available out of which user can select may be 1 or 2 or 3 or may be all 13.
So the description parameters will depend on what user selects and my java code will send that to sql
rest all descriptions will be null
March 4, 2011 at 9:17 am
Pink123 (3/4/2011)
There are 13 choices available out of which user can select may be 1 or 2 or 3 or may be all 13.So the description parameters will depend on what user selects and my java code will send that to sql
rest all descriptions will be null
Given that the simplest is just to code all the parameters.
ID int,
Desc1 varchar(10) = null,
etc...
Then you only have to specify the parameters you need.
_______________________________________________________________
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/
March 4, 2011 at 9:18 am
Thanks for the reply.
Then If user selected only 3 param,I will pass only three and I do not need to pass the rest as they are null by default.Is that what you mean?
March 4, 2011 at 9:21 am
Thanks for your reply
I am using sqlserver 2005 and is this feature also available there..I am not aware of this feature.It woule be of help if you could explain
March 4, 2011 at 9:21 am
You got it. Of course if this list expands you may end up kicking yourself as this list gets longer and longer. This approach is the simplest but it is not very flexible. You can always address the flexibility issue later if you need to. 🙂
_______________________________________________________________
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/
March 4, 2011 at 9:27 am
I concur with Sean. If I have
create procedure MyProc
@param1 int = null
, @param2 int = null
, @param3 int = null
as
If @param1 is not null
insert into MyTable select @param1
If @param2 is not null
insert into MyTable select @param2
If @param3 is not null
insert into MyTable select @param3
return
I can call this with:
exec MyProc 1, 2, 3
or
exec MyProc @param3 = 2
March 4, 2011 at 9:28 am
I will have to check in the stored procedure for each description parameter is null or not and if not null I will insert row in Table A
like
if(description 1 is not null)
Insert into a (description 1)
if(description 2 is not null)
Insert into a (description 2)
if(description 3 is not null)
Insert into a (description 3)
if(description 4 is not null)
Insert into a (description 4)
Is that what you are talking about isnt there any other ,better way.
Thanks
March 4, 2011 at 9:51 am
You could create your stored procedure to do the insert for only 1 row and then call it however many times you need to.
_______________________________________________________________
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply