一个用户表ID UserName Code1 aaa 0012 bbb 0023 ccc 0084 ddd 009哪位朋友帮我写个存储过程或者函数就是返回code值,比如:假如当前传过来的参数code是002,那么生成的字符串就是003 .假如当前传过来的参数是007,那么生成的字符串是010code是参数。如果可以传入表名变量,字段变量 的更好,谢谢了!
调试欢乐多
DBA_Huangzj :是的呵呵
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[UserName] VARCHAR(3),[Code] VARCHAR(3))
INSERT [tb]
SELECT 1,'aaa','001' UNION ALL
SELECT 2,'bbb','002' UNION ALL
SELECT 3,'ccc','008' UNION ALL
SELECT 4,'ddd','009'
GO
--------------开始查询--------------------------
IF OBJECT_ID('pp') IS NOT NULL DROP PROC pp
GO
CREATE PROC pp
@code VARCHAR(10),
@new_code VARCHAR(10) OUTPUT
AS
BEGIN
SELECT TOP 1 @new_code=RIGHT(1000+code+1,3) FROM tb AS a WHERE [Code]>=@code
AND NOT EXISTS (SELECT 1 FROM tb WHERE [Code]=RIGHT(1000+a.[Code]+1,3))
ORDER BY RIGHT(1000+code+1,3)
END
----------------结果----------------------------DECLARE @code VARCHAR(3)
EXEC pp '002',@code OUTPUT
SELECT @code
/*
003
*/
EXEC pp '007',@code OUTPUT
SELECT @code
/*
010
*/
if object_id('output_code')is not null
drop proc output_code
go
create proc output_code
@code varchar(10),
@new_code varchar(10) output
as
begin
select top 1 @new_code=right(1000+@code+1,3) from tb a where
not exists (select 1 from tb where @new_code=a.[code])
order by right(1000+@code+1,3)
end
-------------------------------------------------------------------
declare @a varchar(3)
exec output_code '002',@a output
select @a/*
(无列名)
003
*/
007
006 @code是001的话,那么生成的应该是002而不是010
007006 @code是001的话,那么生成的应该是002而不是010
007006 @code是001的话,那么生成的应该是002而不是010