5

Lets say I have a table that actually store record which looks like

Create Table dbo.Info
(
 SN int primary key identity(1,1),
 FirstName nvarchar(50) not null,
 LastName nvarchar(50) not null,
 Gender char(1) default 'M',
 Age int check(Age>0)
)

Now for reporting purpose I only need this table column name and save in another table

Create Table Report.InfoColumnOrder
(
 SN int primary key identity(1,1),
 UserId int,
 ColumnName nvarchar(100)
)

thus record looks like

1, 1, FirstName
2, 1, LastName
3, 1, Gender
4, 2, LastName
5, 2, FirstName
6, 2, Age
7, 2, Gender

Now when user with id 1 login and wants report from table Info select command should look like

Select FirstName, LastName, Gender from dbo.Info

whereas for a user with id 2 select command will be

Select LastName, FirstName, Age, Gender from dbo.Info

Since, user can order the column from client side select statement should be dynamic. Is it possible?

4

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks.

DECLARE @qry NVARCHAR(MAX) =
'SELECT ' + STUFF((
    SELECT ', ' + QUOTENAME(c.name) 
    FROM Report.InfoColumnOrder r
    JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info')
    WHERE u.UserId = @user
    ORDER BY r.SN
    FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,'') +
 ' FROM dbo.Info'
;
EXEC sp_executesql @qry, N'@user int', @user = @userid;

Or something like that - I'm writing this on my phone...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.