locked
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

  • And which exact error message do you get?

    BTW, you can find SQL Server related forums at https://docs.microsoft.com/en-us/answers/products/sql-server


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 4, 2020 9:27 AM
  • Hello Nitin,

    This forum is dedicated to supporting issues related to open specification. You can access open specifications at https://docs.microsoft.com/en-us/openspecs/. Your inquiry does not seem to be related to open specifications.

    For a better chance of getting an answer to your question, you may want to post your question to the following forum:

    https://docs.microsoft.com/en-us/answers/products/sql-server

    Best regards,
    Tom Jebo
    Sr Escalation Engineer
    Microsoft Open Specifications

    Wednesday, November 4, 2020 3:12 PM