How to pass tablename as parameter in SP to add another col?

Question

Hi, Please help me to solve this. I have three 3 tables with some data and column names are same. June_2018(table name 1) id Name 1 Critical 2 Major 3 Critical 4 Minimum 5 Major July_2018 id Name 6 Critical 7 Major 8 Critical 9 Minimum 10 Major August_2018 id Name 11 Critical 12 Major 13 Critical 14 Minimum 15 Major I need a stored procedure where tablename should be passed as the parameter then Critical, Major should be as Major as another column and Minimum should be updated as Minimum. Expected output should be as follows. Exec procedurename(Tablename as parameter) id Name Staus 1 Critical Major 2 Major Major 3 Critical Major 4 Minimum Minimum 5 Major Major Thanks in advance.

Ravi Kiran Chanduri · Answer

Hello Enni, There are 2 requirements in your question. 1. table name should be changed dynamically 2. data in the 'Name' column should be manipulated with the small condition. For your first requirement, you can make use of the Dynamic SQL query. For the second one, you have to use the CASE statement to manipulate the column data in the result. I have consolidated the result and created an SP. Here you go, CREATE PROC proc1 ( @Tab_Name varchar (100) ) AS BEGIN declare @QuerytoExec VARCHAR ( max ) --Creating Dynamic Query -- added the CASE statement with in the select statement select @QuerytoExec= 'select id, Name= CASE WHEN Name in (' 'Critical' ', ' 'Major' ') THEN ' 'Major' ' ELSE ' 'Minimum' ' END from' +@Tab_Name; --executing the Query exec (@QuerytoExec) END

Packiaraj Santhiyagu · Answer

Hi , It will work, if any dobut comment. CREATE PROC sampeProc( @passTable VARCHAR (50)= null ) AS BEGIN IF(@passTable= 'June_2018' ) BEGIN SELECT id, [ Name ], CASE [ name ] WHEN 'Critical' THEN 'Major' WHEN 'Major' THEN 'Major' WHEN 'Minimum' THEN 'Minimum' END AS [Ststus] FROM June_2018 END IF(@passTable= 'July_2018' ) BEGIN SELECT id, [ Name ], CASE [ name ] WHEN 'Critical' THEN 'Major' WHEN 'Major' THEN 'Major' WHEN 'Minimum' THEN 'Minimum' END AS [Ststus] FROM August_2018 END IF(@passTable= 'August_2018' ) BEGIN SELECT id, [ Name ], CASE [ name ] WHEN 'Critical' THEN 'Major' WHEN 'Major' THEN 'Major' WHEN 'Minimum' THEN 'Minimum' END AS [Ststus] FROM August_2018 END END Accept as answer, if it helps

Nanddeep Nachan · Answer

Please try below code to get reference of table dynamically. create procedure test(@table_name varchar ( max )) AS BEGIN declare @tablename varchar ( max )=@table_name; declare @object_id int ; set @object_id=OBJECT_ID(@table_name) select * from sys.columns where object_id=@object_id; declare @statement varchar ( max ); set @statement = 'Select * from ' + @tablename; execute (@statement); END