Stored Procedures

Try catch exception handling in SQL server
Alter PROCEDURE [dbo].[proc_get_org_info_ex]
(
@nc_org_id varchar(50)=NULL,
@nc_org_name varchar(150)=NULL
)
As
/*
-1 for result not found
-2 for Argument not provided
-3 for Exception occurred will give all the info
*/
Begin
BEGIN TRY

        IF (@nc_org_id IS NOT NULL AND @nc_org_name IS NOT NULL)
        BEGIN
        IF Exists(SELECT    nc_org.nc_org_id,nc_org.nc_org_name FROM nc_org WHERE ( nc_org.nc_org_id = rtrim(ltrim(@nc_org_id)) AND nc_org.nc_org_name = rtrim(ltrim(@nc_org_name))))   
                BEGIN
                        SELECT    nc_org.nc_org_id,nc_org.nc_org_name FROM nc_org WHERE ( nc_org.nc_org_id = rtrim(ltrim(@nc_org_id)) AND nc_org.nc_org_name = rtrim(ltrim(@nc_org_name)))   
                   
                END
        ELSE
                BEGIN
                        SET @nc_org_id =-1
                        SET @nc_org_name='Not Found'
                        Select @nc_org_id as nc_org_id , @nc_org_name as nc_org_name
                END
        END
        ELSE
        BEGIN
        SET @nc_org_id =-2
        SET @nc_org_name='Please provide args'
        Select @nc_org_id as nc_org_id , @nc_org_name as nc_org_name
        END
               
               
       


END TRY
BEGIN CATCH
            SET @nc_org_id =-3
            SET @nc_org_name='Error Occurred'
 SELECT
   
        @nc_org_id as nc_org_id ,
        @nc_org_name as nc_org_name,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;

END CATCH
END
EXEC proc_get_org_info_ex  '128++4478' ,'GENERAL MOTORS GLOBAL'




Alter table and adding column name with default value
ALTER Table nc_osg_join
ADD nc_osg_site_active BIT NOT NULL Default(0)
*********************************************************************************

Transaction example

 /* 
  
 Amit Sharma 04/15/2014 
*/          
CREATE PROCEDURE [dbo].[proc_Active_Inactive_ORG_OSGJoin_NCTYPE]           
@nc_table_name  varchar(100) = NULL,           
 @type varchar(20)=NULL, 
 @nc_org_id varchar(8000) = NULL        
AS           
 SET NOCOUNT ON           
    
 DECLARE @row_count int           
 SET @row_count = 0           
 DECLARE @return_code int           
 DECLARE @return_message varchar(200)           
            
 /* +VE return_code is success. Negative indicates some error.           
  @return_code @return_message           
  0    No Action Happened           
  IDENT_CURRENT Insert Successful !           
  @nc_item_id  Update Successful !           
  -2    Update Failed : No Matching Record to Update           
  -3    Invalid Action Specified           
  -4    @add_or_update Failed : Record @nc_item_new_name Already Exists           
 */           
            
 SET @nc_table_name = UPPER(@nc_table_name)           
            
 IF (@nc_org_id IS NULL OR @nc_org_id = '')        
 BEGIN           
  SET @return_code = -1           
  SET @return_message = 'ERROR : No Records Specified to Delete'           
  GOTO PROC_EXIT           
 END           
            
 IF @nc_table_name IS NULL OR @nc_table_name = ''           
 BEGIN           
  SET @return_code = -2           
  SET @return_message = 'ERROR : Table Name not Specifed to Delete data from'           
  GOTO PROC_EXIT           
 END           
             
             
 ELSE IF (@nc_table_name = 'NC_ORG'  AND  @type='A')         
 BEGIN           
    BEGIN TRANSACTION transA 
BEGIN TRY 
 UPDATE nc_org SET nc_org_active = 1 WHERE nc_org_id =@nc_org_id  
 UPDATE nc_osg_join SET nc_osg_active = 1   WHERE nc_org_id =@nc_org_id  
 UPDATE nc_org_nc_type_join SET nc_org_nc_type_active = 1 WHERE nc_org_id =@nc_org_id  
 IF @@TRANCOUNT > 0     
 BEGIN COMMIT TRANSACTION transA    
 SET @return_code =1 
 SET @return_message = ' Record(s) Activated !'   
 GOTO PROC_EXIT   
 END 
END TRY 
BEGIN CATCH 
 print 'Error Occured' 
 IF @@TRANCOUNT > 0 
 BEGIN ROLLBACK TRANSACTION transA  
 SET @return_code =-1 
 SET @return_message = ' No action !'   
 GOTO PROC_EXIT 
 END 
END CATCH       
 
          
 END     
           
           
           
           
           
ELSE IF( @nc_table_name = 'NC_ORG'   AND  @type='I')      
 BEGIN       
  BEGIN TRANSACTION transI 
BEGIN TRY 
 UPDATE nc_org SET nc_org_active = 0 WHERE nc_org_id =@nc_org_id  
 UPDATE nc_osg_join SET nc_osg_active = 0  WHERE nc_org_id =@nc_org_id  
 UPDATE nc_org_nc_type_join SET nc_org_nc_type_active = 0 WHERE nc_org_id =@nc_org_id  
 IF @@TRANCOUNT > 0     
 BEGIN COMMIT TRANSACTION transI 
 SET @return_code =1 
 SET @return_message = 'Record(s) In-Activated !'   
 GOTO PROC_EXIT   
 END 
END TRY 
BEGIN CATCH 
 print 'Error Occured' 
 IF @@TRANCOUNT > 0 
 BEGIN ROLLBACK TRANSACTION transI  
 SET @return_code =-1 
 SET @return_message = 'No action !'   
 GOTO PROC_EXIT 
 END 
END CATCH       
 END  
      
      
            
            
 ELSE           
 BEGIN           
  SET @return_code = -3           
  SET @return_message = 'ERROR : Invalid Table Name Specified - ' + @nc_table_name           
  GOTO PROC_EXIT           
            
 END           
            
 IF  @row_count = 0            
 BEGIN           
  SET @return_code = -4           
  SET @return_message = 'NO Matching Record(s) FOUND to Delete. No Records Deleted !'            
  GOTO PROC_EXIT           
 END           
            
 SET @return_code = @row_count           
 SET @return_message = convert(varchar, @row_count) + ' Record(s) Activated !'            
 GOTO PROC_EXIT           
            
            
 PROC_EXIT:           
  SELECT @return_code AS return_code, @return_message AS return_message           
  --RETURN @return_code           
            
 SET NOCOUNT OFF           
 RETURN  
***************************************************************************

INSERT INTO SELECT Statement

Insert and Select an existing table

The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected

We can copy all columns from one table to another, existing table:

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';  

 

SQL SELECT INTO Statement

Select and Insert New table

The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

Create a backup copy of Customers:

SELECT *
INTO CustomersBackup2013
FROM Customers;