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

Enni

8y911

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravi Kiran Chanduri8y ago
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
0
Packiaraj Santhiyagu8y ago
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
0
Nanddeep Nachan8y ago
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
0