Can I pass column name as input parameter in SQL s

问题:

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

回答1:

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.

回答2:

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\'\'

回答3:

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

回答4:

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

回答5:

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

回答6:

   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

回答7:

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

回答8:

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 **/