Post reply

Using sp_ExecuteSQL

  • March 15, 2012 at 7:27 am

    #252628

    Hello,

    If I create some dynamic SQL and execute it using sp_ExecuteSQL, and the SQL I created generates an error message, is there any way I can trap this?

    Thanks.

  • March 15, 2012 at 7:32 am

    #1459834

    Use Output parameter:

    DECLARE @SQL NVARCHAR(4000);

    DECLARE @ParameterDefinition NVARCHAR(4000);

    -- Set value for Parameter variable

    SELECT@ParameterDefinition = '

    @top INT,

    @acctnum INT,

    @ErrorOutput INT OUTPUT,

    @ROWCOUNTOutput INT OUTPUT'

    -- Set value for the Sql String

    SELECT @SQL = 'Select top (@top) * from acct where acctnum > @acctnum; SELECT @ErrorOutput = @@Error,@ROWCOUNTOutput = @@ROWCOUNT'

    EXEC sp_executeSQL

    @SQL,

    @ParameterDefinition,

    @top = 10,

    @acctnum = 400975,

    @ErrorOutput = 0 ,

    @ROWCOUNTOutput = 0

  • March 15, 2012 at 7:38 am

    #1459844

    Use TRY.. CATCH

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 15, 2012 at 7:56 am

    #1459858

    Great stuff chaps

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply