0

I have a slight problem with getting records in my report made in vb6. my stored procedure looks like this 

create proc sp_rpt 
(@area varchar(20),
 @type varchar(20) )

 as 
 select * from tableA where (area=@area) and type in (@type)

That is what i tried, but i do not get my records. although if i replace @type with 'Commercial','Domestic' in SQL server itself i get perfect results. The string being passed from vb6 is in this form 'Commercial','Domestic' i have a variable in vb6 named categories .

categories="'commercial','Domestic'"

if you have an idea on how i should go about this please help.

bob269
  • 115
  • 1
  • 13
  • Passing comma separated values in a variable won't help you to achieve what you want. You either need to write table-valued function to split the string to retrieve values in the IN clause or use dynamic sql. Check this link for more information: http://stackoverflow.com/questions/3299141/t-sql-a-problem-while-passing-csv-string-into-a-stored-procedure – Harsh Sep 20 '15 at 14:45
  • @Harsh Thank you for a quick reply .. i am looking into it and will get back to you. Thanks – bob269 Sep 20 '15 at 14:51
  • See http://www.sommarskog.se/arrays-in-sql-2005.html. – Dan Guzman Sep 20 '15 at 16:03
  • @DanGuzman Thank you. its been helpful, i need an opinion on a way i thought i could do it as well. what if before the report is loaded i use a procedure to add the values to a temporary table and then they are selected from that temporary table to show up in the `where in()` statement – bob269 Sep 20 '15 at 19:11
  • You could use a temp table but be aware of the scope. The app would need to create the temp table, insert data (direct inserts or proc), and then execute the report proc all on the same connection. Personally, I'd pass the list as an XML parameter, one of the techniques described in Erland's article. If you were using VB.NET, table-valued parameters would be best. – Dan Guzman Sep 20 '15 at 19:27
  • Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – James Z Oct 10 '15 at 20:59

0 Answers0