Sql stored procedures and string manipulation

Question

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 15-Nov-21 3:13am

Member 15352290

Updated 15-Nov-21 4:13am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2021-11-15T03:50:00

Solution 1

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.

Posted 15-Nov-21 3:50am

OriginalGriff

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.

CHill60 · Answer 2 · 2021-11-15T04:13:00

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