CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdynamic-columns
Alexandr Kapshuk
Alexandr Kapshuk

Reputation: 1471

SQL: select different columns to be returned from a stored procedure

Let's say I have a table mytable with several columns (this is not entirely true, see Edit):

mytable
id    data        description          created_at      viewed_times
1      10        'help wanted'      '20180101 04:23'       45
2      20    'customer registered'  '20180504 03:12'        1
...

I created a stored procedure that returns data from this table. The problem is that sometimes I need it to return only id and data columns, and sometimes I need additional information, like description, created_at etc.

My idea is to create three dummy variables @display_description, @display_created_date, @display_viewed_times. When a dummy variable equals 1, I display corresponding column. For example, for command

exec my_procedure @display_description = 1

I expect output

id    data        description        
1      10        'help wanted'      
2      20    'customer registered'  
...

How I implement it in the procedure is:

if @display_description = 1
    select id, data, description from mytable
else 
    select id, data from mytable

The problem is, if I want to have 3 switches (one for each column), I have to write 8 conditions in my if statement. (My select statement is a complex one. In addition, some of the columns like viewed_times have to be calculated, if I want them displayed. Therefore, writing many if statements makes the query very clumsy, and I want to avoid that)

Is there an easy way to select columns based on the switches? And if no, what approach would you recommend to return different number of columns from a stored procedure?

Edit: Sorry, I sad that the table mytable already exists with all columns. That is not true, some of the columns have to be calculated before displayed. For example, column viewed_times doesn't exist. To display it, I'll need to do the following:

if @display_description = 1
    ~ several selects to calculate the column viewed_times ~
    select id, data, description from mytable join table in which I just calculated the viewed_times column
else 
    select id, data from mytable

Calculating these columns is time consuming, and I would like to do that only if I need those columns displayed.

That's why dynamic SQL will probably not work

Update: I accepted the answer with Dynamic SQL, but what I did is the following:

if @display_viewed_times = 1
begin   
    ~ calculate column viewed_times ~
    update my_table 
    ~ add column viewed_times to the table my_table ~
end

for each of the optional columns. Then I return the table using

select * from my_table

which gives me different number of columns depending on the swithces

Upvotes: 1

Views: 2378

Answers (2)

Daniel Bürckner
Daniel Bürckner

Reputation: 363

I would change the stored procedure to a table valued function and modify the outer "select from".

So for example:

if($value1 === 1)
  $sql = "SELECT c1, c2 FROM dbo.my_udf";
elseif($value1 ===2)
  $sql = "SELECT c1, c3, c4 FROM dbo.my_udf";

Upvotes: 1

Thom A
Thom A

Reputation: 95534

One method would be the use Dynamic SQL. This is pseudo-SQL, as there an absence of information here to achieve a full answer. It's also untested, as i don't have much/any data to really run this against.

--Your input parameters
DECLARE @description bit, @createdate bit, @viewedtimes bit /*...etc...*/;

--Now the SP
DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT ' +
           STUFF(CONCAT(N','+ NCHAR(10) + N'       ' + CASE WHEN @description = 1 THEN QUOTENAME(N'description') END,
                        N','+ NCHAR(10) + N'       ' + CASE WHEN @createdate = 1 THEN QUOTENAME(N'created_at') END,
                        N','+ NCHAR(10) + N'       ' + CASE WHEN @viewedtimes = 1 THEN QUOTENAME(N'viewed_times') END),1,9,N'') + NCHAR(10) +
           N'FROM YourTable' + NCHAR(10) + 
           N'WHERE...;';
PRINT @SQL; --your best debugging friend.
EXEC sp_executesql @SQL /*N'@someParam int', @someParam = @inParam*/;

Ensure that you properly parametrise your query when you use Dyanmic SQL. Don't concatenate your string!!!

For example, the correct format would be N'WHERE yourColumn = @yourParam AND OtherColumn = @otherParam' and then provide the values of @yourParam and @otherParam in sp_executesql (as I have demonstrated within the comment).

Don't, however, do something like: N'WHERE yourColumn = ''' + @myParam + N''' AND otherColumn = ' + CONVERT(varchar(5),@secondParam). This would be open to SQL injection (which is not your friend).

Upvotes: 1

Related Questions