CREATE PROC Pro_EnquiryID
@Name VARCHAR(20) ,
@SupplierID VARCHAR(20),
@EncodeID VARCHAR(20) OUT ----返回ID
AS
DECLARE @SQL NVARCHAR(500) --执行的SQL
BEGIN
SET @SQL = 'SELECT @EncodeID=(Select LTRIM(FID) from UID where FName ='''
+ @Name
+ ''')
+'''+@SupplierID+'''+
(Select isnull(RIGHT(''00000'' + CAST( CONVERT(INT,REPLACE(REPLACE(Max( right(FInComingID,5)),CHAR(10),''''),CHAR(13),''''))+1
AS varchar(20)),5),''00001'') from UIncomingSingle -where ????这里不会写 )'
--@EncodeID=单头+客户ID+流水号
EXEC sp_executesql @SQL, N'@EncodeID VARCHAR(20) OUT', @EncodeID OUT
--返回@EncodeID
PRINT @SQL
END
GO--
DECLARE @EncodeID VARCHAR(20)
EXEC Pro_EnquiryID '来料单', '123', @EncodeID OUT
SELECT @EncodeID AS EncodeID
--
L12300068 ==>> L12300001现在想让每个客户自己一个流水号,请问条件怎么加进去
@Name VARCHAR(20) ,
@SupplierID VARCHAR(20),
@EncodeID VARCHAR(20) OUT ----返回ID
AS
DECLARE @SQL NVARCHAR(500) --执行的SQL
BEGIN
SET @SQL = 'SELECT @EncodeID=(Select LTRIM(FID) from UID where FName ='''
+ @Name
+ ''')
+'''+@SupplierID+'''+
(Select isnull(RIGHT(''00000'' + CAST( CONVERT(INT,REPLACE(REPLACE(Max( right(FInComingID,5)),CHAR(10),''''),CHAR(13),''''))+1
AS varchar(20)),5),''00001'') from UIncomingSingle -where ????这里不会写 )'
--@EncodeID=单头+客户ID+流水号
EXEC sp_executesql @SQL, N'@EncodeID VARCHAR(20) OUT', @EncodeID OUT
--返回@EncodeID
PRINT @SQL
END
GO--
DECLARE @EncodeID VARCHAR(20)
EXEC Pro_EnquiryID '来料单', '123', @EncodeID OUT
SELECT @EncodeID AS EncodeID
--
L12300068 ==>> L12300001现在想让每个客户自己一个流水号,请问条件怎么加进去
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UID]
GO
CREATE TABLE [dbo].[UID] (
[FID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FRe] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FTable] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[FField] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
L 来料单 NULL NULL NULL
LL 领料单 NULL NULL NULL CREATE TABLE [dbo].[UIncomingSingle] (
[FInComingID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]
@Name VARCHAR(20),
@SupplierID VARCHAR(20),
@EncodeID VARCHAR(20) OUT ----返回ID
AS
SELECT @EncodeID = LTRIM(FID) + @SupplierID from UID where FName = @Name
select @EncodeID = @EncodeID + right(isnull(max(convert(int,right(FInComingID,5)))+1,1)+100000,5) from UIncomingSingle where ???这里不知道你什么条件
END
GO
是什么格式的?怎么编码的?
是不是 单头+客户ID+流水号?
where left(FInComingID)=@name and substring(FInComingID,2,3)=@SupplierID
ID=L12300001ID=查询出来的编码(L)+传入的客户ID(321)+当前客户在表里的流水号(00022)
ID=L32100022
2. drop table [dbo].[ttt]
3. GO
4.
5. /****** Object: Table [dbo].[ttt] Script Date: 2008-12-15 17:11:26 ******/
6. CREATE TABLE [dbo].[ttt] (
7. [id] [int] IDENTITY (1, 1) NOT NULL ,
8. [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
9. [time] [datetime] NULL
10. ) ON [PRIMARY]
11. GO
12.
13. ALTER TABLE [dbo].[ttt] ADD
14. CONSTRAINT [PK_ttt] PRIMARY KEY CLUSTERED
15. (
16. [id]
17. ) ON [PRIMARY]
18. GO
19.
20. insert into ttt(name,time) values('logan',getdate());
21. insert into ttt(name,time) values('peter',getdate());
22. insert into ttt(name,time) values('man',getdate());
23. insert into ttt(name,time) values('lida',getdate());
24. insert into ttt(name,time) values('fcuandy',getdate());
25.
26. select * from ttt
27. /*
28. 1 logan 2008-12-15 17:36:37.780
29. 2 peter 2008-12-15 17:36:37.780
30. 3 man 2008-12-15 17:36:37.780
31. 4 lida 2008-12-15 17:36:37.780
32. 5 fcuandy 2008-12-15 17:36:37.793
33. */
34.
35.
36. GO
37. CREATE TRIGGER tr ON ttt
38. INSTEAD OF INSERT
39. AS
40. SET IDENTITY_INSERT ttt ON
41. DECLARE @n INT
42. SELECT @n=MAX(id) FROM ttt
43. ;WITH fc AS
44. (
45. SELECT n=1
46. UNION ALL
47. SELECT nn=n+1 FROM fc WHERE n<@n
48. ),fc1 AS
49. (
50. SELECT n FROM fc a
51. LEFT JOIN ttt b
52. ON a.n = b.id
53. WHERE b.id IS NULL
54. )
55. INSERT ttt(id,name,time) SELECT n,name,time
56. FROM
57. (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a
58. INNER JOIN
59. (
60. SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
61. ) b
62. ON a.idx=b.idx
63. DECLARE @r INT
64. SELECT @r=@@ROWCOUNT
65.
66. SET IDENTITY_INSERT ttt OFF
67. INSERT ttt(name,time) SELECT name,time FROM
68. (
69. SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
70. ) x
71. WHERE idx>@r
72.
73.
74. GO
75. DELETE FROM ttt WHERE name = 'peter' OR name='lida'
76. GO
77. INSERT ttt SELECT 'xxx',getdate()
78. INSERT ttt SELECT 'yyy',GETDATE()
79. GO
80. SELECT * FROM ttt
81. /*
82. 1 logan 2008-12-15 17:37:20.967
83. 2 xxx 2008-12-15 17:37:21.013
84. 3 man 2008-12-15 17:37:20.967
85. 4 yyy 2008-12-15 17:37:21.030
86. 5 fcuandy 2008-12-15 17:37:20.967
87. */
88.
89. DELETE FROM ttt WHERE name ='xxx' OR name='yyy'
90.
91. INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()
92.
93. SELECT * FROM ttt
94.
95. /*
96. 1 logan 2008-12-15 17:38:29.450
97. 2 roy_88 2008-12-15 17:38:29.530
98. 3 man 2008-12-15 17:38:29.467
99. 4 limpire 2008-12-15 17:38:29.530
100. 5 fcuandy 2008-12-15 17:38:29.467
101. 6 熊 2008-12-15 17:38:29.530
102. */