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?

Answer 1

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