0

From a great reply https://stackoverflow.com/a/50859647/156458

The only DBMS product which does not clearly distinguish between procedural code and "plain SQL" is SQL Server: T-SQL is an extension to the SQL language which allows you to mix "regular SQL" and procedural SQL without telling the backend that the code needs a different engine to run (which is a source of great confusion for people migrating from SQL Server to Postgres or Oracle).

I was wondering how T-SQL does not clearly distinguish between procedural code and "plain SQL"?

How does that cause confusion to PostgreSQL users?

Thanks.

Tim
  • 1
  • 141
  • 372
  • 590
  • 1
    I believe in T-SQL 'procedural code' refers to actual stored procedures. So anything after `CREATE PROCEDURE sp_myProcedure` is considered 'procedural code' – Omar Himada Jun 14 '18 at 18:52
  • 1
    `... which allows you to mix "regular SQL" and procedural SQL without telling the backend` implies that the SQL language is extended (*polluted!*) with non-sql elements. (such as: variables,assignments,conditions) And it does *not* confuse Postgres users, it confuses tsql users who switch to Postgres. – wildplasser Jun 14 '18 at 18:59

1 Answers1

2

I was referring to the fact that in SQL Server you can write: 

declare @some_variable

if exists (select ..) then 
  ... do something
else 
  ... do something else
end

... do more ..

without the need to "formally" start a block of procedural code as it is required in Postgres (using DO) or Oracle (using DECLARE). Variables can also be used in that code (again unlike Oracle and Postgres, where variables can only be used a procedural block)

That code is also allowed to run a SELECT statement without the requirement to store the result somewhere (as it is in Postgres, Oracle, DB2, and many other DBMS)

I also never said it (the separation) confuses Postgres (or Oracle) users.

I wrote "it confuses users migrating from SQL Server to Postgres (or Oracle").

For an experienced SQL Server user the clear (and sometimes annoying indeed) distinction between SQL and procedural language (PL/pgSQL or PL/SQL) is at least surprising, if not completely off-putting. I have seen people being extremely mad about that (not to say p* off) to the extent that they claimed "that such a database is unusable"