none
Using expression for a stored procedure argument = syntax error

    Вопрос

  • create procedure dbo.sprocTest(@x int)
    as
    begin
      print @x
    end
    go
    
    exec dbo.sprocTest 1+1

    This results in:
    Incorrect syntax near '1'.
    I've also seen: "near +".

    I know the workaround is to assign the expression to a variable, then use the variable to call the sproc, but I am trying to understand WHY this does not work. Certainly this would work in most procedural programming languages (which T-SQL isn't): the expression is evaluated before the result is used to call the function.


    -Tom. Microsoft Access MVP

    8 июня 2012 г. 2:58

Ответы

  • It's very simple to prove that T-SQL compiles in at least two passes.

    It's also very simple to see that permitting expressions as parameters to stored procedures would be possible. If you can use expressions with PRINT, why not with RAISERROR.

    It's simply a matter of not having come around to it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:54
    10 июня 2012 г. 10:02
  • I think the symbol table is still built in one pass. What I cannot figure out is why we need a ; in front of WITH or in a few other places. It implies that the BNF is not fully ANSI.


    Having written enough code that parses T-SQL, I can answer that one. Some keywords are statement leaders. That is when you see a certain keyword, you know that you may have a new statement.

    WITH is a reserved word that is used in multiple places, and it would probably be hariy to sort out when WITH is part of the statement or a new statement.

    Other statements are simply not reserved words. Both these batches are legal in SQL 2012, but they give diferent results

    BEGIN TRY
       SELECT 1/0
    END TRY  
    BEGIN CATCH
       SELECT error_message()
       THROW
    END CATCH

    BEGIN TRY
       SELECT 1/0
    END TRY  
    BEGIN CATCH
       SELECT error_message()
       ; THROW
    END CATCH

    The original design by Sybase to not use semicolons was of course a major maldesign. I am not sure that Microsoft have made it better with making semicolons mandatory in some places. I would have preferred THROW to be a reserved keyword.

    Microsoft has deprecated semicolon-less code, but I can't see that they will ever be able to complete that deprecation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:54
    10 июня 2012 г. 18:55

Все ответы

  • So why is the sky blue?

    It just one of these things that Microsoft has never come around to enhance. Of course, a general expression with subqueries and all could be hairy, but why not simple expressions?

    Anyway, the place to push for this, as you surely know, is http://connect.microsoft.com/sqlserver/feedback.

    I have not checked, but there must already be a request for this.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    8 июня 2012 г. 7:24
  • By the way, ironically you can have function calls in the parameter list. Not calls to any function, but only to the functions that start with @@, and which originally were known as global variables. Of course, this support exists for compatibility reasons, but there obviously an embryo to handle expressions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Предложено в качестве ответа Hasham NiazEditor 8 июня 2012 г. 7:47
    8 июня 2012 г. 7:35
  • The blue color of the sky is due to Rayleigh scattering. We disagree if my question falls in the same category.

    -Tom. Microsoft Access MVP

    9 июня 2012 г. 20:47
  • T-SQL is a very simple one-pass compiler. That is why you have those filly @, @@, #  and ## metadata prefixes to get around the weak symbol table. It cannot build a thunk (actual compiler term). This is why you do not write long programs in t_SQL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    10 июня 2012 г. 0:17
  • It's very simple to prove that T-SQL compiles in at least two passes.

    It's also very simple to see that permitting expressions as parameters to stored procedures would be possible. If you can use expressions with PRINT, why not with RAISERROR.

    It's simply a matter of not having come around to it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:54
    10 июня 2012 г. 10:02
  • I think the symbol table is still built in one pass. What I cannot figure out is why we need a ; in front of WITH or in a few other places. It implies that the BNF is not fully ANSI.

    I have always used the semi-colon so it is not a problem for me. Steve Feltz on X3H2 used to check the syntax to make sure we were an LALR1 grammar. 

    I am still holding out for full version of SQL/PSM :)


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    10 июня 2012 г. 13:44
  • I think the symbol table is still built in one pass. What I cannot figure out is why we need a ; in front of WITH or in a few other places. It implies that the BNF is not fully ANSI.


    Having written enough code that parses T-SQL, I can answer that one. Some keywords are statement leaders. That is when you see a certain keyword, you know that you may have a new statement.

    WITH is a reserved word that is used in multiple places, and it would probably be hariy to sort out when WITH is part of the statement or a new statement.

    Other statements are simply not reserved words. Both these batches are legal in SQL 2012, but they give diferent results

    BEGIN TRY
       SELECT 1/0
    END TRY  
    BEGIN CATCH
       SELECT error_message()
       THROW
    END CATCH

    BEGIN TRY
       SELECT 1/0
    END TRY  
    BEGIN CATCH
       SELECT error_message()
       ; THROW
    END CATCH

    The original design by Sybase to not use semicolons was of course a major maldesign. I am not sure that Microsoft have made it better with making semicolons mandatory in some places. I would have preferred THROW to be a reserved keyword.

    Microsoft has deprecated semicolon-less code, but I can't see that they will ever be able to complete that deprecation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:54
    10 июня 2012 г. 18:55