0

I have a stored procedure which has a parameter @SomeFilterIds, which takes in comma separated integer ids. If this parameter is not NULL it is translated into something like this:

AND [X] IN(1, 2, 4)

and assigned to @SomeFilter

I then used something along those lines:

SET @Sql = N' ...WHERE    
 c.SomeDate >= @SomeDate
' + @SomeFilter

and:

SET @ParameterDefinition = N'@SomeDate DateTime';
EXEC sp_executesql 
@Sql
,@ParameterDefinition
,@SomeDate = @SomeDate

I would think that this is not best practice and opens potential security holes. Is this correct? Can this be improved? Thanks.

cs0815
  • 16,751
  • 45
  • 136
  • 299
  • Do you really need a dynamic sql? if passing a comma delimited string of values is the only reason for this, then there are better ways such as using a table valued parameter. – Zohar Peled Jun 03 '15 at 12:07
  • I need it for other reasons too. – cs0815 Jun 03 '15 at 12:52
  • Ok, but do you **really** need it? dynamic sql should be the last resort, as it's both dangerous and slow. perhaps someone might be able to show you a way to avoid it if you can share the relevant details. – Zohar Peled Jun 03 '15 at 12:59
  • Zohar please be so kind and provide an alternative (answer). Strong requirement sproc receives @SomeDate (DataTime) and comma separated list of integer Ids (see example). – cs0815 Jun 03 '15 at 13:44
  • [Read this](http://stackoverflow.com/a/24881759/3094533) to see how to pass a table valued parameter to a stored procedure from c#, but I can't give you an exact answer without knowing what you actually need to do and what code is executing the stored procedure. – Zohar Peled Jun 03 '15 at 18:02

1 Answers1

1

I think instead of @SomeFilterIds as varchar parameter you can use XML type variable and then use Inner join your main table with this XML variable. This will avoid dynamic query execution and will be be safer.

Example:

--Instead of comma separated ID use below XML
declare @xml xml = '<row><ID>1</ID></row><row><ID>3</ID></row>'

--Assume this is your other table
declare @YourTable table (ItemId int, ColA varchar(20))

insert @YourTable
select 1, 'Hello World'


--Joining both the tables

select  col.value('data(ID[1])', 'int') as ID
from    @xml.nodes('/row') tbl(col) 
inner join @YourTable t2 
on t2. ItemId=tbl.col.value('data(ID[1])', 'int')
--WHERE  c.SomeDate >= @SomeDate
Sagar Shirke
  • 648
  • 9
  • 32
  • Can u please provide example? – cs0815 Jun 03 '15 at 09:57
  • Thanks for the update. I really do not understand what this has to do with my question and sp_executesql. Please also not that I do the filtering in where. – cs0815 Jun 03 '15 at 10:53
  • Basically I am suggesting you to avoid dynamic query.I am assuming you are using sp_executesql because of list of comma separated Ids.So by using IDs in XML you can avoid dynamic query. And if you see my example I have mentioned where condition also but it is commented.That is the place where you can write your other conditions. – Sagar Shirke Jun 03 '15 at 11:00