Language which compiles to t-sql

Question

I am writing a lot of t-sql at my job and frequently I wish there would be some type of extensions available (for example something like TypeScript is for Javascript - you can create classes and all this OO stuff and it just compiles to standard Javascript).

A quick example what I would want to have (the semantic should be improved off course):

-- Input:
def $t sysname
$t = 'Table1'
select * from $t
-- Output:
select * from Table1

-- Input (issues: remove last comma, newline characters):
def $t sysname
set $t = 'Table1'
;with Units as (select distinct Unit from $t),
[repeat @i; i=1; i<4]S$i as (select Unit, count(*) C from $t where T = $i group by Unit),[endrepeat]
select distinct [repeat (@i; i=1; i<4)]isnull(S$i.C, 0) C$i,[endrepeat]
from Units u
    [repeat @i; i=1; i<4]left join S$i on (u.Unit = S$i.Unit)[endrepeat]
group by [repeat @i; i=1; i<4]S$i.C,[endrepeat]
order by [repeat $i; i=4; i>0]S$i.C desc,[endrepeat]
-- Output:
;with Units as (select distinct Unit from Table1),
S1 as (select Unit, count(*) C from Table1 where T = 1 group by Unit),
S2 as (select Unit, count(*) C from Table1 where T = 2 group by Unit),
S3 as (select Unit, count(*) C from Table1 where T = 3 group by Unit)
select distinct isnull(S1.C, 0) C1, isnull(S2.C, 0) C2, isnull(S3.C, 0) C3
from Units u
    left join S1 on (u.Unit = S1.Unit)
    left join S2 on (u.Unit = S2.Unit)
    left join S3 on (u.Unit = S3.Unit)
group by S1.C, S2.C, S3.C
order by S3.C desc, S2.C desc, S1.C desc

-- Input (sq = singlequotes):
declare @sql varchar(max), @year int
set @year = 2012
set @sql = [sq]
select JobId, InventoryItem
from openquery(MyLinkedServer, '
    select JobId, InventoryItem
    from Jobs
    where year(Created) = {@year}
        and Type <> {TypeA}
') x
[endsq]
exec @sql
-- Output:
declare @sql varchar(max)
declare @year int
set @year = 2012
set @sql = '
    select  JobID, InventoryItem
    from    openquery (MyLinkedServer, ''
            select  JobID, InventoryItem
            from    XXX.Jobs
            where   year(Created) = ' + convert(varchar, @year) + '
                    and Type <> ''''TypeA''''
            order by "Created" desc
    '') x
'
exec @sql

Looks like I would need to write my own DSL to implement this, but never used it before, so any ideas are more then welcome. Would Visual Studio Visualization and Modeling SDK (VMSDK) be the right choice for this kind of project? How complex would it become? :)

Edit - ORM was suggested, but this would be run from SSMS directly, not from a language like c# or java... I mean, there would be no objects and stuff, just queries. I removed c# tag because it was misleading. The idea is more in the direction of a SSMS plugin.

Answer 1

Perhaps the BQL Specification fits the bill? (see the link for spec)

There is CoffeeScript, Dart, Typescript, etc for JavaScript. There is LESS and SCSS for CSS.

Why not something for SQL?

So I played the devil's advocate and said: "What if SQL was a compile target?"

In doing so, I found myself wanting a language that:

• Strict superset of SQL. This is important because it promotes a clean transition. One can move migrate their SQL codebase incrementally since all valid SQL is also valid in this language.
• Promotes good and efficient SQL practices
• Promotes keeping SQL code DRY
• Transpiles into easily readable SQL

http://tech.pro/blog/1917/a-better-query-language-bql-language-specification

Answer 2

Have you looked at linqpad.you can write a Linq query and see the generated sql

Its not a SSMS plugin but an Application of its own just like SSMS.

LinqPad