SQL Server stored procedure select specific columns passed by arguments, and check other constraints

Question

I want to create a stored procedure in SQL Server 2017 and let it be called somewhere else (i.e., Python). It accepts three parameters, stkname - stock name, stktype - stock type, colname - output columns (only these columns are returned). @colname is a varchar storing all required column names separated by ,.

If @colname is not specified, return all columns (*)
If stkname or stktype is not specified, do not filter by it

This is my code so far:

create procedure demo 
     (@stkname varchar(max), 
      @colname varchar(max), 
      @stktype varchar(max)) 
as
begin
    ----These lines are pseudo code -----
    select 
        (if @colname is not null, @colname, else *) 
    from 
        Datatable 
    (if @stkname is not null, where stkname = @stkname)
    (if @stktype is not null, where stktype = @stktype)

    ---- pseudo code end here-----
end

The desired result is that

exec demo @colname= 'ticker,price', @stktype = 'A'

returns two columns - ticker and price, for all records with stktype = 'A'

I could imagine 'dynamic SQL' would be possible, but not that 'elegant' and I need to write 2*2*2 = 8 cases.

How can I actually implement it in a better way?

PS: This problem is not a duplicate, since not only I need to pass column names, but also 'generate a query by other parameters'

You will need dynamic SQL. You want an "elegant" solution for a very non-elegant problem. "Just select whatever columns you like, and add any where causes you like" is not exactly elegant. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 31, 2018 at 19:31
@Sami Any possibilities I still use dynamic SQL but not write 8 separate cases? — MTANG
– MTANG, Commented Oct 31, 2018 at 19:32
This has design flaws in capital letters all over the place. — Sean Lange
– Sean Lange, Commented Oct 31, 2018 at 20:05
I was talking about you claiming I was making a straw man argument. But your claim could also be straw man argument. In the case of this question it needed to be said that the real issue is the design, not the solution they are trying to achieve. Also commonly known as an xy problem. The link you posted is absolutely relevant and I agree that it is basically the same question. :) — Sean Lange
– Sean Lange, Commented Oct 31, 2018 at 20:39

Aaron Bertrand · Accepted Answer · 2021-12-28 03:02:21Z

4

You need dynamic SQL, but you don't need to write a case for every permutation, and it's really not all that hard to prevent malicious behavior.

CREATE PROCEDURE dbo.demo -- always use schema prefix
  @stkname  varchar(max)  = NULL,  
  @colnames nvarchar(max) = NULL, -- always use Unicode + proper name
  @stktype  varchar(max)  = NULL
AS
BEGIN
   DECLARE @sql nvarchar(max);

   SELECT @sql = N'SELECT ' 
       + STRING_AGG(QUOTENAME(LTRIM(RTRIM(x.value))), ',') 
     FROM STRING_SPLIT(@colnames, ',') AS x
     WHERE EXISTS 
     (
        SELECT 1 FROM sys.columns AS c 
        WHERE LTRIM(RTRIM(x.value)) = c.name
        AND c.[object_id] = OBJECT_ID(N'dbo.DataTable')
     ); 

   SET @sql += N' FROM dbo.DataTable WHERE 1 = 1'
     + CASE WHEN @stkname IS NOT NULL THEN 
            N' AND stkname = @stkname' ELSE N'' END
     + CASE WHEN @stktype IS NOT NULL THEN 
            N' AND stktype = @stktype' ELSE N'' END;

  EXEC sys.sp_executesql @sql,
     N'@stkname varchar(max), @stktype varchar(max)',
     @stkname, @stktype;
END

It's not clear if the stkname and stktype columns really are varchar(max), I kind of doubt it, you should replace those declarations with the actual data types that match the column definitions. In any case, with this approach the user can't pass nonsense into the column list unless they somehow had the ability to add a column to this table that matched their nonsense pattern (and why are they typing this anyway, instead of picking the columns from a defined list?). And any data they pass as a string to the other two parameters can't possibly be executed here. The thing you are afraid of is probably a result of sloppy code where you carelessly append user input and execute it unchecked, like this:

SET @sql = N'SELECT ' + @weapon_from_user + '...';

For more on malicious behavior see:

edited Dec 28, 2021 at 3:02

answered Oct 31, 2018 at 19:39

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MTANG Over a year ago

Thanks! I forgot the 'where 1 = 1' trick to prevent writing cases for using 'where' or 'and'. However the @colname in my case is not a single name but might be many column names separated by ',' (Or I can change the input formatting for different separators)

Aaron Bertrand Over a year ago

@MTANG that's a terrible parameter name then.

MTANG Over a year ago

Yes, maybe 'colnames' would be more meaningful here @Aaron Bertrand

Elaskanator Over a year ago

Just split the parameter value and do a left exclusive join on sys.columns to check for illegal values in the parameter string. I have had to implement such a validation for dynamic sorting queries (without hardcoding a big nasty CASE statement for each use).

MTANG Over a year ago

@AaronBertrand This works perfectly! Just for curiosity, why stkname and stktype are added after EXEC sys.sp_executesql (but not the colname)

|

Collectives™ on Stack Overflow

SQL Server stored procedure select specific columns passed by arguments, and check other constraints

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related