VBA Rubberducking

7:07 PM

something like @this:

	BEGIN TRY
		SET @result = CAST(@workSSN as int)
	END TRY
	BEGIN CATCH
		SET @result = 0;
		THROW 50042, CONCAT(N'Non numeric SSN provided: ', @workSSN)
		RETURN (-1)
	END CATCH

the RETURN (-1) is superfluous though, isn't it?

this

well I do put it in though, and also at end, too.

FreeMan

Never gets there because of the THROW

this

yep

but fwiw....

FreeMan

just for completeness/OCD sake

this

BEGIN TRY
   --do work

   IF myCondition = 0
   BEGIN
     SET @errMessage = N'derp';
     THROW 50001, @errMessage, 1;
  END;

   IF myOtherCondition = 0
   BEGIN
     SET @errMessage = N'duh';
     THROW 50002, @errMessage, 1;
  END;

END TRY BEGIN CATCH
   SET @errNumber = ERROR_NUMBER();
   THROW;
   RETURN @errNumber;
END CATCH;

RETURN 0;

note: aircode. Caveat emptor

also, note the use of semicolons. ;)

FreeMan

7:15 PM

what does the unparameterd THROW at the end do?, just returns whatever err number caused it to end up in the CATCH?

this

yeah

FreeMan

yeah... not good with remembering the semi-colon

23fc9a62-56de-47fb-97b4-737890

Rethrows the same error, likely.

this

that only works in the CATCH block, though.

23fc9a62-56de-47fb-97b4-737890

Note that if there's a stack trace, it probably doesn't reset that either.

this

7:16 PM

Note this:

> The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

23fc9a62-56de-47fb-97b4-737890

Or whatever SQL does with throws.

this

docs.microsoft.com/en-us/sql/t-sql/language-elements/…

FreeMan

Your code is effectively what I've done, I just didn't wrap the whole thing in a TRY...CATCH (yet. :)

this

so this will be .... surprising:

FreeMan

(is it not?)

this

7:17 PM

BEGIN
  THROW 50001, N'doesn't throw....', 1
END

this is a syntax error. Yay!

@FreeMan I was concerned you were using it for each statement. Mine shows you only need only one TRY/CATCH block

FreeMan

just using it to validate that I've got only numbers in the SSN field at that point.

this

ok. just don't want to do it the SQL Server 2000 way. :)

FreeMan

12345678a isn't really a valid SSN anywhere I'm familiar with...

not sure what that way is, but it sounds bad.

this

OTOH, I usually have my guard clauses return before I enter the TRY block furhter down.

basically storing the @@ERROR into a local variable, checking that it's 0 before proceeding further. SO MUCH FUN.

Mathieu Guindon

@this BEGIN; <~ wtf

this

7:23 PM

@this but sometimes I end up needing guard clauses inside since it can only be validated after some DML statement has executed. the TRY basically should contain all the DML statements, obviously and I usually have a transaction if it's crucial DML.

@MathieuGuindon IKR? Such is the joy of T-SQL.

that's why I "work around" it by setting a local variable even if I actually don't need it:

BEGIN
  SET @errMessage = "derp';
  THROW 50001, @errMessage, 1;
END;

T-SQL's consistency is right up there with VBA and JS, unfortunately.

FreeMan

tSQL, VBA, both from MS, so their inconsistency is consistent...

@this ahhhh! that makes so much more sense

Mathieu Guindon

apparently begin is an instruction that warrants a semicolon. I ...I just can't.

@this better than ;throw

this

@MathieuGuindon Neither can I. it's just mind-bending.

But the parser is kind of dumb - BEGIN THROW get interpreted as a marker for transaction scope because THROW wasn't originally a reserved keyword. :\

@MathieuGuindon unfortunately I see a number of people electing this options, esp. with CTEs..... #ContributingToProblem

FreeMan

7:40 PM

Guess it would make sense to extract the SSN cleanup/validate procedure to its own stored proc, then it could be tested on its own (pass a variety of potential SSNs, make sure I get the right thing back), then used to validate the SSN I'm using in other stored procs.

