MSSQL Sorgu , sunucu kapalıyken hata dönmesi

sorgu sonucu MSSQL Sunucu kapalı olsa bile sp hata fırlatmayıp boş dönebilecek şekilde tasarlanmıştır.



CREATE TABLE #Results0 (Name NVARCHAR(max),Status NVARCHAR(max))

INSERT #Results0

EXECUTE Deneme



CREATE TABLE #Results0_1 (Name NVARCHAR(max),Status NVARCHAR(max))

INSERT #Results0_1

EXECUTE Deneme2



SELECT * FROM #Results0 UNION all SELECT * FROM #Results0_1

DROP TABLE #Results0

Örnek kod blogu 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[_sp_Deneme ] AS

DECLARE @ExecQuery nVARCHAR (max)=null;

begin  -- Diyarbakır

CREATE TABLE #Baymak_Diyarbakir (CITY NVARCHAR(max),DISTRICT NVARCHAR(max),STREET NVARCHAR(max),REGION NVARCHAR(max),COUNTRY NVARCHAR(max))

BEGIN TRY        set XACT_ABORT on 

 INSERT #Baymak_Diyarbakir

EXEC [sp_executesql] 

N' Select   

,'''' as CITY

        ,'''' as DISTRICT

,'''' as STREET

,'''' REGION

,'''' as COUNTRY                        

FROM   deneme '

END TRY

BEGIN CATCH

print 'Diyarbakır Sunucusunda Hata Var'

END CATCH

 IF OBJECT_ID('tempdb..#Baymak_Diyarbakir') IS NOT NULL

set @ExecQuery = N'select * from #Baymak_Diyarbakir'; 

end

begin  --  Erzurum

CREATE TABLE #Baymak_Erzurum (CITY NVARCHAR(max),DISTRICT NVARCHAR(max),STREET NVARCHAR(max),REGION NVARCHAR(max),COUNTRY NVARCHAR(max))

BEGIN TRY        set XACT_ABORT on

 INSERT #Baymak_Erzurum

EXEC [sp_executesql] 

N' Select

,'''' as CITY

        ,'''' as DISTRICT

,'''' as STREET

,'''' REGION

,'''' as COUNTRY

FROM   deneme2'

END TRY

BEGIN CATCH

print 'Erzurum Sunucusunda Hata Var'

END CATCH

IF OBJECT_ID('tempdb..#Baymak_Erzurum') IS NOT NULL

set @ExecQuery = @ExecQuery+  N' UNION all select * from #Baymak_Erzurum'; 

end

DECLARE @exe nvarchar(max); 

Set @exe = @ExecQuery

EXEC [sp_executesql] @exe    

  

-- Receive

IF OBJECT_ID('tempdb..#Baymak_Diyarbakir') IS NOT NULL drop table #Baymak_Diyarbakir

IF OBJECT_ID('tempdb..#Baymak_Erzurum') IS NOT NULL drop table #Baymak_Erzurum

GO