I having an issue getting error trapping to work correctly using sp_executesql in conjunction to a xp_cmdshell script. I've been asked to error trap an exiting stored procedure (which currently uses exec xp_cmdshell and I want to replace it with sp_executesql instead) that is in production. The script that I've attached below is a generic shell that I'd like to wrap around the xp_cmdshell call in the production proc. The problem that I'm having is that although my @query parameter is using a simple generic proc (designed to fail on purpose) to simulate failure, the result returned by sp_executesql indicates that everything functioned correctly and never returns an error or value greater than 1.
Does anyway have an idea on how I can make my wrapper work using sp_executesql and catch a value other then 0, since 0 indicated that everything functioned correctly when is obivously should not?
Declare @cmd nvarchar (200)
Declare @cmd2 nvarchar(200)
Declare @query nvarchar(200)
Select @query = N'test.dbo.spProcFailsOnPurpose' -- simple select * from TableDoesnotExists
-- statement inside the proc
select @cmd = N'''sqlcmd -E -S ' + @@servername + ' -w 1000 /Q "' + @query + '"'''
Select @cmd2 = N'exec master..xp_cmdshell ' + @cmd
Declare @ExecResult int
EXEC @ExecResult = sp_executesql @cmd2
EXEC sp_executesql @cmd2
Select @ExecResult as ExecResult
IF (@ExecResult = 0) or (@@ERROR = 0)
Print 'The Result was 0 or Pass, although this should have failed'
Print 'The result was not 0 and it actually failed!!!'
RAISERROR ('Procedure Filed to run', 16, 1)
Any help would be greatly appreciated.