Use SQL Statement With String

Question

I have a products table and i need to concat my string and my sql statements. Is there any way?

My purpose is that i want to define column names just one time in a string variable and i will use it alot of times. Otherwise my sql statements has alot of column name and it complex my code.

For example, i use this

DECLARE @MyStr NVARCHAR(MAX) = 'ProdId,ProdName'
SELECT TOP 10 @MyStr FROM Products

Result is here

---

But i need the result as this.

Answer 1

You'll need to use dynamic SQL here. I also suggest you fix your design and don't store delimited data, and ideally use a table type parameter. This would look like the following:

DECLARE @Columns table (ColumnName sysname);
INSERT INTO @Columns (ColumnName)
VALUES(N'Column1'),(N'Column2');

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SELECT @SQL = N'SELECT ' + STRING_AGG(QUOTENAME(ColumnName),N',') + @CRLF + 
              N'FROM dbo.Products;'
FROM @Columns;

PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL;

If you don't want to use a table type, you can use STRING_SPLIT:

SELECT @SQL = N'SELECT ' + STRING_AGG(QUOTENAME([Value]),N',') + @CRLF + 
              N'FROM dbo.Products;'
FROM STRING_SPLIT(@Columns,',');