Prompting for values in SQL script

This is something that I forget 2 seconds after I’ve used it so now I’ll put it here as a note to self.

I have a series of TSQL scripts that I use once in different circumstances and often it’s a while between each go. In some of the I need to add some environment specific information and instead of having to scroll down through the text the query editor offers a way of prompting for parameters through a tagging of text like this:

select <Parameter name, Data type, Default value>

Before executing the script you press CTRL+SHIFT+M (this is the part I usually forget) and that makes the editor prompt you like this:

Screenshot 2019-02-19 at 13.49.43

Replace the Default value with the appropriate text and click OK. This will update your SQL script to this:

select 1234

Not the most mind-blowing thing in the world, but a great help.

 

Locate duplicate values (or the opposite) in a table

The way X++ handles SQL statement often lacks a bit compared to what you can do in standard TSQL.

One of the areas is if you want to get a set of records from a table where a specific value in a field only occurs once in the table. You could traverse through the records and compare values as you go along. That could pull down performance significantly depending on the record count. Another way of doing it is to group by the field while counting the records in e.g. RecId. Then traversing this result set and look at the value in RecId could give you either the unique or not unique value depending on what you need.

A better way would be to let the SQL server do some of the work and consequently return as few records as possible. Here is a job that illustrates this and the group by version mentioned above.

AX 2012 queries support the HAVING clause. That can in some scenarios do the same and a bit more elegant than this.

 

static void DEMO_NoDuplicates(Args _args)
{
    CustGroup custGroup;
    CustGroup custGroup2;
    PaymTermId lastPaymTermId;

    info("TRAVERSE COUNT");

    while select count(recid) from custGroup
        group by PaymTermId
    {
        if (custGroup.RecId == 1)
        {
            while select custGroup2
                where custGroup2.PaymTermId == custGroup.PaymTermId
            {
                info(custGroup2.PaymTermId);
            }
        }
    }

    info("USE JOIN");
    
    while select custGroup
        order by PaymTermId
        notexists join custGroup2
            where custGroup.RecId != custGroup2.RecId
               && custGroup.PaymTermId == custGroup2.PaymTermId
    {
        info(custGroup.PaymTermId);
    }
}


Getting SQL scripts from the wizards in the SQL Server Management Studio

Ok, this is not exactly AX but it IS in the category of related to. The thing is that you sometimes needs to take a backup of a database or other stuff on the SQL server and sometimes you need to either do this repeatedly every once in a while, have somebody else trigger it or simply document what you did.

Microsoft SQL Server Management Studio offers a lot of nice UI to handle processes like backup, restore and index manipulation. And it is so much easier to do it here than trying to write it in a query window. But it can be a pain remembering – I mean documenting – every option set and redo these settings every time. So this often forgotten feature in most of these UI elements in the Management Studio could come in handy.

After doing all the setup of e.g. the backup process you can use the Script button in the upper left-ish corner:

The script button

We are now offered 4 different ways of getting our script including short cuts for us to remember. The top three gives us the script in respectively a query window, a file or in the clipboard:

Script to query

Now you can combine multiple scripts into one if you would like to for example backup one database and restore it over another by

  1. Create the script for the backup in a query window
  2. Create the script for the restore in the clipboard and paste it in query window with the backup
  3. Do whatever you need to do with the full script to backup/restore

The 4th possibility is to get the script to be a step in a new job if you need to repeat the script on for example a daily basis. Select the Script Action to Job:

Script to job

From here you just need to set up the schedule and you are ready to go.

The script is added to Step 1 so you can manipulate it or just see what is being executed.

The script is added to step 1

Remember that firing scripts directly on the SQL-server or through other clients can be extremely powerful and dangerous and can have fatal effects on the server and databases. So whatever you do … be carefull out there …

EXISTS JOIN/NOT EXISTS JOIN versus NOT EXISTS JOIN/EXISTS JOIN

Not the prettiest headline so far. I do admit that. Nevertheless it is actually quite relevant. Here is the question to be answered: If I have multiple exists joins in my select statement and one of them is a not exists how can I be certain that I will get the expected result set back from the SQL server.

Let us start off by lining up 3 tables:

Trans

GroupId SubGroupId
A 123
B 456
C 789
D 987

 

GroupTable

GroupId
A
D
G

 

SubGroupTable

SubGroupId
123
456

 

The structure should be fairly obvious. Trans has 2 fields that refers to the sub tables. What I would like is to write a select statement that gives me all records from Trans where the GroupId isn’t present in GroupTable and where the SubGroupId is found in SubGroupTable.

This could be done like this:

select trans
    exists join subGroupTable.SubGroupId
        where subGroupTable.SubGroupId == trans.SubGroupId
            not exists join grouptable
                where grouptable.GroupId == trans.GroupId

Another way could be by turning it a bit upside down like this:

select trans
    not exists join grouptable
        where grouptable.GroupId == trans.GroupId
            exists join subGroupTable.SubGroupId
                where subGroupTable.SubGroupId == trans.SubGroupId

What happens when we look at what the SQL server receives as the TSQL-statement is a bit different than expected:

select GroupId, SubGroupId
    from Trans as t
        where EXISTS (SELECT 'x'
            FROM GroupTable as g
                where g.GroupId = t.GroupId
                    and not (exists (select 'x'
                    from SubGroupTable as s
                        where s.SubGroupId = t.SubGroupId)))

and

select GroupId, SubGroupId
    from Trans as t
        where not (EXISTS (SELECT 'x'
            FROM SubGroupTable as s
                where s.SubGroupId = t.SubGroupId
                    and (exists (select 'x'
                    from GroupTable as g
                        where g.GroupId = t.GroupId))))

 

What to notice is that in the first statement the EXISTS and NOT EXISTS are considered as one and in the second statement we now have 2 EXISTS that is   encapsulated by a NOT giving it a bit of a twist compared to what we might have expected.

The result set for the first statement is

D 987

since

A is existing in SubGroupTable which was ruled out by our NOT EXISTS

B is in SubGroupTable and not in GroupTable which both goes against our EXISTS/NOT EXISTS

C is in SubGroupTable A was

D is present in GroupTable and not in SubGroupTable

 

and for the second the result set is

B 456

C 789

D 987

since

A is in both GroupTable and SubGroupTable and we specified by having two EXISTS’s and adding these to a NOT that this was a no go.

B is returned although being in SubGroupTable it was not in GroupTable making the double EXISTS false and thereby pleasing the NOT

C is not present in neither GroupTable nor SubGroupTable and therefore matching the NOT

D is – like B – returned since it only exists in GroupTable and not i SubGroupTable and the EXISTS’s then returning false

 

I realise that this can be a bit tricky to get the head around but nevertheless very important to keep in mind when doing those large selects with lots of tables and EXISTS/NOT EXISTS joins.