SQL Server中的查询问题 [英] Problem for query in sql server

问题描述

我有一个表t_customer_account，其中有1000条记录.

我要注册一个帐号.
我的格式是
01010234000001
如果会计年度ID为
，则为01 01是公司ID
o2是分支ID
34是方案ID
其余6位数字是slno
我想使slno作为分支和方案明智.
在我的表格中，所有字段都显示为
Financial_id，Branch_id，Company_id，Scheme_Id

我想创建一个过程并也创建了它，但是它并不能获取所有的14位数字

我的查询是:-

////////

I have a table t_customer_account where 1000 records are there.

I want to make an account no.
My format is
01010234000001
here 01 if financial year id
01 is company id
o2 is branch id
34 is scheme id
and rest 6 digit is slno
I want to make the slno as branch and scheme wise.
In my table all the fields are present like
Financial_id,Branch_id,Company_id,Scheme_Id

I want to create a procedure and created also but it is not fetch all the 14 digit number

my query is:-

////////

alter procedure [dbo].[generate_acno]
as
begin
	DECLARE @fnno varchar(2)
	DECLARE @cmpid VARCHAR(2)
	DECLARE @brid int
	DECLARE @scid varchar(2)
	DECLARE @Cusid int
    declare @accno varchar(6)
	declare @sql varchar(100)
	declare @slno int
	declare @StrCode varchar(8)
	set @fnno='01'
	set @cmpid='01'
	
	--SET @CNT=0
	DECLARE VoucherCursor CURSOR  
	FOR
	SELECT customer_id,Branch_id,cast(Scheme_id as varchar(2)) FROM t_customer_account order by customer_id
    OPEN VoucherCursor;
    FETCH NEXT FROM VoucherCursor INTO @Cusid,@brid,@scid;
	--update t_voucher_master set voucher_no=voucher_no+'*'
   WHILE @@FETCH_STATUS = 0
   BEGIN
	select @scid;
	if len(@brid)=1 
			set @StrCode=@fnno+@cmpid+'0'+cast(@brid as varchar(1))+@scid 
	else
		set @StrCode=@fnno+@cmpid+cast(@brid as varchar(1))+@scid 
	select @StrCode;
	   set @accno=(select +@StrCode+REPLICATE('0',6-LEN(CONVERT(INT,(isnull(max(substring(passbook_No,9,6)),'000000')))))+ CONVERT(VARCHAR,CONVERT(INT,(isnull(max(substring(passbook_No,9,6)),'000000'))+1)) from T_Customer_Account where passbook_No like +''''+@StrCode+'%'+'''');
       select @accno;
		--select ''' + @StrCode + ''' + REPLICATE('0',6-LEN(CONVERT(INT,(SUBSTRING(isnull(max(Account_No),'000001'),9,6)))+1)) + CONVERT(VARCHAR,CONVERT(INT,(SUBSTRING(isnull(max(Account_No),'000001'),9,6))+1)) from T_Customer_Account where Account_No like +''' + StrCode + ''%'' +''';
		--set @CNT=@CNT+1;
		--SET @VOUCHER_NO='Pv'+cast(@CNT as varchar);
		update t_customer_account set passbook_no=@accno where customer_id=@Cusid;
		FETCH NEXT FROM VoucherCursor INTO @Cusid,@brid,@scid;
   END;
end;
CLOSE VoucherCursor;
DEALLOCATE VoucherCursor;

//////

请帮帮我.
谢谢.

//////

please help me.
thanks.

推荐答案

你好，


首先，我会说您为此创建表格设计时犯了一个大错误.您应该添加一列以给定的格式存储上述代码.现在，您必须在要访问帐户号"的整个数据库中编写此逻辑，这是一项繁琐的工作.如果您只是简单地在表"t_customer_account"中添加一列.这样做还为时不晚.添加一列并编写一个过程，该过程将更新每条记录，并创建帐号"并将其更新回记录.并修改逻辑以插入此代码的记录.

您是否知道您浪费多少内存处理速度?


另一件事，使用

Hello,


First of all i would say that you did a big mistake while creating table design for this. You should have added one column which stores the above Code in given format .Now you have to write this logic all over the database where you want to access "account no" which is a tiresome job. If you simple add one column in table "t_customer_account ". Still its not late to do so.Add one column and write a procedure which will update each record and will create Account No and update it back to record. And modify the logic to insert record for this code.

Do you have any idea how much Processing speed you are wasting on the cost of Memory?


Another thing, Use

RIGHT (''000''+ CAST ( CompanyCode AS varchar), 3) AS CompanyCode

如果Code像99，它将自动在右边插入0，之后将是099.您无需检查长度，也不必为此编写逻辑.

您能在这里发送表结构吗?

This will automatically insert 0''s in right if Code is like 99 it will be 099 afterwards. You don''t need to check length and have to write logic for this.

Could you please send your table structure here

这篇关于SQL Server中的查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！