K
ks_reny
Unregistered / Unconfirmed
GUEST, unregistred user!
我想實現這樣功能的存儲過程: 通用的編碼自動增加<br>該存儲過程可以對多個表適用. 參數有三個,代碼,字段,表名<br>比如 表A 的字段 NO,它的格式是 AM07120023 其中 AM 為 代碼 0712代表 07年12月 0023代表流水號<br>如果當前是07年12月 表A中最大的NO是 AM07120023 那嚜我執行存儲過程後能返回AM07120024 <br> 其中 該存儲過程的參數分別為 AM,字段NO,表A<br>該存儲過程可以對其它表適用, 參數如果為 PD ,字段NUM,表B 執行後就能返回PD071200XX<br><br>我的意思大家應該明白了吧<br>我寫了一個存儲過程 可是在最後報錯<br>alter Procedure Add_number<br>@Str varchar(4),<br>@Tablename varchar(20),<br>@Coloumname varchar(20)<br>AS<br>Declare @SQL nvarchar(1000)<br>Declare @maxmonth char(4) ------数据中最大的月份<br>declare @month char(4) ------寄存数据中最大的月份<br>declare @maxnum char(4) -------数据中最大月份中的最大流水号<br>Declare @num char(4) --------寄存数据中最大月份中的最大流水号<br>declare @currentmonth char(4) -----系统当前月份<br>declare @current char(4) -------积存系统当前月份<br>Declare @N int ------- @Tablename的记录数 <br>Declare @NO int ------寄存@Tablename的记录数 <br>declare @returnnum char(10) -------返回值--当前对应的编号<br>declare @return char(10) ------寄存返回值--当前对应的编号 <br>Declare @number char(4)<br>Declare @numbers char(4)<br>Declare @numbers1 char(4)<br>Declare @numbers2 char(4)<br>Declare @numbers3 char(4)<br>Declare @numbers4 char(4)<br>Declare @char varchar(4)<br>Declare @charnum varchar(4)<br>Select @SQL='select @maxmonth = substring(max('+@Coloumname+'),3,4) From '+@Tablename<br>exec sp_executesql @SQL,N'@maxmonth varchar(4) output',@month output <br><br>Select @SQL='select @Maxnum = substring(max('+@Coloumname+'),7,4) From '+@Tablename<br>exec sp_executesql @SQL,N'@Maxnum varchar(4) output',@num output<br><br>Select @SQL='Select @currentmonth=substring(convert(char(16),getdate(),112),3,4)'<br>exec sp_executesql @SQL,N'@currentmonth varchar(4) output',@current output<br><br>Select @SQL='Select @N=count(*) From '+@Tablename<br>exec sp_executesql @SQL,N'@N varchar(4) output',@NO output<br> if @NO=0 <br> begin<br> Select @SQL='Select @returnnum='+@Str+@current+'0001'<br> exec sp_executesql @SQL,N'@returnnum varchar(4) output',@return output <br> end <br> if @month=@current <br> Begin <br> Select @SQL='Select @number=Cast((Cast('+@num +'as int)+1) as char(4)) ' <br> exec sp_executesql @SQL,N'@number varchar(4) output',@numbers output <br> if len(@numbers)=1<br> begin<br> Select @SQL='Select @number1='+'''000'''+@numbers<br> exec sp_executesql @SQL,N'@number1 varchar(4) output',@numbers output <br> End <br> if len(@numbers)=2<br> begin<br> Select @SQL='Select @number2='+'''00'''+@numbers <br> exec sp_executesql @SQL,N'@number2 varchar(4) output',@numbers output <br> End <br> if len(@numbers)=3<br> begin<br> Select @SQL='Select @number3='+'''0'''+@numbers <br> exec sp_executesql @SQL,N'@number3 varchar(4) output',@numbers output <br> End <br> if len(@numbers)=4<br> begin<br> Select @SQL='Select @number4='+@numbers <br> exec sp_executesql @SQL,N'@number4 varchar(4) output',@numbers output <br> End <br> Select @SQL='Select @returnnum='+@Str+@current+@numbers<br> exec sp_executesql @SQL,N'@returnnum varchar(4) output',@return output <br> end<br> if @month<@current <br> Begin <br> Select @SQL='Select @returnnum='+@Str+@current+'0001'<br> exec sp_executesql @SQL,N'@returnnum varchar(4) output',@return output <br> End <br><br>select @month,@current,@num,len(@numbers),@charnum,@numbers,@return<br><br>服务器: 消息 207,级别 16,状态 3,行 1<br>列名 'AM07120001' 无效。<br><br>(所影响的行数为 1 行)<br>結果是出來了,但就是報錯.不知哪位高手給看以下.