Passing a Column name to a stored procedure.

• cfeisel

  SSC Veteran

  Points: 206

  More actions

  December 2, 2003 at 9:50 am

  #83577

  Is there a way I could pass a column name to a stored procedure to be used in a select statement?

  Create PROCEDURE varcolSearch

  @colname varchar(30) = 'DefaultCol' AS

  Select T1.col1, t1.col2, t1.[@colname]

  From table as t1

  Where X

  Return

  Any insights would be greatly appreciated.

  Thanks,

  Chirs

• mila

  SSC Enthusiast

  Points: 175

  More actions

  December 2, 2003 at 10:13 am

  #484318

  You can create SQL string and than execute it in a stored procedure, like this:

  Create PROCEDURE varcolSearch

  @colname varchar(30) = 'DefaultCol' AS

  declare @U nvarchar(1000)

  set @U='Select T1.col1, t1.col2, t1.' + @colname + 'From table as t1 Where X '

  EXECUTE sp_executesql @U

  GO

• dkretz

  SSC Rookie

  Points: 33

  More actions

  December 2, 2003 at 2:54 pm

  #484375

  You can, but you probably shouldn't. Any solution is almost surely going to eliminate effective compiling and optimization.

  You're better off just writing a separate sp for each column. Swiss-Army-Knife stored procecures usually end up unreadable and unoptimizable.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  December 3, 2003 at 12:37 am

  #484404

  Sure you can do this, but read http://www.algonet.se/~sommar/dynamic_sql.html first and decide then if you want to.

  Frank

  http://www.insidesql.de

  http://www.familienzirkus.de

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]