Column Name Variable in SPROC

• mebjen

  SSC Veteran

  Points: 219

  More actions

  March 19, 2004 at 7:45 am

  #161245

  I am try to write a SPROC where @colName is a variable for the column name of a table - something like this:

  Create proc sp_doIt

  @colName varChar(20)

  as

  Select distinct(@colName), count(*)

  from theTable

  group by @colName

  Can somebody tell me what I am doing wrong and what the correct way would be.

  thanks meb

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  March 19, 2004 at 9:17 am

  #499525

  Well usage of Dynamic SQL has its pro's and Con's and I learnt from the article posted by Frank, however if you still want to go with it then it could be your solution

  CREATE proc sp_doIt

  @colName varChar(20)

  as

  declare @sql1 varchar(1000),

   @sql2 varchar(1000)

  set @sql1 ='Select distinct(' + @colName + '), count(*)

  from theTable'

  set @sql2 = ' group by ' + @colName

  exec (@sql1+@sql2)

  Prasad Bhogadi
  www.inforaise.com