如果只要前1000个: Select top 1000 * From Customers如果有条件,若ID值有规则,就不要那么多OR了 如:要找第一个字母在B到C之间的ID Select * From Customers where CustomerId>'B' and CustomerId<='D'
错了,应是 Select * From Customers where CustomerId>='B' and CustomerId<'D' 没必要从头“OR”到尾,查找总是有规律的,否则就是表设计不好
CREATE FUNCTION SelectCustomers (@str as varchar(8000) ) RETURNS TABLE RETURN (select * from Customers where charindex('['+CustomerId+']','['+replace(@str,',','][')+']')>0)运行时用: select * from dbo.SelectCustomers(ALFKI,ANATR) 就可以了。
笔误,改改: select * from dbo.SelectCustomers('ALFKI,ANATR')
oh ,The 1000 is how many customerid ,SelectCustomers 1000 ALFKI,ANATR,....1 if number id of the string is number you need,don't need numbercreate proc SelectCustomers @queried varchar(3000) AS begin declare @str varchar(7000) set @str=replace(@queried,',',''' or CustomerId=''') exec('select * from customers where CustomerId='''+@str+'''') return end2 if number you need is shorter than numner ID of the stringcreate proc SelectCustomers @num int, @queried varchar(3000) AS begin declare @str varchar(7000),@i int set @i=1 while @i<@num and charindex(',',@queried)<len(@queried) begin set @queried=stuff(@queried,charindex(',',@queried),1,''' or CustomerId=''') set @i=@i+1 end set @str=left(@queried,charindex(',',@queried)-1) exec('select * from customers where CustomerId='''+@str+'''') return end3 if your string is long than 8000 chars,you need cut it two strings,or put it into a table,read from table to query
exec northwind.dbo.selectcustomers 5, 'white,jane,ALFKI,ANATR,lius,queed,yark,jim' only matached id ALFKI,ANATR was returned
Select top 1000 * From Customers如果有条件,若ID值有规则,就不要那么多OR了
如:要找第一个字母在B到C之间的ID
Select * From Customers where CustomerId>'B' and CustomerId<='D'
Select * From Customers where CustomerId>='B' and CustomerId<'D'
没必要从头“OR”到尾,查找总是有规律的,否则就是表设计不好
(@str as varchar(8000) )
RETURNS TABLE
RETURN (select * from Customers where charindex('['+CustomerId+']','['+replace(@str,',','][')+']')>0)运行时用:
select * from dbo.SelectCustomers(ALFKI,ANATR)
就可以了。
select * from dbo.SelectCustomers('ALFKI,ANATR')
@queried varchar(3000)
AS
begin
declare @str varchar(7000)
set @str=replace(@queried,',',''' or CustomerId=''')
exec('select * from customers where CustomerId='''+@str+'''')
return
end2 if number you need is shorter than numner ID of the stringcreate proc SelectCustomers
@num int,
@queried varchar(3000)
AS
begin
declare @str varchar(7000),@i int
set @i=1
while @i<@num and charindex(',',@queried)<len(@queried)
begin
set @queried=stuff(@queried,charindex(',',@queried),1,''' or CustomerId=''')
set @i=@i+1
end
set @str=left(@queried,charindex(',',@queried)-1)
exec('select * from customers where CustomerId='''+@str+'''')
return
end3 if your string is long than 8000 chars,you need cut it two strings,or put it into a table,read from table to query
only matached id ALFKI,ANATR was returned