Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

My requirement is to pass column names as input parameters. I tried like that but it gave wrong output.

So Help me

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
  yourTable
This is a bit more long winded, but a whole lot more secure.
                If you're getting the columns from another table "UpdateableColumns" you can also do some kind of verification with it.  Example: "Where Column exist in (select ColumnName from UpdateableColumns)"
– EduLopez
                Sep 14, 2015 at 18:13
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''
                No, not much slower. The only amount it's slower by is the string concatenation overhead. sp_executesql will execute the text in a way where it will be translated into an execution plan just like any other command.
– Darren Kopp
                Aug 21, 2014 at 14:35
    declare @sql nvarchar(4000);
    set @sql='select ['+@columnname+'] from Table_1';
    exec sp_executesql @sql
exec sp_First 'sname'
                If the CASE is only in the SELECT statement (and not in a JOIN, WHERE clause, ORDER BY, etc) then this option is not actually that slow.
– MatBailie
                Apr 10, 2012 at 16:51
   Create PROCEDURE USP_S_NameAvilability
     (@Value VARCHAR(50)=null,
      @TableName VARCHAR(50)=null,
      @ColumnName VARCHAR(50)=null)
        BEGIN
        DECLARE @cmd AS NVARCHAR(max)
        SET @Value = ''''+@Value+ ''''
        SET @cmd = N'SELECT * FROM ' + @TableName + ' WHERE ' +  @ColumnName + ' = ' + @Value
        EXEC(@cmd)
As i have tried one the answer, it is getting executed successfully but while running its not giving correct output, the above works well 
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
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)
    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'
        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 
First Run;
CREATE PROCEDURE sp_First @columnname NVARCHAR(128)--128 = SQL Server Maximum Column Name Length
BEGIN
    DECLARE @query NVARCHAR(MAX)
    SET @query = 'SELECT ' + @columnname + ' FROM Table_1'
    EXEC(@query)
Second Run;
EXEC sp_First 'COLUMN_Name'
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT * FROM ' + @Table + ' WHERE ' + @Column + ' = ' + @Value
--SELECT @sql
exec (@sql)
-----execution----
/** Exec Test Products,IsDeposit,1 **/
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.