2

I am trying to implement a user customizable search and finally I got to the SQL part. The user can select a variable number of criteria and I send everything to the stored procedure in a table-valued parameter. I figured out the logic on how to create the sql string, but I am stuck at the part where I send the parameter. A pseudo code example would be :

foreach parameter in the table-valued parameter
   SqlString = SqlString + '@parameter'
   ParamList= ParamList + '@parameter'

EXEC sp_executesql SqlString , ParamList, ???

My question is how do I set up the parameters, when I don't know how many they are?

Oana Marina
  • 277
  • 3
  • 19

1 Answers1

0

Populate your table variable with Column Name, Operator, and Value. Then you can iterate through it and build a where clause. Then append the where clause to your sqlstring parameter.

Declare @WhereClause varchar(max)
set @WhereClause = 'Where 1=1'

... then loop through the table ...

Select @WhereClause = @WhereClause + ' and ' + ColumnName + ' ' + Operator + ' ' + Value
From @tableVariable

Append where clause to the select statement and execute 

EXEC sp_executesql @SqlString + @WhereClause
bsivel
  • 2,821
  • 5
  • 25
  • 32
  • 1
    That was the main idea, but in order to prevent sql injection I need to add the value as a parameter, not just concatenate it with the main string. And if i do that then I need to add parametersto the Exec commands... – Oana Marina Oct 16 '13 at 02:09