Comparison Operator With Local Variable
-
I'm developing a stored procedure that accepts a string as an input parameter. I want to use that parameter with the "IN" comparison operator, but I'm having difficulty (hopefully just syntax).
An example of what I'm trying to do using Northwind is as follows:
DECLARE @Parm varchar(100)
SET @Parm = 'VINET, TOMSP , VICTE' -- (comma separated list as user would input)
- -- Below I've tried to massage the string to look like the standard format for an "IN"
- -- comparison operator.
SET @Parm = '''' + @Parm + ''''
SET @Parm = replace(@Parm , ' ', '')
SET @Parm = replace(@Parm , ',', ''',''')
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM Orders
WHERE (CustomerID IN (@Parm))
This returns zero records.
I can do this with dynamic SQL, but I'd rather not. Any other ideas?
I Only Work Here...... -
It's works for me in a different database as I don't have northwinds in my sqlserver box.
Check the data exists
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM Orders
WHERE CustomerID IN ('VINET', 'TOMSP' , 'VICTE' 
Thanks,
Ganesh
-
I do not have Northwind (gotta save that space...), so I plopped your code into Dynamic SQL to see what you produce. Other than the extra paren, this looks excellent.
Are you sure CustomerID has these values?
DECLARE @Parm varchar(100),
@SQL varchar(1000)
SET @Parm = 'VINET, TOMSP , VICTE'
SET @Parm = '''' + @Parm + ''''
SET @Parm = replace(@Parm , ' ', '')
SET @Parm = replace(@Parm , ',', ''',''')
SELECT @SQL = 'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE( CustomerID IN( ' + @Parm + '))'
PRINT @SQL
Output:
SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE( CustomerID IN( 'VINET','TOMSP','VICTE'))
I wasn't born stupid - I had to study.
-
I know the dynamic SQL will work. But I haven't been able to get it to work without building the SQL string. I was hoping to avoid the dynamic SQL. One reason is that my query is long (well in excess of the 8000 character limit).
I Only Work Here...... -
I would suggest another approach.
If I am interpretting this correctly, SQL is interpretting your @Parm variable as a single value. Hence, the code does not error, but also will not bring back any records.
I used our data and did a simple test breaking down @Parm into @Parm1, @Parm2, and @Parm3. This will retrun values, (without having to use your code to insert single quotes).
You may want to look into using CHARINDEX to determine how many values are passed in; maybe use the comma to count and add one more for the final value. Then, you will ned to generate that many variables to contain your values. That can be tricky because it is easy to loss the scope of this spid if you try and generate @Variables dynamically - been there
Hopefully someone has tried to tackle this before and will give you a better answer than I can. I would also recommend you try searching this site for others who have asked similar questions.
Good luck
I wasn't born stupid - I had to study.
-
This works for me.
Declare @Names as varchar(8000)
set @Names = 'SysObjects, SysIndexes'
--this is in your stored proc :
set @Names = ',' + Replace(@Names, ' ', '') + ','
Select Id, Name, XType from dbo.SysObjects where charindex (',' + Name + ',' , @Names, 1) > 0
Please note that this script assume that the comma and the space isn't gonna come up in any of the ids
However if you think that you may go well over the 8000 limit of the varchar you may consider creating a Table like this :
WantedOrders
Order_id
User_id (that requests them so multiple users can use this table)
then you can simply join to that table and get the orders you want. However this forces you to make a big ( or bulk) insert then a join and then a delete to complete the task, while the in() method can simply select but is limited in quantity. I am unaware of the requirements of your software but you may play with both solutions and see which fits better under different loads of ids to list.
-
Thank all of you for your replies. I ended up going the dynamic SQL route. The query was originally well over 8000 characters but was a UNION query. I busted up the query and it worked fine.
Thanks again...
I Only Work Here...... -
You can also do this using like :
DECLARE @Parm varchar(100)
SET @Parm = 'VINET, TOMSP , VICTE' -- (comma separated list as user would input)
-- Below I've tried to massage the string to look like the standard format for an "IN"
-- comparison operator.
SET @Parm = '''' + @Parm + ''''
SET @Parm = replace(@Parm , ' ', '')
SET @Parm = replace(@Parm , ',', ''',''')
SET @Parm = ',' + @Parm + ',' -- add start and end ","
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM Orders
WHERE @Parm like '%,''' + CustomerID + ''',%'
Bert
-
Another method (that also allows you to not use dynamic SQL) is to take the in parameter, and instead of tokenize it, insert into a temptable or tempvariable, then join against that. Not the prettiest, but still - you don't need to do the dynamic boogie
/Kenneth
-
there is a handy UDF function called SPLIT on this site that I use for this purpose;
http://www.sqlservercentral.com/scripts/contributions/835.asp
it accepts two parameters, the text and the delimiter to split the text on.
it returns a single column table with the split values.
I often call the split function in stored procedures; an example is:
declare @parm varchar(200)
set @parm='sysobjects,sysindexes,syscolumns'
select name,xtype from master.dbo.sysobjects where name in (select * from dbo.split(@parm,','))
results:
sysobjects S
sysindexes S
syscolumns S
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,
Thanks a bunch. The Split function is exactly what I was looking for. Works great!
I Only Work Here......
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply