Formatting parameter fields

[luvtitans50]

I have a parameter @SSN varchar(11). RIght now the paramter has to be entered without dashes because that is the way the field is in the database. How can I set the parameter field up so that it will accept the value with or without dashes?

Thanks for your help!!

 

[markros]

In the beginning of the sp

if charindex(@SSN,'-') > 0
set @SSN = replace(@SSN,'-','')



PluralSight Learning Library

 

[luvtitans50]

Thanks! Just for understaning on my part, why does is it set to > 0?

Thanks for explaining this!

 

[gmmastros]

CharIndex returns the position within the string where the '-' is found. If there are no dashes, the replace command is not necessary. For strings without dashes, CharIndex will return 0.

Truth is, the IF check isn't really necessary. When replacing, if the search string is not found, replace won't modify the string anyway.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I was thinking, that may be we can use a better execution plan if we don't need to change the parameter, thus I decided to only replace in case of '-' in the field. Though, we may not need check anyway, you're right.

PluralSight Learning Library