By user1169809


2012-04-10 16:42:43 8 Comments

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

8 comments

@Saronyo 2017-05-30 10:11:42

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

@MatBailie 2012-04-10 16:50:24

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.

@KM. 2012-04-10 17:28:46

+1, This is a bit more long winded, but a whole lot more secure.

@EduLopez 2015-09-14 18:13:15

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)"

@Out 2013-03-05 06:09:58

   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 = ''''[email protected]+ ''''
        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

@Sujay 2013-01-17 10:06:16

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

@Tiago 2014-12-30 19:04:29

Select * is not a good practice and this is not the user's doubts

@John Dewey 2012-04-10 16:50:01

Try using dynamic SQL:

create procedure sp_First @columnname varchar 
AS 
begin 
    declare @sql nvarchar(4000);
    set @sql='select ['[email protected]+'] from Table_1';
    exec sp_executesql @sql
end 
go

exec sp_First 'sname'
go

@Jon Raynor 2012-04-10 16:48:13

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

@Darren Kopp 2012-04-10 16:47:48

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

@Ashkan Mobayen Khiabani 2013-06-14 10:00:09

it must be slower, right? but how much? can it be ignored?

@Darren Kopp 2014-08-21 14:35:43

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.

@usr 2012-04-10 16:46:55

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

@MatBailie 2012-04-10 16:51:28

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.

@usr 2012-04-10 16:51:53

It will pull out all columns every time, no matter what concrete column is requested.

Related Questions

Sponsored Content

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

16 Answered Questions

[SOLVED] Select columns from result set of stored procedure

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

25 Answered Questions

37 Answered Questions

15 Answered Questions

[SOLVED] SQL Server - SELECT FROM stored procedure

12 Answered Questions

[SOLVED] SQL Server: Query fast, but slow from procedure

2 Answered Questions

Sponsored Content