CODEWITHSUNDEEP
sqlsql-server
Sraj Muneer
Sraj Muneer

Reputation: 27

How to pass parameter as column name in stored procedure in my example

CREATE PROCEDURE ProcedureName1 
    @columnsname varchar(50), 
    @columnsvalue varchar(50) 
AS 
BEGIN 
    with cet as   
    ( 
        Select  ID,
                names,
                Null As address,
                work,
                note
        From    Tabl1
        Where   @columnsname Like @columnsvalue
        Union All
        Select  t2.ID,
                t2.name,
                t2.address,
                Null,
                Null As tt
        From    Tabl2 As t2
        Left Join Tabl1 As t1
                On t2.ID = t1.ID
        Where   @columnsname Like @columnsvalue 
    )  
    Select  *
    From    cet
    Order By id,
            note Desc,
            cet.address  
END GO

Upvotes: 1

Views: 1642

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You would have to use dynamic sql for something like this. It would be a good idea to whitelist the value of @columnsname against sys.columns or information_schema.columns as well.

Use sp_executesql to continue to keep @columnsvalue as parameter, and not concatenate it to the SQL you will be executing. 

create procedure ProcedureName1 (
   @columnsname sysname --varchar(50) 
 , @columnsvalue varchar(50)
) as
begin
declare @sql nvarchar(max), @column_name sysname;
/* make sure the @columnsname is a valid column name */
set @column_name = (
  select c.name
  from sys.columns c
  where c.object_id = object_id(N'Tabl1') 
    and c.name = @columnsname
  );
set @sql = 'with cte as (
    select 
        ID
      , names
      , null as address
      , work
      , note
    from Tabl1
    where '+@column_name+' like @columnsvalue
    union all
    select 
        t2.ID
      , t2.name
      , t2.address
      , null
      , null as tt
    from Tabl2 as t2
    left join Tabl1 as t1 on t2.ID = t1.ID
    where '+@column_name+' like @columnsvalue
    )
  select *
  from cet
  order by 
      id
    , note desc
    , address;'
exec sp_executesql @sql, N'@columnsvalue varchar(50)', @columnsvalue
end;
go

Reference:

Upvotes: 1

Related Questions