SELECT Query with Columns stored in a variable

Question

I have a stored procedure in which I am getting table's column which is needed for SELECT statement in a variable with comma separated. But It is not working for me.:

SP:

ALTER PROCEDURE [dbo].[SPGet_Assistance_Detail]
(
 @Id int,
 @UserDesig varchar(20) --User Desig can be 'FFR' or 'HO'
)
AS
 DECLARE @Cols VARCHAR(MAX)
 SELECT @Cols = Column_Listing FROM Role_Col_Mapping WHERE Tbl_Name = 'Assistance' and Role = @UserDesig
 SELECT @Cols FROM dbo.Assistance WHERE Service_Id=(@Id)
RETURN

Here Role_Col_Mapping has fields like: Tbl_Name, Role , and Column_Listing. It stores data of cols allowed to any user based on his role in any table. And Assistance is a table which columns' data I needed to display.

I am getting wrong output. Which is cols separated by comma.

Answer 1

You must use dynamic SQL - something like this:

ALTER PROCEDURE [dbo].[SPGet_Assistance_Detail]
(
 @Id int,
 @UserDesig varchar(20) --User Desig can be 'FFR' or 'HO'
)
AS
 DECLARE @Cols VARCHAR(MAX)
 SELECT @Cols = Column_Listing FROM Role_Col_Mapping WHERE Tbl_Name = 'Assistance' and Role = @UserDesig
 DECLARE @query NVARCHAR(MAX)
 SELECT @query = 'SELECT ' + @Cols + ' FROM CRM.dbo.Assistance WHERE Service_Id=' + cast(@Id AS VARCHAR(MAX))
 EXEC sp_executesql @query
RETURN

• Nenad Zivkovic: Comment EXEC and try PRINT @query first so see what you are getting before executing and tweak accordingly. I don't know what your @cols are, maybe there are commas at the end or beginning that need to be removed
• Dhwani: Data is like this: Service_Id,Request_Id,Vat_Tin_No,Bill_Invoice_Date,Created_Date,Entry_Done_By,Modified_Date,Executed_Date No error in data but still facing the same problem.
• Nenad Zivkovic: sqlfiddle.com/#!6/d41d8/4366 - Looking fine in example, please double check if you made any mistakes and/or post your exact query so it can be checked