Sql Server Data base query problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

SQL

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

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]

Posted 9-Feb-11 20:39pm

juwel115

Updated 9-Feb-11 20:46pm

OriginalGriff

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shahriar Iqbal Chowdhury/Galib · Answer 1 · 2011-02-09T21:26:00

Solution 1

Hi,

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

SQL

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

Posted 9-Feb-11 21:26pm

Shahriar Iqbal Chowdhury/Galib

Comments

juwel115 10-Feb-11 3:51am

i doesnt work. Thanks for reply

Shahriar Iqbal Chowdhury/Galib 10-Feb-11 4:29am

what is the value in @accNo1 ?

Wendelius · Answer 2 · 2011-02-09T21:30:00

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)';

Regards,

mika