Is there a short hand method of retrieving a value from a function in an insert?

Question

I have some tSQLt tests which are using magic numbers for some static data IDs and I'm trying to make them more self documenting by using a function.

Currently I'm using this but it's a bit more wordy than I would like and was hoping there was a short form method I could use without the extra brackets around the function. I know I could do this more efficiently by declaring the Ids as variables but as this is for tests my priority is more on the readability/self-documenting side.

INSERT INTO dbo.tAccessProfileAreaRight (id, AccessProfileId, AccessAreaRightId)
VALUES (1, 1, (SELECT dbo.GetAccessAreaRightId('Purchase Orders', 'Authorise'))),
       (2, 1, (SELECT dbo.GetAccessAreaRightId('Purchase Invoices', 'Authorise'))),

Answer 1

You could do an Insert Into with a Select.

INSERT INTO dbo.tAccessProfileAreaRight (id, AccessProfileId, AccessAreaRightId)
select 1,1,dbo.GetAccessAreaRightId('Purchase Orders', 'Authorise')
UNION select 2,1,dbo.GetAccessAreaRightId('Purchase Invoices', 'Authorise')
UNION select ...

Also you don't have to specify the columns for the INSERT INTO if the select columns match the table columns exactly.

INSERT INTO dbo.tAccessProfileAreaRight
select 1,1,dbo.GetAccessAreaRightId('Purchase Orders', 'Authorise')
UNION select 2,1,dbo.GetAccessAreaRightId('Purchase Invoices', 'Authorise')

Answer 2

You want your test to be more readable with is a great goal to aim at. However, your chosen way might not be optimal.

In general, a test should insert the data needed for the test in all tables accessed by the code under test. So in this case I suggest you insert a row into dbo.tAccessAreaRight and a row into dbo.tAccessProfile before inserting into dbo.tAccessProfileAreaRight.

To not be hindered by existing or potential future constraints on the tables, use tSQLt.FakeTable.

That would make your test look something like this:

CREATE PROCEDURE MyTestClass.[test ... is doing ... when ...]
AS
BEGIN
  -- Assemble
  exec tSQLt.FakeTable 'dbo.tAccessProfileAreaRight'
  exec tSQLt.FakeTable 'dbo.tAccessAreaRight'
  exec tSQLt.FakeTable 'dbo.tAccessProfile'

  INSERT INTO dbo.tAccessProfile(id) VALUES (1042); 
  INSERT INTO dbo.tAccessAreaRight(id) VALUES (5013),(5017); 

  INSERT INTO dbo.tAccessProfileAreaRight(id, AccessProfileId, AccessAreaRightId)
    VALUES(1,1042,5013),
          (1,1042,5017);
  
  --Act
  INSERT INTO #SomeTableThatYouNeedToCreateFirst
  EXEC dbo.ProcedureUnderTest @AccessProfileId=1042

  --Assert

  --do what you need to do to make sure the code behaves correctly, 
  --for example using EXEC tSQLt.AssertEqualsTable
END
GO

Because we are using tSQLt.FakeTable you do not need to worry about the columns you do not need, so in the three inserts above, just include the columns that are actually accessed by the code under test. For that same reason, you do want to explicitly list the columns, even if you end up using all columns in a table. That way, if an unrelated piece of functionality requires an additional column in the table later on, this test will be unaffected by that change.

I find this pattern leads to not only more immediately understandable tests, as for each "magic number," it is clear where it came from; you also make your test independent of an unrelated piece of code that at some point might change or just stop being maintained which could lead to random test failures, something we should strive to avoid.