How to judge whether an SQL statement is a query or execution

xiao zhongshuai 176 Reputation points
2021-09-10T09:03:20.15+00:00

How to judge whether an SQL statement is SELECT or UPDATE/DELETE/...?

How to develop a tool similar to SSMS?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114K Reputation points
    2021-09-10T09:56:55.013+00:00

    For an example of SSMS alternative, see https://github.com/Microsoft/azuredatastudio.

    In order to determine if a script return data like a SELECT query, I think that you can try something like this:

    declare @text as nvarchar(max) = 'declare @x int = 123; select @x'
    select * from sys.dm_exec_describe_first_result_set( @text, null, 0)
    

    If this special function returns rows, then the checked script returns data.

    In case of DELETE, for example, nothing is returned:

    set @text = 'delete from Users'
    select * from sys.dm_exec_describe_first_result_set( @text, null, 0)
    

    However, an SQL script could include several kinds of statements, calls of stored procedures, etc.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,576 Reputation points
    2021-09-10T09:14:46.083+00:00

    Hi @xiao zhongshuai ,

    Simply put, SELECT/UPDATE/DELETE statements start with SELECT/UPDATE/DELETE respectively. You can intuitively determine whether a piece of code is a SELECT, UPDATE, or DELETE statement.
    For example, the following code is a SELECT statement:

        SELECT FirstName, LastName, StartDate AS FirstDay    
        FROM DimEmployee     
        WHERE EndDate IS NOT NULL     
        AND MaritalStatus = 'M'    
        ORDER BY LastName;  
    

    UPDATE statement:

    UPDATE dbo.Table1     
        SET c2 = c2 + d2     
        FROM dbo.Table2     
        WHERE CURRENT OF abc;    
        GO    
        SELECT c1, c2 FROM dbo.Table1;    
        GO    
    

    DELETE statement:

        DELETE Production.ProductCostHistory    
        WHERE StandardCost BETWEEN 12.00 AND 14.00    
              AND EndDate IS NULL;    
        PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));  
    

    Sorry,I don’t know much about development tools. You can find professional forums for help.

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments