0

I want to create a stored procedure in T-SQL which takes an input parameter itemVal which is a string.

This parameter takes values in 4 ways:

  1. item name (name: [pen])
  2. item key (key: [pen_k2])
  3. item GUID (guid: [213314-32434-45757]),
  4. itemCode (code: [12435])

I have declared @itemVal in the stored procedure. Any user can provide the value in any of the four forms stated above. How I can parse this string in my stored procedure so that it can do the further procedure. As if it is an item name there is a separate procedure to follow. I want to know what users have provided for input variable @itemVal: item name, item code, item GUID or item key.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
anam
  • 1
  • 1
  • You can use `charindex()` for finding a substring and then do `left()`/`right()` to split the string. – Tanveer Badar Nov 15 '21 at 14:13
  • Why not just have the SP take four parameters, all optional? The same logic you'd do if you parsed one delimited parameter will be same on four separate parameters. – HardCode Nov 15 '21 at 14:21
  • Ok Thank you , can you give a small example. if user provide name:[pencil] – anam Nov 15 '21 at 14:22
  • It can not take four parameters , because its a single field that take different option for the input parameter ,Since this stored procedure is connected to an API. the API has only one feature of selecting an item based on these four options – anam Nov 15 '21 at 14:24
  • I would suggest going back and taking a hard look at your design here. The reason this is so challenging is because the design you have is flawed. – Sean Lange Nov 15 '21 at 16:49
  • It is not a clean way to do it, but maybe you can look if it has "-" char, and the you can assume it is a a GUID, if it has "_" you can assume it is an item key, if it is a number you can assume it is and itemCode and else you assume it is an itemName. Not a good solution, but maybe it is useful for you. – Carlos Nov 15 '21 at 16:59
  • Am I reading this correctly that your API passes, as strings, key-value pairs in the form of `type: identifier` and you want to parse that in T-SQL? I have to agree with other comments that your design is badly flawed and suggest that you re-work your API (written in a general-purpose language) so that it handles determining which type to pass to the T-SQL (not a general-purpose language) – paneerakbari Nov 15 '21 at 20:32
  • Yes thank you all ,I think I need to work on design again – anam Nov 16 '21 at 08:03

0 Answers0