k9yeAQyS

1CREATE PROCEDURE [dbo].[USP_SMSGeneric_CountrySpace] @DB VARCHAR(100)                                        
2As                                        
3BEGIN                                        
4
5SET NOCOUNT ON                                    
6
7DECLARE @StudyID  varchar(max)                                    
8DECLARE @Databasename VARCHAR(max)                                    
9DECLARE @QUERY NVARCHAR(MAX)                                  
10DECLARE @Protocol varchar(max)                                    
11DECLARE @Servername varchar(max)                                     
12DECLARE @script VARCHAR(Max)        
13DECLARE @script1 VARCHAR(Max)                                    
14DECLARE @initscript NVARCHAR(Max)       
15DECLARE @Countries VARCHAR(Max)         
16DECLARE @Countryrelease VARCHAR(Max) 
17
18IF OBJECT_ID('TEMPDB..#OBJMISSING') IS NOT NULL DROP TABLE  #OBJMISSING       
19
20
21CREATE TABLE #OBJMISSING (ERRID INT IDENTITY(1,1),ERRNUM BIGINT,ERRMSG VARCHAR(MAX),DBNAME VARCHAR(MAX))                                        
22
23
24SET @initscript='
25    DECLARE csrStudy CURSOR FOR                                    
26    SELECT  ProtocolName, DBName, studyid,DBServer AS Servername from SMSAPP.dbo.studymaster  WITH (NOLOCK)                                   
27      WHERE ClientName LIKE ''%NOVARTIS%'' AND studystatus IN (1,2) AND DBServer  IN (''SQL002'' ,''SQL004'',''SQL005'')                                         
28    '
29EXEC sp_executesql @initscript                                 
30
31OPEN csrStudy                                  
32FETCH NEXT FROM csrStudy INTO @Protocol,@Databasename,@StudyID,@ServerName                                    
33
34WHILE @@FETCH_STATUS = 0                                      
35BEGIN                                      
36    SET @DB = @Servername+'.'+@Databasename        
37
38    SET @script = '
39
40    DECLARE @StrValue VARCHAR(max)
41
42    BEGIN TRY     
43        IF EXISTS (         
44            SELECT DISTINCT 1 FROM  '+@DB+'.sys.columns c JOIN '+@DB+'.sys.Tables t ON c.Object_ID=t.Object_ID
45            WHERE c.Name = ''Countries’'' AND t.name =''tblMaterials'')      
46        BEGIN
47            SELECT  @StrValue = ISNULL(@StrValue + '','', '''') + Countries’ FROM (
48            SELECT DISTINCT (LEN(Countries’ + '','') - LEN(REPLACE(Countries’, '' '', '''') + '',''))CNT,Countries  
49            FROM  '+@DB+'.dbo.tblMaterials WITH (NOLOCK) )A WHERE CNT>0
50        END
51    END TRY
52
53    BEGIN CATCH          
54        INSERT INTO #OBJMISSING VALUES 
55        (ERROR_NUMBER(),ERROR_MESSAGE(),''+@Databasename+'')     
56    END CATCH     
57
58    IF @StrValue IS NOT NULL   -- If any Duplicate values found, then raise an alert           
59    BEGIN           
60        SELECT '+@StudyID+' As StudyID,
61        ''Countries field value Should not have space''   AS Actual ,          
62        ''Countries field value exists with space for String :'' + @StrValue AS Discrepancy INTO #tempOutput
63	
64EXEC('SELECT * FROM [SomeLinkedServer].DatabaseName.SchemaName.NonExistingTable')
65	
66BEGIN TRY
67
68    EXEC('SELECT * FROM [SomeLinkedServer].DatabaseName.SchemaName.NonExistingTable')
69
70END TRY
71
72BEGIN CATCH
73
74    SELECT 'This is the catch section'
75
76END CATCH
77	
78BEGIN TRY
79
80    SELECT 1 FROM [SomeLinkedServer].DatabaseName.SchemaName.NonExistingTable
81
82END TRY
83
84BEGIN CATCH
85
86    SELECT 1
87
88END CATCH
89	
90DECLARE @Variable ...
91
92DECLARE MyCursor CURSOR FOR ...
93
94FETCH NEXT FROM MyCursor INTO @Variable
95
96WHILE @@FETCH_STATUS = 0
97BEGIN
98
99    BEGIN TRY
100
101        DECLARE @DynamicSQL VARCHAR(MAX) = ... -- The DynamicSQL may have another TRY CATCH inside
102
103        EXEC(@DynamicSQL)
104
105    END TRY
106
107    BEGIN CATCH
108
109        -- Do your catch operation here, you can leave this section empty if you want (not recommended)
110
111    END CATCH
112
113    FETCH NEXT FROM MyCursor INTO @Variable
114
115END