How to raise errors
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