-1
line1   SELECT 'A'

line2   SELECT  CONCAT(a,b);

line3   SELECT 'B'

Trying this out in SQL Server 2008 and found that line1 will not be queried, due to the batch failing on line 2. If SQL Server is a procedural programming then line1 should've been successfully queried right?

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
SuicideSheep
  • 5,260
  • 19
  • 64
  • 117
  • 3
    SQL isn't a procedural language... It a declarative language. That's one of the base fundamental concepts of the language... Also, That should print anything if you're using SQL Server. The lack of single quotes on a & b should throw an invalid column name error. – Jason A. Long Oct 10 '17 at 03:10
  • It might help to read this: http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/ (particularly the part titled "Parsing and Compilation"). Essentially, if there are errors when parsing a statement, it will not execute anything in the statement. – ZLK Oct 10 '17 at 03:11
  • 1
    This link may be helpful: https://stackoverflow.com/questions/2668529/what-is-a-batch-and-why-is-go-used. Particularly answer from Quassnoi. – AB_87 Oct 10 '17 at 03:22
  • @sandeeprawat points out that the CONCAT() function is not supported in SQL Server 2008, it was introduced in SQL 2012 https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql – Paul Maxwell Oct 10 '17 at 03:41
  • @Used_By_Already: The point of this question is not about syntax or how to replace CONCAT in 2008 but rather the understanding of execution sequence as I've always misunderstood that if a language can support IF ELSE then it is a procedural language – SuicideSheep Oct 10 '17 at 03:45
  • @Mr.SuicideSheep - The CONCAT() function wasn't introduce into SQL Server until 2012. https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql... Prior to that all we had was **+** for concatination... **SELECT 'A' + 'B'** – Jason A. Long Oct 10 '17 at 03:50
  • I understand your question is about "procedure" and have attempted to address that below, but it is worth noting (hence a comment) that even if your procedure was correctly sequenced it would fail in SQL 2008 due to the fact CONCAT() does not exist in that version. – Paul Maxwell Oct 10 '17 at 04:03
  • What does "found that A will not be queried" mean, and how did you find that out? – Nick.Mc Oct 10 '17 at 04:25
  • 2
    I don't understand why people seem to be having trouble understanding what the question actually is. He means the line `SELECT 'A'` _does not execute at all_, even though it appears after the line that causes the error. The question is about why NO part of that batch is executed, not why the `CONCAT` in particular fails... Answers that have literally anything to do with CONCAT or why that line fails in any version of SQL are failing to address the actual question here. – ZLK Oct 10 '17 at 04:45
  • @Nick.McDermaid: Sorry for the confusion, I've updated the question using line number instead. Thanks a lot ZLK, I thought no one can understand my message – SuicideSheep Oct 10 '17 at 05:59
  • Ok that makes sense now. I'm sorry to say that my assumption was that there was some kind of copy paste error or misunderstanding about database platform. IMHO, if you are going to ask a question you need to show the output, i.e. an error message. That at least tells us where you are coming from. – Nick.Mc Oct 10 '17 at 06:24
  • I hope you don't mind that I have edited your question to make it clearer for other people like me! – Nick.Mc Oct 10 '17 at 06:25

2 Answers2

2

The reason for the observed behaviour is that you see a compile-time error, not a run-time one. Here are some examples that might shed some light on differences between them.

When database engine encounters a compile-time error, the entire batch isn't executed at all, and your code comprises a single batch. That's why you don't see any results. However, if you would separate these statements with go (in SSMS, sqlcmd or any other client that recognises it) only the second statement will be skipped.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
0
SELECT 'A'

Is valid in T-SQL, and it will quite literally display A as the result with no column header. But that query does NOT create a reference called a that holds the value of A. 

SELECT  CONCAT(a,b);

In SQL 2008 this won't work because that function simply isn't available. Note also that this row terminates with a semi-colon. Neither a nor b exist however as the only preceding line of code does not create any persistent reference.

SELECT  a + b;

In SQL 2008 this might work (if both a and b existed and were strings)

SELECT 'B'

Is valid in T-SQL, and it will quite literally display B as the result with no column header. But that query does NOT create a reference called b that holds the value of B.

in brief

  1. SELECT 'A' does not give that value of A any name to refer to later.
  2. the concatenation is in the wrong order and terminates with ;
  3. SELECT 'B' does not give that value of B any name to refer to later.

T-SQL does allow the following:

DECLARE @a AS VARCHAR(10)
DECLARE @b AS VARCHAR(10)

SET @a = 'A'
SET @B = 'B'

SELECT @a + @b
;

Another approach:

select a + b 
from (select 'A' as a, 'B' as b) as derived

Here the columns of the derived table are given an alias of a and b, which can be referenced in the outer select clause to perform the concatenation.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51