Error - help

Hi,
Something is wrong with this:
CREATE PROCEDURE Admin_LogError
AS
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
INSERT INTO Admin_Errors
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
END
Error:
Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
The name "ErrorNumber" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.This is not how TSQL work. You first do a SELECT of what the function returns, returning that as a
result set to the client, then you refer to just the word ERRORNUMBER in the insert statement, where
that word has no correlation to the prior SELECT. Skip the SELECT, hand do the INSERT like:
INSERT INTO tblname(col1, col2, col3...) --Always specify column names
VALUES(ERRORNUMBER(), ERRORSEVERITY(), ...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Your error is here:
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
Hint: that should be ERROR_NUMBER() not ErrorNumber
The select statement does not assign values, it selects them.
If you need to store the error information so that you can use it later you
will need to do things a bit differently within your stored procedure
DECLARE @.errNum int
SELECT @.errNum = ERROR_NUMBER()
Now you can use @.errNum at other parts of the stored procedure.
--
Keith Kratochvil
<tootsuite@.gmail.com> wrote in message
news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Thanks all - this was an oversight on my part - I borrowed this code
from somewhere else - wrong context
Keith Kratochvil wrote:
> Your error is here:
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> Hint: that should be ERROR_NUMBER() not ErrorNumber
> The select statement does not assign values, it selects them.
> If you need to store the error information so that you can use it later you
> will need to do things a bit differently within your stored procedure
> DECLARE @.errNum int
> SELECT @.errNum = ERROR_NUMBER()
> Now you can use @.errNum at other parts of the stored procedure.
> --
> Keith Kratochvil
>
> <tootsuite@.gmail.com> wrote in message
> news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> > Hi,
> >
> > Something is wrong with this:
> >
> > CREATE PROCEDURE Admin_LogError
> > AS
> > BEGIN
> >
> > SELECT
> > ERROR_NUMBER() AS ErrorNumber,
> > ERROR_SEVERITY() AS ErrorSeverity,
> > ERROR_STATE() as ErrorState,
> > ERROR_PROCEDURE() as ErrorProcedure,
> > ERROR_LINE() as ErrorLine,
> > ERROR_MESSAGE() as ErrorMessage;
> >
> > INSERT INTO Admin_Errors
> > VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> > ErrorLine, ErrorMessage, GETDATE())
> >
> > END
> >
> > Error:
> >
> > Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> > The name "ErrorNumber" is not permitted in this context. Valid
> > expressions are constants, constant expressions, and (in some contexts)
> > variables. Column names are not permitted.
> >