Can I pass column name as input parameter in SQL stored Procedure

Question

create procedure sp First  columnname varchar AS begin select  columnname from Table 1 end  exec sp First  sname    My requirement is to pass column names as input parameters   I tried like that but it gave wrong output   So Help me

User · Answer

Try using dynamic SQL   create procedure sp First  columnname varchar  AS  begin      declare  sql nvarchar 4000       set  sql  select     columnname    from Table 1       exec sp executesql  sql end  go  exec sp First  sname  go

User · Answer

First Run   CREATE PROCEDURE sp First  columnname NVARCHAR 128 --128   SQL Server Maximum Column Name Length AS BEGIN      DECLARE  query NVARCHAR MAX       SET  query    SELECT      columnname     FROM Table 1       EXEC  query   END   Second Run   EXEC sp First  COLUMN Name

User · Answer

This is not possible  Either use dynamic SQL  dangerous  or a gigantic case expression  slow

User · Answer

Create PROCEDURE USP S NameAvilability        Value VARCHAR 50  null         TableName VARCHAR 50  null         ColumnName VARCHAR 50  null          AS         BEGIN         DECLARE  cmd AS NVARCHAR max          SET  Value         Value               SET  cmd   N SELECT   FROM      TableName     WHERE       ColumnName            Value         EXEC  cmd        END   As i have tried one the answer  it is getting executed successfully but while running its not giving correct output  the above works well

User · Answer

You can do this in a couple of ways   One  is to build up the query yourself and execute it   SET  sql    SELECT      columnName     FROM yourTable  sp executesql  sql   If you opt for that method  be very certain to santise your input   Even if you know your application will only give  real  column names  what if some-one finds a crack in your security and is able to execute the SP directly   Then they can execute just about anything they like   With dynamic SQL  always  always  validate the parameters   Alternatively  you can write a CASE statement     SELECT   CASE  columnName     WHEN  Col1  THEN Col1     WHEN  Col2  THEN Col2                 ELSE NULL   END as selectedColumn FROM   yourTable   This is a bit more long winded  but a whole lot more secure

User · Answer

As mentioned by MatBailie  This is much more safe since it is not a dynamic query and ther are lesser chances of sql injection   I Added one situation where you even want the where clause to be dynamic    XX YY are Columns names               CREATE PROCEDURE  dbo   DASH getTP under TP             fromColumnName varchar 10         toColumnName varchar 10          ID varchar 10            as     begin      -- this is the column required for where clause      declare  colname varchar 50      set  colname case  fromUserType         when  XX  then  XX          when  YY  then  YY          end         select SelectedColumnId  from          select              case  toColumnName              when  XX  then tablename XX             when  YY  then tablename YY             end as SelectedColumnId          From tablename         where           case  fromUserType              when  XX  then XX             when  YY  then YY         end   ISNULL  ID    colname         as tbl1 group by SelectedColumnId       end

User · Answer

You can pass the column name but you cannot use it in a sql statemnt like   Select  Columnname From Table   One could build a dynamic sql string and execute it like EXEC   SQL   For more information see this answer on dynamic sql   Dynamic SQL Pros and Cons

User · Answer

Please Try with this      I hope it will work for you   Create Procedure Test        Table VARCHAR 500        Column VARCHAR 100        Value  VARCHAR 300    AS BEGIN  DECLARE  sql nvarchar 1000   SET  sql    SELECT   FROM      Table     WHERE      Column            Value  --SELECT  sql exec   sql   END  -----execution----      Exec Test Products IsDeposit 1

User · Answer

No  That would just select the parameter value  You would need to use dynamic sql   In your procedure you would have the following   DECLARE  sql nvarchar max     SELECT      columnname     FROM Table 1   exec sp executesql  sql  N

[sql] Can I pass column name as input parameter in SQL stored Procedure

Examples related to sql

Examples related to stored-procedures