Execute a statement : EXECUTE « Store Procedure Function « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Store Procedure Function
3. EXECUTE

Execute a statement

1>
2>
3> -- Replace the default error message and numbers with my own:
4>
5> CREATE PROCEDURE spRunSQL
6>     @Statement VarChar(2000) -- Input param. accepts any SQL statement.
7> AS
8>     DECLARE   @StartTime DateTime
9>             , @EndTime DateTime
10>             , @ExecutionTime Int
11>             , @ErrNum Int
12>     SET @StartTime = GetDate()
13>     EXECUTE (@Statement)
14>     SET @ErrNum = @@Error
15>     IF @ErrNum = 207      -- Bad column
16>        RAISERROR 50001 'Bad column name'
17>     ELSE IF @ErrNum = 208     -- Bad object
18>        RAISERROR 50002 'Bad object name'
19>     ELSE IF @ErrNum = 0   -- No error. Resume.
20>        BEGIN
21>         SET @EndTime = GetDate()
22>         SET @ExecutionTime = DateDiff(MilliSecond, @StartTime, @EndTime)
23>         RETURN @ExecutionTime -- Return execution time in milliseconds
24>       END
25> GO
1>
2> EXEC spRunSQL 'select 1 GO'
3> GO
GO
-----------
          1
1>
2> EXEC spRunSQL 'selet 1 GO'
3> GO
Msg 102, Level 15, State 1, Server JAVA2S\SQLEXPRESS, Line 1
Incorrect syntax near 'GO'.
1>
2>
3> drop procedure spRunSQL
4> GO
1>
2>

           
       

Related examples in the same category