0

I need to have a sqldatasource with the following sql in it.

if @filter = 'departments'
begin

SELECT ISNULL(DocTitle,'') as Name, DocNumber as id, DocUrl+DocName AS link,LastModBy,LastModDate, IsLink 
FROM cmc.CMC_Docs d 
INNER JOIN CMC.CMC_Doc_Locations as l on l.FamilyID = d.FamilyID 
INNER JOIN CMC.CMC_DocFamilies df on df.FamilyID = d.FamilyId 
WHERE IsEnabled=1 
AND ISNULL(DocName,'') <> '' 
AND d.FamilyID IN @dep  
ORDER by DocTitle

end

where @dep is something like (2,3)

However when I try to test the query I get an error saying incorrect syntax near @dep.

Any ideas how I need to write this inside of the datasource in order for it to work?

Thanks,

cillierscharl
  • 7,043
  • 3
  • 29
  • 47
user541597
  • 4,247
  • 11
  • 59
  • 87
  • as far as im aware this would only be possible with a dynamic sql query concatenated from strings. Or, have a nested query that could do the filter for you. – cillierscharl Nov 21 '12 at 20:18
  • try:SELECT ISNULL(DocTitle,'') as Name, DocNumber as id, DocUrl+DocName AS link,LastModBy,LastModDate, IsLink FROM cmc.CMC_Docs d INNER JOIN CMC.CMC_Doc_Locations as l on l.FamilyID = d.FamilyID INNER JOIN CMC.CMC_DocFamilies df on df.FamilyID = d.FamilyId WHERE IsEnabled=1 AND ISNULL(DocName,'') <> '' AND d.FamilyID IN ( @dep ) ORDER by DocTitle – sjramsay Nov 21 '12 at 20:22
  • If you are trying to have a parameterized in clause - [This post](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) may be helpful. – Goose Nov 21 '12 at 20:23
  • Goose's comment is correct. The approach I take is David Basarab's answer. I like it because it is SQL based - http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause/337752#337752 – MikeSmithDev Nov 21 '12 at 21:49

1 Answers1

0

do you need to put this in ()?

ex: select * from product where productid in (1,2,3) works
ex: select * from product where productid in 1,2,3 - does not work

sjramsay
  • 555
  • 1
  • 5
  • 12