Now, we want to convert the SELECT results into an multi-values INSERT script for a temporary table. Specify grid output, execute the newly-fattened query. OUTER APPLY TrimLeadingZeros ( RandomNumberWithLeadingZeros )ĭoesn’t look much does it? Now, with the power of cut ‘n paste, a few seconds later we have fifty rows, which should be sufficient. Listing 1 is a routine that removes leading zeros from a string. Basic testing for a function to trim leading zeros It allows you to refactor the results of a query into a INSERT INTO…VALUES statement that save the results into a temporary table. Testing all your SQL queries and routines in this way can take time to do properly, but there is a very handy feature in SQL Prompt called “ Script as Insert” that considerably lightens the workload. It is often a life-saver when working on performance improvements.
for the routine This isn’t just to prevent you from making any obvious mistakes, as you develop the routine, but also to prevent anyone else from subsequently altering it when they are ‘feeling lucky’, without doing manual unit tests.įrom my experience, it has saved me from embarrassment, and caught out a few colleagues who sought to improve my work, without considering the unpleasant side-effects. The best way to do this is to define the tests in a batch that is attached to the CREATE or ALTER script. When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected.
He is a regular contributor to Simple Talk and SQLServerCentral. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.ĭespite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.