求个存储过程 比较简单的

[luyuanshangao]

取出表A中Id为两位数的记录

取出表A中Id为四位数且前两位跟传进来的参数一样的记录

select * from A where len(id)=2
select * from A where len(id)=4 and left(id,2)=参数

拜托各位再帮忙把上面的两条sql写成个存储过程

参数 是ID
我的ID是01，0101，010101这样的分3级
如果传进来的ID参数ID=01 就找到0101 如果是0101 就找到010101

拜托了

[黄_瓜]

create proc p 
@s int
as 
begin 
select * from A where len(id)=2 
union all
select * from A where len(id)=4 and left(id,2)=@s 
end

[luyuanshangao]

那样不行吧 我是要一次取出一个ID级别的记录 比如说0101、0102这样的 不要取出01或010101的记录

[chuifengde]

create proc proc_test
    @id varchar(20)
as
    select * from [Table] where len(id)>len(@id) and left(id,2)=@id

[nianran520]

create proc proc_getdata @id varchar(30)
as
begin
	select * from tb
	where len(id) = len(@id)+2 and left(id,len(@id))=@id 

end

[--小F--]

create proc f
(
  @id varchar(20)
)
as
    select * from [Table] where len(id)>len(@id) and left(id,2)=@id

[luyuanshangao]

首先要判断传进来的Id是几位数的 如果是2位数的 那就要取Id为4位数的记录 且最前面两位必须是跟传进来的Id相同

[chuifengde]

create proc proc_test
    @id varchar(20)
as
    select * from [Table] where len(id) between len(@id) and len(@id)+3 and left(id,2)=@id

[itliyi]

顶

[百年树人]

create proc sp_test @s int
as 
begin 
  select * from tb where len(id)=len(@s)+2 and left(id,len(@s))=@s 
end

[黄_瓜]

[Quote=引用 2 楼 luyuanshangao 的回复:]
那样不行吧 我是要一次取出一个ID级别的记录 比如说0101、0102这样的 不要取出01或010101的记录
[/Quote]

create proc p 
@s int
as 
begin 
select * from A where len(id)=len(@s)
union all
select * from A where len(id)=len(@s)+2 and left(id,2)=@s 
end

[yjh53615728]

CREATE PROC sp_test(@ID INT)
AS
BEGIN
  SET NOCOUNT ON
  SELECT * FROM A WHERE ID=@ID OR (LEN(ID)=4 AND LEFT(LTRIM(ID),2)=@ID) OR (LEN(ID)=6 AND LEFT(LTRIM(ID),2)=@ID)
  SET NOCOUNT OFF
END

[yjh53615728]

[Quote=引用 9 楼 josy 的回复:]
SQL codecreateproc sp_test@sintasbeginselect*from tbwherelen(id)=len(@s)+2andleft(id,len(@s))=@send
[/Quote]

树哥正解

[luyuanshangao]

谢谢各位了