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