Best practices for calling sp_executesql

I'm quite new to writing sp's. I'm making extensive use of exec sp_executesql and want to make sure that I get to know when things don't go right.

I know that sp_executesql returns zero on success, and also the @@error gets set to a non-zero value if there's a problem. Currently i'm checking both these values. Do I need to check both? Can problems occur that will cause one of them to be non-zero while the other is zero?

I just want to know what's the established way of trapping problems.

Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
check both. there could be errors with the call of sp_executesql that are not passed into @@error or vice-versa...
Einstine98Connect With a Mentor Commented:
Debugging dynamic SQL is not easy (to me is always a nightmare) ... one way to go around it is to use dynamic SQL to build stored procedures on the fly and trap the errors there... but that works in certain cases and doesn't work in many others...

Anyway, no easy way, check both outcomes...
ipendleburyAuthor Commented:
Ok, i'll keep checking both. Thanks for your input.

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Aneesh RetnakaranDatabase AdministratorCommented:
here is an example
create table test (i int )

DECLARE @err int
exec sp_executeSQL N'EXEC @err = sp_rename ''a'',''aa'' ', N'@err int output' ,@Err output

select @@ERROR Error, @Err InsideDynSqlErr

exec sp_executeSQL N'EXEC @err = sp_rename ''test'',''test1'' ', N'@err int output' ,@Err output
select @@ERROR, @Err InsideDynSqlErr
ipendleburyAuthor Commented:

I understand perfectly the exec statements, but what purpose do the Error and InsideDynSqlErr perform in the subsequent select statements?
Aneesh RetnakaranDatabase AdministratorCommented:
> insideDynSqlErr perform in the subsequent select statements?

Did you run my example,
In the first part, i was trying to rename an object which was not there .. if you run the statement inside the sp_execute, ie. sp_rename 'a','aa' ,  and check the value , it will returnn the error value > 0, i.e. the statement was a fail.
But when you run the first statement , then you wont be able to cath the error inside the SP_execute, the @@Error will retun the error value of the 'sp_execute' statement and not the statements inside the sp_executeSQl
ipendleburyAuthor Commented:
I'm sorry i'm a beginner. It's the syntax I didn't understand.

select @@Error as Error, @Err as InsideDynSqlError is what i'm used to.

I understand now. Thanks for your help.

Aneesh RetnakaranDatabase AdministratorCommented:
>select @@Error as Error, @Err as InsideDynSqlError

This is just for showing the @@Error values

Here @@Error returns the value after thesp_executeSQL call and shows as 'Error'
the otherone , the value we caught inside the sp_executeSQl statement ...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.