When using dynamic SQL in SQL Server 2000, how do I confirm that my SQL
statement executed successfully?  I have my statement in var @sql, and
execute it with:

EXEC @ck = sp_executesql @sql
N'[...parm defintion list and values list...]

but I think var @ck will be non-0 only if sp_executesql itself fails
(unlikely) rather than if *my* SQL statement fails.  Any thoughts?

In this case, you will have to include and OUTPUT parameter & assign the
error value to the variable and return it. You can find examples of using
OUTPUT parameters with sp_ExecuteSQL in SQL Server Books Online.

Thanks; I am using OUTPUT parameters (which aren't mentioned in BOL for
sp_executesql for some reason!).  But I'm not sure what OUTPUT parameter
I would use to store a potential error value for the entire SQL
statement.  I just had sp_executesql execute a dynamic SQL statement in
which I had misspelled a colum name.  Though the server returns a FAIL
status, I don't know how to trap for it in my code.  The variable for
the OUTPUT parameter that I was using to SELECT the value of that column
still had the valid value that it had before this failure, and my @ck
variable was good old 0, not reflecting any error value.

Any thoughts?  Thanks much.

In article <ebzWgLe1GHA.3***@TK2MSFTNGP02.phx.gbl>,
an***@bizdatasolutions.com says...
Show quoteHide quote

Here is an example with the Northwind database that shows the usage of
OUTPUT parameters with sp_ExecuteSQL:

DECLARE @OrderId INT
DECLARE @CustomerId CHAR(5)
DECLARE @SQLx NVARCHAR(300)
SET @OrderId = 10248 ;
SET @SQLx = N'
SET @CustomerId = ( SELECT CustomerID
                      FROM ORDERS
                     WHERE OrderId = @OrderId )'
EXEC sp_ExecuteSQL @SQLx, N'
        @OrderId INT,
        @CustomerId CHAR(5) OUTPUT',
        @OrderId = @OrderId,
        @CustomerId = @CustomerId OUTPUT ;
SELECT @CustomerId ;

Right, thanks very much.  I have no difficulty with using OUTPUT
parameters and am doing so currently.  I'm not clear on how they can be
used to do error-checking on my dynamic SQL statement.  In the below
case, any pre-existing value of @CustomerId will be unaffected by an
error in the SQL statement.  I can't see that any of these output parms
can be used to reflect or report any error in the statement.  Thanks for
any help.

In article <O12m32o1GHA.2***@TK2MSFTNGP06.phx.gbl>,
an***@bizdatasolutions.com says...
Show quoteHide quote

Essentially, it would be like handling errors inside a stored procedure. If
this is not a connection/batch terminating error, you can use a variable to
assign the value of @@ERROR and return it. Otherwise, you are pretty much
out of luck.

Can you post a repro, that can create an error that you expect?

Ah...THANK YOU, everyone.  I didn't quite understand that you can just
check for good old @@ERROR *after* sp_executesql, that you don't check
for errors within sp_executesql's parameter list.  Thanks much.

In article <#4ImWIr1GHA.4***@TK2MSFTNGP04.phx.gbl>,
an***@bizdatasolutions.com says...
Show quoteHide quote

Rick Charnes (rickxyz--nospam.zyxcharnes@thehartford.com) writes:
It's just like a stored procedure. You have a variable that you set to
@@error, and if the variable goes non-zero, it's time to quit.

Note that you cannot trap all errors, as some errors abort the batch.

See also my article on http://www.sommarskog.se/error-handling-II.html
There is a section sp_executesql.

getting the records returned by one query and not another
local variable not recognized when executing dynamic sql
Trigger ID
Database Id prb !
c sql server library?
How to pass a list of parameters from Select Table
URGENT - Cannot find bad data in table
NDF and file groups
Service Broker
Rollback actions of SP