Select columns based on dynamic table value parameters?

Question

I'm trying to build a stored procedure that will be dynamic as possible:

I'd like to pass the values of the select statement as parameters which can have a variable number of parameters, then perform the select by those parameters, e.g.

select @column1, @column5, @column8 from myTable

@column1, @column5, @column8 - Those should be passed as one parameter to the stored procedure, where the number of parameters can vary.

How do I write this kind of stored procedure?

Why do you want to do this? Might as well generate the adhoc SQL strings in your application. Can't see what benefit doing this in a stored procedure gives you. — Martin Smith, Sep 07 '14 at 15:16
@MartinSmith - I had a feeling that writing it in a stored procedure would leave me with a clearer code on my application (instead of concatenating string 'select ' + String.Join(",", columnList) + '...'. — BornToCode, Sep 07 '14 at 15:21
Whether or not you end up doing it in SQL you shouldn't use `String.Join(",", columnList)` anyway if this input is in anyway untrusted. You need to make sure that the column names are quoted in `[]` and any `]` characters are doubled up to avoid SQL injection. — Martin Smith, Sep 07 '14 at 15:26
@MartinSmith - I think this method will only move the mess from the client code into T-SQL, not perform as well, and be less secure. — Dan Guzman, Sep 07 '14 at 15:55

M.Ali · Accepted Answer · 2014-09-07T16:03:55.047

2

CREATE PROCEDURE usp_selectColumns
  @Var1   SYSNAME = NULL
 ,@Var2   SYSNAME = NULL 
 ,@Var3   SYSNAME = NULL
 ,@Var4   SYSNAME = NULL
AS
BEGIN
  SET NOCOUNT ON;
    DECLARE @Columns NVARCHAR(MAX);
    DECLARE @Sql NVARCHAR(MAX);

SET @Columns = ', ' 
                 + CASE WHEN @Var1 IS NOT NULL THEN QUOTENAME(@Var1) + N', ' ELSE N' ' END
                 + CASE WHEN @Var2 IS NOT NULL THEN QUOTENAME(@Var2) + N', ' ELSE N' ' END
                 + CASE WHEN @Var3 IS NOT NULL THEN QUOTENAME(@Var3) + N', ' ELSE N' ' END
                 + CASE WHEN @Var4 IS NOT NULL THEN QUOTENAME(@Var4) + N', ' ELSE N' ' END


SET @Columns = LEFT(STUFF(@Columns, 1, 2,''), LEN(STUFF(@Columns, 1, 2,'')) -1)



        SET @Sql = N'SELECT ' + @Columns + ' FROM dbo.TableName'

        EXECUTE sp_executesql @Sql

END

Using a Table Valued Parameter

Table Type

CREATE TYPE dbo.ColumnNames AS TABLE
(
  ColumnName SYSNAME
)
GO

Procedure Definition

CREATE PROCEDURE usp_selectColumns_TVP
 @ColumnName AS dbo.ColumnNames READONLY 
AS
BEGIN
  SET NOCOUNT ON;
    DECLARE @Columns NVARCHAR(MAX);
    DECLARE @Sql NVARCHAR(MAX);

SELECT @Columns = STUFF((SELECT ', ' + QUOTENAME(ColumnName)
                         FROM @ColumnNames
                         FOR XML PATH(''),TYPE)
                         .value('.','NVARCHAR(MAX)'),1,2,'')

        SET @Sql = N'SELECT ' + @Columns + ' FROM dbo.TableName'

        EXECUTE sp_executesql @Sql

END

edited Sep 07 '14 at 16:03

answered Sep 07 '14 at 15:40

M.Ali

67,945
13
101
127

Thank you, but what if I might have 20 columns (This would look kind of messy..)? That's why I thought I could use table valued parameters somehow? – BornToCode Sep 07 '14 at 15:47
another twenty columns mean another twenty Case statements, Not messy at all, always indent you code nicely and no matter if you have thousands of rows in your code it will always look clean and easier to read. :) – M.Ali Sep 07 '14 at 15:53
@BornToCode have a look now I have update my answer to make use of a TVP and yes will decrease the line of code require to do the same thing :) – M.Ali Sep 07 '14 at 16:05
I'm sorry for nagging, but this just returned a null result, also how can this work for multiple (unknown number of) columns in addition to `ColumnName SYSNAME` – BornToCode Sep 07 '14 at 16:47
You Declare a variable of `dbo.ColumnNames` type , populate that variable with values (column names of your table), and finally pass that table variable to this procedure. – M.Ali Sep 07 '14 at 16:52
Thank you, you should just change `SELECT @Columns` into `SET @Columns` – BornToCode Sep 11 '14 at 12:42

Select columns based on dynamic table value parameters?

1 Answers1

Using a Table Valued Parameter

Table Type

Procedure Definition