(clean-up includes removing -, ., ` ` from it so I've got just digits left)

is there an easy way to REPLACE(@mySSN, [A-Z,a-z], '') ?

this

hm, if it has alpha, it's not a SSN?

FreeMan

not as far as I know!

this

so why are you stripping alpha?

FreeMan

3 hours ago, by Hosch250

Social Security number

In the United States, a Social Security number (SSN) is a nine-digit number issued to U.S. citizens, permanent residents, and temporary (working) residents under section 205(c)(2) of the Social Security Act, codified as 42 U.S.C. § 405(c)(2). The number is issued to an individual by the Social Security Administration, an independent agency of the United States government. Although its primary purpose is to track individuals for Social Security purposes, the Social Security number has become a de facto national identification number for taxation and other purposes.A Social Security number may be...

@this so if I get 123-45-678a I can spit it out as an invalid SSN instead of looking for a person with that "number"

this

but you're doing REPLACE(..., '[A-z]', '')

why not: IF NOT @ssn LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' .... ?

(not sure if the - needs to be escaped)

FreeMan

7:47 PM

A) Can I do that? B) Once I've replaced any letters, it will fail the LEN(@mySSN) = 9 test

this

you don't even need the LEN test....

Vogel612

@this no way to get \d{3}-\d{2}-\d{4}?

this

it must conform to the format of xxx-xx-xxxx

hmm. that's regex, isn't it?

LIKE isn't regex

Vogel612

that's why I'm asking ...

FreeMan

most of the files I get don't have dashes, so I'm not storing with them, but the regex description is otherwise correct

this

7:48 PM

docs.microsoft.com/en-us/sql/t-sql/language-elements/…

in that case, you can handle both dash format and non-dash format

but they must match one of either.

otherwise you have a no-go

FreeMan

Yeah, except sometimes I use a . separator because it's easier to type on the Num keys than a - (and I can be a jerk to the programmers). Don't think I'd ever need to manually input SSNs to search, but, if I do...

picture forming...

this

fwiw, i usually prefer to strip away all formatting

always easiest to store only the raw data in database. you can then format it anyway you please.

FreeMan

yup

