Error Message

[maverick786us]

I've got a procedure like this..


CREATE PROC dbWBS_sp_ExecuteQuery
(
@QueryString VARCHAR(8192),
@ErrMsg VARCHAR(512) OUTPUT
)
AS
BEGIN
EXEC @QueryString
END


Now if there is any error in the querystring I want to store that error message in this variable @ErrMsg. How can I accompalish this??

Thanks in Advance

[andreasblixt]

SQL Server 2000 or 2005?

[maverick786us]

SQL Server 2000 or 2005?
2005

[andreasblixt]

Try this:
CREATE PROCEDURE uspTryExecute
@QueryString nvarchar(max),
@Error nvarchar(2048) = NULL OUTPUT
AS
BEGIN TRY
EXECUTE(@QueryString);
END TRY
BEGIN CATCH
SET @Error = ERROR_MESSAGE();
END CATCH;
GO

DECLARE @Message nvarchar(2048);
EXEC uspTryExecute N'SELECT a, b, c FROM NonExistentTable;', @Message OUT;
SELECT @Message;
GO