TSQL with cmdshell how I can catch exception?

Question

I made this script to process batch .sql source files to define SP (as part of migration). It works fine but I can' catch any exception, when any source file is bad and given sp can not be created. I tried touse try/catch and looks like cmdshell doesn't care about result, even output for this file is different then for good files. do you know how else I can catch bad files ??

Thanks M

While @cc > @ccRun
Begin
set @ccRun = @ccRun + 1;
set @shellArg = (select 'sqlcmd -S '+ etc...+ @file....);
begin try
  EXEC xp_cmdshell @shellArg  -- to create sp
end try
begin catch                   -- try to catch bad files,??
  select 'Error_____ for ' + @file
end catch

End

Answer 1

xp_cmdshell is not very "try-catch friendly". You should try checking return value like below.

WHILE @cc > @ccRun
BEGIN
    SET @ccRun = @ccRun + 1;
    SET @shellArg = (select 'sqlcmd -S '+ etc...+ @file....);

    DECLARE @returnValue INT

    EXEC @returnValue = xp_cmdshell @shellArg

    IF @returnValue <> 0 
    BEGIN
        SELECT 'Error_____ for ' + @file
    END

END