July 20, 2006 at 9:24 am
Can anyone tell me what the problem is with the following Query #3?
Query #1 - Works - Returns records
@myIDParam = 019988
SELECT * FROM myTable WHERE (myID IN @myIDParam)
Query #2 - Works - Returns records
@myIDParam = 019999
SELECT * FROM myTable WHERE (myID IN @myIDParam)
Query #3 - Does not return any records
@myIDParam = 019988, 019999
SELECT * FROM myTable WHERE (myID IN @myIDParam)
Thanks!
July 20, 2006 at 9:40 am
SELECT * FROM myTable a
join select Value from Split(@myIDParam,',') b
on a.myID = b.Value
Refer:
http://www.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=850
July 21, 2006 at 3:20 am
July 21, 2006 at 8:22 am
Thanks to all for the responses and suggestions. I tried using the following query but still get a syntax error:
QUERY:
SELECT * FROM myTable
WHERE (myID IN (SELECT Item FROM dbo.SPLIT(@myIDParam, ',') AS SPLIT_1))
ERROR Msg:
"Invalid object name 'dbo.SPLIT'."
If I remove 'dbo.' and 'AS SPLIT_1' it is automatically reinserted into the statement.
What am I doing wrong?
Thanks!
July 21, 2006 at 9:28 am
did you create the split function, that myt be the coz of the error
goto this url http://weblogs.asp.net/pleloup/archive/2003/04/14/5569.aspx
July 21, 2006 at 12:57 pm
Thank you for the response. No I didn't create the stored procedure and another problem is that I don't have permission to create a stored procedure on the datasource that I'm using. Is using this 'SPLIT' stored procedure the only way to accomplish this?
July 21, 2006 at 6:03 pm
You could perform the split yourself, something like the following...
declare
@input varchar(1000)
set
@input = '1,3'
create
table #temp (id int primary key identity(1,1), [text] varchar(100))
insert
into #temp ([text])
values
('test')
insert
into #temp ([text])
values
('wee')
insert
into #temp ([text])
values
('blah')
declare
@values table (id int)
declare
@id int
while
charindex(',', @input) > 0
begin
begin try
set @id = convert(int, substring(@input, 1, charindex(',', @input) - 1))
end try
begin catch
-- failed to convert the substring to an integer, do not add the
-- value to the table variable of values
set @id = 0
end catch
set @input = substring(@input, charindex(',', @input) + 1, len(@input) - charindex(',', @input))
-- if a valid id was found, insert it into the table variable of values
if @id > 0
insert into @values values (@id)
end
-- add the last id (the string no longer contains any ,'s)
begin
try
set @id = convert(int, @input)
insert into @values values (@id)
end
try
begin
catch
-- do nothing
end
catch
select
* from #temp where id in (select id from @values)
drop
table #temp
This could also be done with some dynamic sql, but be wary of sql injections of course.
July 22, 2006 at 10:17 am
Thanks for the response! Do you mean that this type of coding should be place directly into my query?
Thanks
July 23, 2006 at 4:15 am
using some of the code posted by william you can put the code in a table valued function and then use that in your stored proc
-------------
CREATE FUNCTION Split (@input varchar(1000))
RETURNS @values table (id int)
as
begin
declare @id int
while charindex(',', @input) > 0
begin
set @id = convert(int, substring(@input, 1, charindex(',', @input) - 1))
set @input = substring(@input, charindex(',', @input) + 1, len(@input) - charindex(',', @input))
-- if a valid id was found, insert it into the table variable of values
if @id > 0
insert into @values values (@id)
end
-- add the last id (the string no longer contains any ,'s)
set @id = convert(int, @input)
insert into @values values (@id)
return
end;
-----
implement your Query #3 like so:
declare @myIDParam varchar(1000)
@myIDParam = '019988, 019999'
SELECT * FROM myTable WHERE (myID IN Split(@myIDParam))
--
take care to change the datatype of the column being return from the function and hopefully this solves your problem
July 23, 2006 at 7:20 am
Thank you for the response! But again, I don't have permission to create functions or stored procedures on the datasource that I'm using. What I'm trying to find out now is whether using this 'SPLIT' stored procedure is the only way to accomplish this?
Thanks
July 23, 2006 at 10:25 am
DECLARE @idoc int
DECLARE @doc varchar (1000)
SET @doc =''
--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/list',1)
WITH (id varchar(10))
-- Clear the XML document from memory
EXEC sp_xml_removedocument @idoc
*-------------
i have forgotten how to stick the xml on this site so the structure is
@doc = !ROOT!!list id="092000"/!!list id="205634"/!/ROOT!
please replace the ! with the appropriate
--- you can then use the select stmt as part of the sub query for your stored proc
July 23, 2006 at 11:00 am
The code that I posted is essentially the split function, so you could just include that in your query if you cannot create stored procedures or functions. You could also create dynamic sql such as the following:
declare @sql varchar(1000)
set @sql = 'SELECT * FROM myTable WHERE myID IN (' + @myIDParam + ')'
exec(@sql)
This assumes @myIDParam is a varchar, if not simply cast / convert it. Also you would need to be wary of sql injection using this method, so put in some kind of validation of the @myIDParam before (such as checking for any semicolons).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply