Converting table-valued function to dynamic SQL

• Robert Frasca

  SSCertifiable

  Points: 6027

  More actions

  January 14, 2011 at 10:01 am

  #97632

  I've got a stored procedure that has 19 different parameters, all of them optional. The query that uses these parameters is incredibly slow because of all the logical or's necessary to test for the presence of each parameter.

  I've re-written the proc to generate the query dynamically based on the parameters that are actually passed and it works great. The queries are running in a fraction of the time they used to. I have one last annoying problem.

  One of the parameters can be a comma delimited string, i.e. 'ABC','DEF','GHI' etc. The existing code uses a table-valued function to parse the comma-delimited string. The following line of code is what I'm having trouble converting to dynamic sql.

  WHERE X_Column IN (SELECT parameter FROM fn_getParmTable(@input_parameter))

  Note: The function fn_getParmTable works just fine. (Parameter is the table variable returned by the function.)

  How do I convert the SELECT between the parens into dynamic sql?

  Is it even possible, i.e. should I try some other strategy to parse that comma delimited string into a variable prior to generating the dynamic SQL?

  According to Erland Sommarskog's article, "The Curse and Blessings of Dynamic SQL", this is one of the scenarios where you shouldn't use dynamic SQL but I don't really have an option.

  I'm aware of the risk of SQL Injection but it isn't a concern in this case.

  I'll probably end up going with an alternative strategy but I was hoping someone had solved this problem before.

  "Beliefs" get in the way of learning.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 14, 2011 at 12:32 pm

  #1273741

  Why do you want to replace this? Is it causing some performance problem?

  WHERE X_Column IN (SELECT parameter FROM fn_getParmTable(@input_parameter))

  Trying to turn the the delimited parameter list into dynamic SQL is asking for SQL Injection.

  I would suggest loading the output from the function into a declared table inside the dynamic SQL. It is more likely to give you a good query plan. You could also load the function output into a temp table (#temp) created and loaded outside the dynamic SQL call.

  declare @Tmp table

  (MyParam int (...or other data type...) not null primary key clustered)

  insert into @Tmp (MyParam)

  select distinct parameter FROM fn_getParmTable(@input_parameter)) order by parameter

  ... select statement ...

  where X_Column IN ( select MyParam from @Tmp)