whew!

	DECLARE @workSSN nvarchar(11);
	SELECT @workSSN = REPLACE(@searchSSN, '-', '');
	SELECT @workSSN = REPLACE(@workSSN, '.', '');
	SELECT @workSSN = REPLACE(@workSSN, ' ', '');
	SELECT @workSSN = REPLACE(@workSSN, '[A-Z,a-z]', '');
	SELECT @workSSN = TRIM(@workSSN);

	IF @workSSN NOT LIKE('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
	BEGIN
		SET @result = 0;
		SET @errMessage = CONCAT(N'Incorrect SSN format provided: ', @workSSN);
		THROW 50042, @errMessage, 1;
		RETURN (-1);
	END
	SET @workSSN = CAST(@workSSN as int);

Sadly, SELECT @workSSN = REPLACE(@workSSN, '[A-Z,a-z,[-],[.],[ ]]', ''); doesn't work.

this

8:04 PM

confused you're still stripping alpha and whatever....

FreeMan

since I need to work with a 9-digit SSN, it only makes sense to strip the dashes. Guess it's not necessary to strip letters since it won't match the [0-9]^9 pattern...

but... I'm just validating that I've got an otherwise all numeric SSN... so stripping those characters at this point would be side affecting if I was actually returning the edited SSN, so nevermind...

blarg

this

I would only strip the - and maybe . and ` `, that's it.

if it has anything else and doesn't meet hte format of 9 digits, you lose, thanks for playing!

FreeMan

Yeah, but this isn't... well, actually it is... returning the stripped SSN. Actually it isn't but it easily could and probably should...

CREATE PROCEDURE [dbo].[CleanUpSSN] (
	  @searchSSN nvarchar(11),
	  @validSSN int out)

AS
BEGIN

	DECLARE @workSSN nvarchar(11);
	SELECT @workSSN = REPLACE(@searchSSN, '-', '');
	SELECT @workSSN = REPLACE(@workSSN, '.', '');
	SELECT @workSSN = REPLACE(@workSSN, ' ', '');
	SELECT @workSSN = TRIM(@workSSN);

	IF @workSSN NOT LIKE('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
	BEGIN
		DECLARE @errMessage nvarchar(255);
		SET @errMessage = CONCAT(N'Incorrect SSN format provided: ', @searchSSN);

Actually, @validSSN should be @validatedSSN #namingishard

and @searchSSN should be something like @inputSSN

this

8:20 PM

ok one more question. Why isn't this a scalar function?

i see no reason for it to not be one.

FreeMan

@this do you use the incredibly painfully long tSQLt naming convention: CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate] or do you suck all the spaces out of that [] phrase?

ummm... cause I didn't think of it.

if I go with a scalar function, I either get a @validatedSSN or an error, right?

CREATE OR ALTER FUNCTION [dbo].[CleanUpSSN] (
	  @inputSSN nvarchar(50))
RETURNS nvarchar(9)
AS
BEGIN

	DECLARE @workSSN nvarchar(50);
	SELECT @workSSN = REPLACE(@inputSSN, '-', '');
	SELECT @workSSN = REPLACE(@workSSN, '.', '');
	SELECT @workSSN = REPLACE(@workSSN, ' ', '');
	SELECT @workSSN = TRIM(@workSSN);

	IF @workSSN NOT LIKE('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
		RETURN (-1);

	RETURN @workSSN;

END

allow nvarchar(50) in order to return -1 if I pass in 123-45-6789a.

Though... declaring it as nvarchar(11) nicely takes care of that automatically...

but, I don't think that's the way I want to handle it.

OK, lots of SELECT dbo.CleanUpSSN '<something.' tests run, now to write 'em up formally in tSQLt.

Thanks again for the patience, @this!

Yay!!!

(0 rows affected)
[testFindBySSN].[testSsnTooLong] failed: (Failure) Expected actual value to not equal <123456789>.

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                     |Dur(ms)|Result |
+--+-----------------------------------+-------+-------+
|1 |[testFindBySSN].[testSsnWithDash]  |      0|Success|
|2 |[testFindBySSN].[testSsnWithLetter]|      0|Success|
|3 |[testFindBySSN].[testSsnWithPeriod]|      0|Success|
|4 |[testFindBySSN].[testSsnWithSpace] |      0|Success|

(yeah, I know one failed... I'm excited that I've got some tests setup and they ran!)

Now to start doing this in mah VBA code!!!

this

8:44 PM

nice job.

just as a reminder - they like to suggest that you write failing test first

but TBH I'm not always good at that because sometimes I find the design too much in flux so it's not easy to write a failing test before there's an implementation

but the reasoning is to prove that the test itself is working

@FreeMan Yes. One more thing - you always should have a WITH SCHEMABINDING on your scalar function. That helps the optimizer know that there is in fact no data access for the scalar function and do a better job for optimizing the calls to the function.

Actually, I wouldn't do RETURN (-1); but rather RETURN NULL; if you prefer to not throw an error. If you want it to throw an error, I think you can do a THROW right there in the function, with the message. Your VBA code then needs error handling.

FreeMan

I tried THROW in the Function and it didn't like that.

this

whether it's appropriate to return NULL for non-valid values is a subject of debate.

FreeMan

Guess I could change it to return NULL

this

Ok, yeah, functions has some rules preventing it from having side effects.

so I'd have used NULL, yeah.

FreeMan

makes sense to @validatedSSN = CleanUpSSN ... IF @validatedSSN IS NOT NULL ...

tomorrow.

TTQW on a high note!!

@this BTW - I'm patting myself on the back for writing a test!!! never mind what "they" say! :D

this

8:54 PM

agreed!

FreeMan

having an @IvenBach™ moment here...

2

KySoto

9:05 PM

^+1

Vogel612

9:18 PM

Sigh.... lovely working conditioms today as well...

I opened three tabs while building the app and now my PC is locked up since 5 minutes

Whatever...

KySoto

9:34 PM

sounds like a FTS moment

in other news... its going to be 107°F on sat, and 110°F on sunday in my town

<<is the sad

Vogel612

@KySoto yea, it's also close enough to midnight to really say that

KySoto

oof

Vogel612

I only started working on it at 8PM, soo ...

I'm really shifted around with my sleep cycle rn...

KySoto

yeah, sounds.. uhh

not the best

well, if you wanna be able to do stuff when business' are open

Vogel612

oh it's okay, so long as I'm still a student

and I usually wake up around noon

KySoto

9:40 PM

i was a night owl too

Duga

10:58 PM

[rubberduck-vba/Rubberduck] naming1086 starred us

4

Duga

11:16 PM

[rubberduck-vba/Rubberduck] Vogel612 commented on Rubberduck.Core/UI/UnitTesting/TestExplorerViewModel.cs of pull request #5064: Add menu item to Test Explorer context menu allowing Ignore/Unignore of unit test

Quack Exchange

11:33 PM

0

Q: "String Interpolation in {language}", "VBA"

"This is {0} cool!", "freaking" I've always wanted an easy and intuitive way to inject variables into a string. So after about 10 variations, I finally came up with this function. How it works The concept is that I can find every pattern such as {key} or {0} or whatever {taco} and get a uniq...

Transcript for