User must input 'X' number of characters - SPROC parameter

Question

User must input 'X' number of characters - SPROC parameter

nbaer

Old Hand

Points: 348
More actions
June 1, 2017 at 9:40 am

#326222

I have a stored procedure with a parameter. I would like to force the input to be at least five characters. Is there a way to accomplish this in my query? Is this something that must be defined in the variable of the query?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

As always, it depends on the implementation.
if it is just an SSMS consumption,
at the procedure level, all you can do is add an error;
IF LEN(@param) < 5 BEGIN RAISERROR('Parameter must be at least five characters',16,1) return; END

if the procedure is consumed by an application, or an SSRS report, etc, the app should validate the parameter prior to the call to the procedure,and be designed to handle any errors that might be returned.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nbaer Old Hand Points: 348 More actions · Answer 2

My next step will be to deploy this as a dataset in SSRS. If I create the "constraint" and error in my SPROC, will SSRS recognize the error?

Is there a way to implement a min character length in SSRS, or should I post that question to the RS forum?

Lowell SSC Guru Points: 323518 More actions · Answer 3

yes, SSRS can use an expression to validate the length of your input;
this stackoverflow has an example that was testing a credit card length = 16, basically the same thing you are after.
https://stackoverflow.com/questions/18189836/ssrs-validate-user-input-length

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nbaer Old Hand Points: 348 More actions · Answer 4

Thanks! I swear I googled this several times looking for an answer. I guess my combination of words was off.