How to Pass Column Name as Input Parameter in SQL Stored Procedure

Passing a column name as parameter to a stored procedure in mySQL

You would need to use dynamic SQL :

DELIMITER //
CREATE PROCEDURE myDB.edit_myTable(
    IN key CHAR(16), 
    IN col VARCHAR(100), 
    new_value  VARCHAR(200)
)
BEGIN
    SET @s = CONCAT(
        'UPDATE myDB.myTable SET `', 
         col, '` = ', QUOTE(new_value),
         ' WHERE key = ', QUOTE(key)
    );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
DELIMITER;

Please note that, as commented by Paul Spiegel, using a variable for column name creates a risk of SQL injection. One solution for improve security would be to make sure that the input col does exists in the target table, using MySQL information schema :

DELIMITER //
CREATE PROCEDURE myDB.edit_myTable(
    IN key CHAR(16), 
    IN col VARCHAR(100), 
    new_value  VARCHAR(200)
)
BEGIN
    DECLARE col_exists INT;

    SELECT COUNT(*) INTO col_exists 
    FROM  information_schema.COLUMNS
    WHERE TABLENAME = 'mytable' AND COLUMN_NAME = col;

    IF (col_exists != 1) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = CONCAT('Column ', col, ' does not exist in table mytable');
    END IF;

    SET @s = CONCAT(
        'UPDATE myDB.myTable SET `', 
         col, '` = ', QUOTE(new_value),
         ' WHERE key = ', QUOTE(key)
    );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
DELIMITER;

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

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:

• The curse and blessings of dynamic SQL - Erland Sommarskog
• sp_executesql

How do I pass field name as a parameter into a stored procedure

You need dynamic sql.

DECLARE @SQL NVARCHAR(4000)
Declare @column varchar 
set @column = 'S_E1' 

SET @SQL = 'select Avg(' + quotename(@column) + ') from TBL_SENSORS'

EXEC sp_executesql @SQL

Update: applied both suggestions for quotename and sp_executesql

New procedure: you can use dynamic SQL to place data into a temp table with a known schema. Refer to column based on alias: ColumnToAvg. Instead of TBL_SENSORS use #TempSensors.

Note: in production you'll want to check if #TempSensors exists and drop if it does.

SET @SQL = 'Select RECORD_TIMESTAMP, '
    + quotename(@ColumnName)   
    + ' as ColumnToAvg 
       INTO #TempSensors
       from TBL_SENSORS
       Where RECORD_TIMESTAMP Between @StartDate and @EndDate 
       And @ColumnName Between @Start And @End'

EXECUTE sp_executesql @sqlCommand
    , N'@StartDate datetime, @EndDate datetime, @Start int, @End int'
    , @StartDate , @EndDate, @Start, @End

SQL server Function - Take column name as input parameter

No you cannot use dynamic sql in functions in SQL. Please check this link for more info link.

So it is not possible to achieve this by any function, yes you may use stored procedures with output parameter for same.

You may find this link for reference link.

How can I pass column and table name as parameters to a SQL stored procedure

If you create a function like this, ensure it's safe from sql injection with something like the following. This uses dbms_assert to sanitize the inputs against thing like ';drop table xyz;'

 CREATE OR REPLACE FUNCTION getidfromnameparameter (
        in_columnname        VARCHAR2,
        in_tablename         VARCHAR2,
        in_wherecolumn       VARCHAR2,
        in_parametercolumn   VARCHAR2
    ) RETURN VARCHAR2 AS
        idcatc   VARCHAR2(50);
    BEGIN
        EXECUTE IMMEDIATE 'SELECT '
                         || sys.dbms_assert.qualified_sql_name(in_columnName)
                         || ' FROM '
                         || sys.dbms_assert.sql_object_name(in_tableName)
                         || ' WHERE '
                         || sys.dbms_assert.qualified_sql_name(in_whereColumn)
                         || ' = '
                         || sys.dbms_assert.enquote_literal(in_parametercolumn)
       INTO idcatc;

       RETURN idcatc;
  END;
  /

Column Name in Stored Procedure Parameter

The only way I can think of doing this would be using sp_executesql.

It will do the job but I think that you also loose any benefits of stored procedures

create procedure spGetChartValue (@FieldName nvarchar(50), @Value nvarchar(50) as
begin
    set nocount on

    declare @sql nvarchar(1000) = 'select count(1) from Feedback where ' + quotename(@fieldname) + ' = ' + quotename(@grade, '''')
    exec sp_executesql @sql

end

from your last comment I think you are looking for something like this ?

create procedure spGetChartValue (@Grade nvarchar(50)) as
begin
    set nocount on

    declare @sql nvarchar(1000)

    set @sql = 'select '
    set @sql = @sql + '(select count(1) from Feedback where ' + quotename(@Grade) + ' = 0) as grade_0, '     
    set @sql = @sql + '(select count(1) from Feedback where ' + quotename(@Grade) + ' = 1) as grade_1, '     
    set @sql = @sql + '(select count(1) from Feedback where ' + quotename(@Grade) + ' = 2) as grade_2 '     
    -- and so on...

    exec sp_executesql @sql
end;

SQL Stored Procedure with column name as variable

It is better to use dymamic SQL as Andrew or Roman Czerwinski recommended.
But if you don't want to use dynamic SQL then you can use this logic:

ALTER PROC [dbo].[GetCard](
    @Input varchar(50),
    @Input2 varchar(50)
)
AS
SELECT * 
FROM Main 
WHERE CardName  = case when @Input = 'CardName'    then @Input2 else CardName    end
and OtherColumn = case when @Input = 'OtherColumn' then @Input2 else OtherColumn end
--etc...

Pass input parameter as column name to update a column value in stored procedure

declare @sql nvarchar(50);

50 characters is too short for your SQL String. Try changing it to nvarchar(500)

---

Related Topics