How to pass string array of Columns as parameter to SQL Server Stored procedure

Question

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.

what is the purpose of doing so ?why do you want row_number ?what you are trying to do ?refer this for split function stackoverflow.com/questions/2647/split-string-in-sql — KumarHarsh
– KumarHarsh, Commented Feb 14, 2014 at 12:32

seph · Accepted Answer · 2014-02-14 12:50:00Z

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.

edited Feb 14, 2014 at 12:50

answered Feb 14, 2014 at 12:28

seph

6942 gold badges8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M.Ali Over a year ago

This is not correct, sql server will treat this passed string of column names as a string but not object names.

Hans Keﬆing Over a year ago

You don't need to pass @ColNames to that sp_executesql: the value is already embedded in @sSQL

seph Over a year ago

@M.Ali Could you point out exactly what would be not working out? Testing this out really quick @SQLFiddle seems to work.

Community · Accepted Answer · 2017-05-23 11:45:16Z

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?

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Feb 14, 2014 at 12:41

Fabien Zentz

565 bronze badges

Collectives™ on Stack Overflow

How to pass string array of Columns as parameter to SQL Server Stored procedure

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related