Click here to Skip to main content
15,170,331 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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])


What I have tried:

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 item Val: item name, item code, item GUID or item key
Posted
Updated 15-Nov-21 5:13am

To be honest, it's a pretty poor idea to start with: you are better creating four procedures and calling the appropriate one with just the value to process instead.

SQL is not good at string handling is just part of the reason. But if you must, then start here: SQL Server CHARINDEX() Function[^] and here: SQL Server SUBSTRING() Function[^] but it'll be messy.
   
Comments
Member 15352290 15-Nov-21 9:54am
   
I tried something like this : SELECT RIGHT('name:[Pencil]',CHARINDEX(':','name:[Pencil]')) but the output is 'ncil]'. Since i have to check for four possible user inputs , Each will be of different lengths. As name:[value], here value can have several different inputs of different lengths how i can check that
OriginalGriff 15-Nov-21 10:10am
   
So go back to the link I gave you and read what CHARINDEX returns. Then think about what that means as a parameter to the RIGHT function.
I agree with @OriginalGriff - this is not a good idea.

If you don't want to go down the advised four procedures route then have 4 separate input parameters and have the caller populate the relevant parameter - but be warned, make sure the parameters have obvious names, and you are likely to get many empty datasets returned. Validation on the form of the input should be done in your UI layer
   
Comments
Member 15352290 15-Nov-21 10:15am
   
Thank you, I think both of you are suggesting right thing . I will try to implement it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900