none
SQL stored procedure query RRS feed

  • Question

  • Hi all ,

    i am working on one change set and facing issue as :

    CREATE procedure Name
    
    	@variable1 -- User define table type,
    	@variable2  -- User define table type,
    	@boolVariable bit
    
    AS
    	SET NOCOUNT ON
    	
    
    		-- declare temp table
    		DECLARE table_name table (field1 type,field2 type,field3 type,field4 type,field5 type)
    
    		-- Insert input table param records
    		INSERT INTO --temp table name(table_name)
    		SELECT v1.field1 ,v1.field2 ,v1.field3 ,v2.field4 ,v2.field5 from @variable1 v1 cross join @variable2 v2
    
    	BEGIN TRANSACTION;
    	BEGIN TRY
    		-- Check if it has unique data for insertion
    
    			SELECT * FROM table_name(temp table)
    			 EXCEPT
    			SELECT  p1.field1 ,p1.field2 ,p1.field3 ,p2.field4 ,p2.field5
    			FROM name_table (this is Dabase table) p1
    			INNER JOIN rank_table (this is database table) p2 on p1.Id =  p2.Id
    
    
    		BEGIN
    			INSERT INTO name_table 
    				(
    					field1
    					,field2
    					,field3									
    					) 
    					SELECT field1,field2,field3 from @variable1
    
    	
    			INSERT INTO rank_table 
    				(
    					
    					,filed4
    					,field5)
    					SELECT dc.field4,dc.field5 from @variable2 as dc
    		END
    	
    
    	END TRY
        BEGIN CATCH
    	 ROLLBACK TRANSACTION;
        END CATCH
    	 COMMIT TRANSACTION;
    	 END
    If boolVariable is 1 then 
    GO
    
    
    ---- Here i want to check if boolVaroable = 1 then exclude field4 from check as follows : while doing so it is giving error can some one help me ?
    
    			SELECT * FROM table_name(temp table)
    			 EXCEPT
    			SELECT  p1.field1 ,p1.field2 ,p1.field3 ,p2.field5
    			FROM name_table (this is Dabase table) p1
    			INNER JOIN rank_table (this is database table) p2 on p1.Id =  p2.Id
    


    Thanks and Regards, Nitin.

    Wednesday, November 4, 2020 7:52 AM

All replies