SQL Server数据库查询问题 [英] Sql Server Data base query problem

问题描述

declare @accNo nvarchar(1000)
declare @ParmDefinition nvarchar(1000)
DECLARE @SQLString NVARCHAR(500);
set @accNo =''
SET @ParmDefinition = N'@accNo1 nvarchar(1000)'
declare @amount money
Declare @temp as int
Declare @bl as tinyint
Set @bl=1
DECLARE db_cursor CURSOR FOR  
select rcb.gl_accno_cbs into #temp from particulars_reports pr
Inner Join reports rp on pr.rpt_id=rp.rpt_id
Inner Join particulars p on p.particular_id=pr.particular_id
Inner Join rmu_cbs_border rcb on rcb.particular_id=pr.particular_id
Where rp.rpt_id=3 and p.particular_id=9

	OPEN db_cursor   
		FETCH NEXT FROM db_cursor INTO @temp  
		WHILE @@FETCH_STATUS = 0  
		BEGIN 
		
		if @bl=1
		begin 
		Set @accNo= @accNo+''''+ CAST(@temp as CHAR(6)) +''''
		end
		else 
		begin
		Set @accNo=@accNo+','+ '''' + CAST(@temp as CHAR(6)) +''''
		end 
		Set @bl=2

	FETCH NEXT FROM db_cursor INTO @temp
	END  

CLOSE db_cursor  
	DEALLOCATE db_cursor
	select @accNo

select @amount
set @SQLString=N'Select sum(case when fmgl.dr_cr=''DR'' then fmgl.amount_tk else -amount_tk end) as Amount
From balance fmgl
Where fmgl.tdate< ''30-Sep-2010'' and fmgl.accountno in (@accNo1)';							
EXECUTE sp_executesql @SQLString,@ParmDefinition,@accNo1 = @accNo

这是我的代码
****************************
在@accNo变量中，我得到的值是``1''，``5''，``6''
如果将harcode值设为``1''，``5''，``6''，我得到金额，但如果我将@accNo变量放入，我将得到null.

我可以使用内部联接获得重用，但我需要它来进行更快的查询

[edit]已添加代码块，已删除紧急性.对您来说可能很紧急，但对我们而言并非如此.它只会使某些人烦恼，并可能减慢响应速度. -OriginalGriff [/edit]

This is my code
****************************
in @accNo variable i get value ''1'',''5'',''6''
if put harcode value as ''1'',''5'',''6'' i get amount but if i put @accNo variable i get null.

I can get the reusult using Inner join but i need it to make faster query

[edit]Code block added, urgency removed. It may be urgent for you, but it isn''t to us. It just annoys some people and can slow down responses. - OriginalGriff [/edit]

推荐答案

我看到问题出在您建立在底部的动态SQL中.像下面那样重新编写sql，应该没问题

Hi,

I see the issue is in dynamic sql you building at bottom. Re write the sql like follow and it should be ok

set @SQLString=N'Select sum(case when fmgl.dr_cr=''DR'' then fmgl.amount_tk else -amount_tk end) as Amount
From balance fmgl
Where fmgl.tdate< ''30-Sep-2010'' and fmgl.accountno in ('+@accNo1+')';

希望对您有所帮助

Hope this will help

您好，

您不能像这样在IN子句中使用字符串变量.一种方法是将@ accNo1中的字符串值连接到SQL语句，但是我不建议您这样做.而是将查询放置在摘要语句中以获取帐号.像这样的东西:

Hi,

You cannot use a string variable in IN clause like that. One way would be that you concatenate the string value in @accNo1 to your SQL statement, but I don''t suggest you do that. Instead place the query to get account numbers in your summary statement. Something like:

set @SQLString=N''Select sum(case when fmgl.dr_cr=''''DR'''' then fmgl.amount_tk else -amount_tk end) as Amount
From balance fmgl
Where fmgl.tdate < ''''30-Sep-2010'''' 
and fmgl.accountno in (select rcb.gl_accno_cbs 
                       from particulars_reports pr
                       Inner Join reports rp on pr.rpt_id=rp.rpt_id
                       Inner Join particulars p on p.particular_id=pr.particular_id
                       Inner Join rmu_cbs_border rcb on rcb.particular_id=pr.particular_id
                       Where rp.rpt_id=3 and p.particular_id=9)'';

问候，

mika

Regards,

mika

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