0

I have a comma delimited string with column names i.e.

"Column1, Column2, Column3" which will be passed as a parameter to the following stored procedure.

My Stored Procedure is as follows:

@ColNames VARCHAR(1000)

Select ROW_NUMBER() OVER (ORDER BY Column1, Column2, Column3) AS RowNum, 
Column1, Column2, Column3
INTO #Results
From Table A

I want to replace the above hardcoded column names with the parameter @colNames. This will include splitting the comma delimited parameter into the individual column names.

Shiva
  • 20,575
  • 14
  • 82
  • 112
user2906420
  • 1,249
  • 6
  • 27
  • 44
  • 1
    what is the purpose of doing so ?why do you want row_number ?what you are trying to do ?refer this for split function http://stackoverflow.com/questions/2647/split-string-in-sql – KumarHarsh Feb 14 '14 at 12:32

2 Answers2

2

You'd need to use dynamic SQL in this case and execute it with sp_executesql.
For example:

DECLARE @ColNames VARCHAR(1000)
DECLARE @sSQL NVARCHAR(MAX)

SET @ColNames ='Column1, Column2,...'

SET @sSQL = 'SELECT ' + @ColNames + ' FROM myTable....'

exec sp_executesql @sSQL



I also made a quick SQLFiddle to this.

seph
  • 674
  • 2
  • 8
  • 23
  • This is not correct, sql server will treat this passed string of column names as a string but not object names. – M.Ali Feb 14 '14 at 12:38
  • 1
    You don't need to pass `@ColNames` to that sp_executesql: the value is already embedded in `@sSQL` – Hans Kesting Feb 14 '14 at 12:43
  • @M.Ali Could you point out exactly what would be not working out? Testing this out really quick [@SQLFiddle](http://sqlfiddle.com/#!6/fdf65/5) seems to work. – seph Feb 14 '14 at 12:43
2

You may try this :

CREATE PROCEDURE yourId (@columns VARCHAR(1000))
AS
BEGIN
DECLARE @s_query VARCHAR(MAX)

SET @s_query = 'Select ROW_NUMBER() OVER (ORDER BY ' + @columns + ') AS RowNum, ' + @columns + ' From A'

EXEC(@s_query)
END

this is for t sql syntax

If you need information on how to split a string, you may have a look on this thread : Split function equivalent in T-SQL?

Community
  • 1
  • 1
Fabien Zentz
  • 56
  • 5