capture error messages from dynamic tsql

How does one go about capturing error messages for a procedure that executes
dynamic sql...
for example ten lines out of 1000 produce an error... can the error be
captured and returned to the user?
--
Regards,
JamieI'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
USE tempdb
CREATE TABLE t(c1 int CHECK (c1 < 10))
GO
CREATE PROC p AS
EXEC('INSERT INTO t (c1) VALUES (20)')
GO
--Verify error
EXEC p
GO
--Capture using TRY and CATCH
BEGIN TRY
EXEC p
END TRY
BEGIN CATCH
DECLARE @.errStr nvarchar(4000)
SET @.errStr = ERROR_MESSAGE()
PRINT @.errStr
END CATCH
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> How does one go about capturing error messages for a procedure that executes
> dynamic sql...
> for example ten lines out of 1000 produce an error... can the error be
> captured and returned to the user?
> --
> Regards,
> Jamie|||I should have added we are still using SQL 2000. Tested this in 2005 and it
works great. Thanks.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
> USE tempdb
> CREATE TABLE t(c1 int CHECK (c1 < 10))
> GO
> CREATE PROC p AS
> EXEC('INSERT INTO t (c1) VALUES (20)')
> GO
> --Verify error
> EXEC p
> GO
> --Capture using TRY and CATCH
> BEGIN TRY
> EXEC p
> END TRY
> BEGIN CATCH
> DECLARE @.errStr nvarchar(4000)
> SET @.errStr = ERROR_MESSAGE()
> PRINT @.errStr
> END CATCH
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> > How does one go about capturing error messages for a procedure that executes
> > dynamic sql...
> > for example ten lines out of 1000 produce an error... can the error be
> > captured and returned to the user?
> > --
> > Regards,
> > Jamie
>
>