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

by

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.

More Related Contents:

  1. Function vs. Stored Procedure in SQL Server
  2. Passing an array of parameters to a stored procedure
  3. SQL Server – How to lock a table until a stored procedure finishes
  4. The maximum recursion 100 has been exhausted before statement completion
  5. Search text in stored procedure in SQL Server
  6. SQL Server SELECT into existing table
  7. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  8. How to check if a stored procedure exists before creating it
  9. Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? [duplicate]
  10. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  11. SQL : in clause in stored procedure:how to pass values
  12. How to return the output of stored procedure into a variable in sql server
  13. Split values over multiple rows [duplicate]
  14. Execute Immediate within a stored procedure keeps giving insufficient priviliges error
  15. How can I determine the status of a job?
  16. Procedure or function !!! has too many arguments specified
  17. Must declare the scalar variable
  18. Is it possible to have a default parameter for a mysql stored procedure?
  19. Ad hoc queries vs stored procedures vs Dynamic SQL [closed]
  20. Array in IN() clause oracle PLSQL
  21. Insert Update stored proc on SQL Server
  22. SQL Server: should I use information_schema tables over sys tables?
  23. Inserting rows into a table with one IDENTITY column only [duplicate]
  24. UNION the results of multiple stored procedures
  25. How to pass a temp table as a parameter into a separate stored procedure
  26. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  27. Error trying to call stored procedure with prepared statement
  28. Dynamic order direction
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess

Leave a Comment