Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GUID
Starting Member
3 Posts |
 Posted - 2005-04-20 : 19:52:07
|
Apologies for length below... I hope it reads ok. I'm trying to create a SPROC as basis for advanced Search Page on a site. I've pasted it below.Issue is... need to pass ItemCategoryID and ManufacturerID as the subject of IN clauses, so they end up as "IN (1,2,3)" etc, the values come from a CheckBoxList. I got it working in the Query Analyzer Debugger, without the ManID, but adding the ManID, I get an error, because this column is an 'int' in the table and it won't let me input anything other than a single integer, even though I've declared the parameter as NVarChar. I had same prob with ItemCatID, but I changed the field to NVarChar in the table, but don't want to have to do same with ManID.Firstly... is there a way around this? Then I just need some help with correctly placing apostrophes so the final statement is well formed, because thats a headache in itself.ALTER Procedure cc_Search( @Search nvarchar(255), @ItemCategoryID nvarchar(10), @ManufacturerID nvarchar(10))ASBEGIN DECLARE @SQL varchar(5000) SET @SQL = 'SELECT ProductID, ItemCategoryID, ProductName, Dimension, UnitCost, ProductImage, cc_Manufacturer.ManufacturerIDFROM cc_ProductsINNER JOIN cc_Patterns ON cc_Products.PatternID = cc_Patterns.PatternIDINNER JOIN cc_Manufacturer ON cc_Patterns.ManufacturerID = cc_Manufacturer.ManufacturerIDWHERE ItemCategoryID in ' + @ItemCategoryID + ' AND (cc_Manufacturer.ManufacturerID in ' + @ManufacturerID + ') AND (ProductID LIKE ' + @Search + 'OR ProductName LIKE ' + @Search + ' OR ProductDescription LIKE ' + @Search + ')' EXEC(@SQL)END |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-20 : 20:12:50
|
| What are you passing? if it's the string '1,2,3,4'thenWHERE ItemCategoryID in (' + @ItemCategoryID + ') AND cc_Manufacturer.ManufacturerID in (' + @ManufacturerID + ') AND...You have the bracket in the wrong place.You might be better off parsing the values into a temp table and joining to it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
GUID
Starting Member
3 Posts |
Posted - 2005-04-20 : 20:31:06
|
| Right. So I was passing the brackets in with the variable, when I was creating the string in the code. Your way looks better, so I've changed that.But the issue I'm getting now is with Data Types. ManufacturerID is an integer, but it doesn't seem to let me use the IN keyword with it. When running SPROC in Query Analyzer, and the dialog box asks me to input the values, I put 10,1,12 and pops up not valid.I've tried changing the variable declaration datatype to int or nvarchar...Thanks btw |
|
|
|
GUID
Starting Member
3 Posts |
Posted - 2005-04-20 : 20:58:23
|
| Looks like it's working. I'd been over zealous with trying to pass apostrophes and brackets and everything bar the kitchen sink.Though it still wouldn't work in Query Analyzer Debugging... looks like a bug with the debugger!Anyway... cheers... I owe ya. |
|
|
|
|
|
|
|
|
Loadsa Problems...