asked on

comma delimited values in sp

Qualifier states are currently stored in t_retail_qualifier_states

Create a t_retail_Qualifier_State_List table that includes the combinations of qualifiers that are in use for a particular event:

This is how the data will look when SP will be inserting the data into above table.

Need to create a procedure sp_retail_get_qualifier_seq_id (@ievent_id, @iqualifier_state_names, @iqualifier_state_ids, @oQualifierSeqId). Where you can provide either the state names or the state ids. Possible values for input to this procedure will look like (1)@ievent_id- must be provided. e.g:- 'ESF111' (2) @iqualifier_state_names= COUP0N1, GREEN,MEDIUM (comma separated values)(3) @iqualifier_state_ids = 49,54,62.
We need to validate provided input values against t_retail_qualifier_state_list table to check if values exist in that table for that event?. If the value is not found, it will insert a new record and return the value of qualifier_seq_id (autoincrement field)
The procedure will also check whether the provided input is valid: ( Only one state from each group & proper group type.). So for group_id = 0 u can have only 1 value from 0 to 4, for group_id = 4 u can have only 1 value from 0 to 6. so If you have @iqualifier_state_names= Blue, GREEN then its invalid and throw error.
If both , qualifier_state_names, qualifier_state_ids are provided then take , qualifier_state_names.
Values provided has to be correct so check those values against t_qualifier_states table, check if qualifier_state_name and qualifier_state_ids matches for particular event or not?
I already wrote the sp but it will work only when @iqualifier_state_names= COUPON1, GREEN
What if more than 3 or 4 comma delimited values??(which is the real case) do I need cursor for that?
not sure how to write this

cottage125

ASKER

@iqualifier_state_names= COUP0N1, GREEN,MEDIUM....
@iqualifier_state_ids = 49,54,62....

So if we can create a temp table to differentiate those values,

CREATE TABLE #temp_tbl (structure same as above table t_retail_qualifier_states)
( qualifier_state_id integer
, event_id integer
, group_id integer
, state_number integer
, qualifier_state_name varchar(255)
)
and then insert those values in temp table.so then wht is a best way to parse these comma seperated values and
insert them as individual entry in temp table?

deviprasadg

You can insert the comma separated values into your temp table without using cursors.
Check the below link:
http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows

Alternatively you can use the below methods to check if the comma separated values are present in base table:
http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx

cottage125

ASKER

Thanks but I have almost figured out everything except this condition,
"The procedure will also check whether the provided input is valid: ( Only one state from each group & proper group type.). So for group_id = 0 u can have only 1 value from 0 to 4, for group_id = 4 u can have only 1 value from 0 to 6. (see first table above) so If you have @iqualifier_state_names= Blue, GREEN then its invalid and throw error."
Please take a look at attached code and let me know how to check above logic???
sp-retail.sql

ASKER CERTIFIED SOLUTION

deviprasadg

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

cottage125

ASKER

Thanks a lot.