SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Weird Query Problem...Urgent !!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

manojshinde78
Starting Member

1 Posts

Posted - 08/13/2008 :  02:42:58  Show Profile  Reply with Quote
Hello,

I have a query which I use inside a stored procedure. It uses paramters passed to the proc in the query.

Here is the script


declare @dbamt float
declare @partycode varchar(10)
declare @acccode varchar(10)
declare @asondate datetime
declare @yrsrno int

set @partycode = 'N43'
set @acccode = '000175'
set @asondate = '03/31/2008'
set @yrsrno = 7

SET @DBAmt = (SELECT SUM(fAmount) FROM VRHeader AS H,VRDetail AS D,AccountHeads AS A WHERE D.vVRNo=H.vVRNo AND D.vVRType=H.vVRType AND D.iYrSrNo=H.iYrSrNo AND D.vAccCode=A.vAccCode AND D.vAccCode=@acccode AND h.dvrdate <= @transdate and h.iyrsrno=@yrsrno AND D.vDCFlag = 'D' AND H.vVRType='BANK' AND D.vPartyCode=@partycode)

print @dbamt

SET @DBAmt = (SELECT SUM(fAmount) FROM VRHeader AS H,VRDetail AS D,AccountHeads AS A WHERE D.vVRNo=H.vVRNo AND D.vVRType=H.vVRType AND D.iYrSrNo=H.iYrSrNo AND D.vAccCode=A.vAccCode AND D.vAccCode='000175' AND h.dvrdate <= '03/31/2008' and h.iyrsrno=7 AND D.vDCFlag = 'D' AND H.vVRType='BANK' AND D.vPartyCode='N43')

print @dbamt



As you can see above, if I use the variables in the query then it gives NULL output. If I use literal values instead of assigning variables then it shows the actual value i.e. 10238.

What could be the issue? Is there a problem with variables?

Please guide me in this regard.

Thank you very much

Manoj Shinde

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 08/13/2008 :  02:57:29  Show Profile  Reply with Quote
just out of interest, try passing your date as YYYYMMDD.

oh... and why not write your joins properly as ANSI joins?



also... do you really think your question is more "Urgent !!!" than anyone else's?

Em
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 08/13/2008 :  03:01:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
In first query, you are referring to @TransDate variable which is not defined in your sample code.
Replace @TransDate with @asondate and test again.



E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 08/13/2008 03:02:11
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 08/13/2008 :  03:06:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@DBAmt FLOAT,
	@PartyCode VARCHAR(10),
	@AccCode VARCHAR(10),
	@AsonDate DATETIME,
	@YrSrNo INT

SELECT	@PartyCode = 'N43',
	@AccCode = '000175',
	@AsonDate = '03/31/2008',
	@YrSrNo = 7

SELECT		@DBAmt = SUM(fAmount)
FROM		VRHeader AS h
INNER JOIN	VRDetail AS d ON d.vVRNo = h.vVRNo 
			AND d.vVRType = h.vVRType
			AND d.iYrSrNo = h.iYrSrNo
			AND d.vAccCode = @AccCode
			AND d.vDCFlag = 'D'
			AND d.vPartyCode = @PartyCode
INNER JOIN	AccountHeads AS a ON a.vAccCode = d.vAccCode
WHERE		h.dvrdate <= @AsonDate
		AND h.iyrsrno = @YrSrNo
		AND h.vVRType = 'BANK'

SELECT	@DBAmt



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/13/2008 :  03:07:23  Show Profile  Reply with Quote
How will you be passing values to variables? is it exactly as posted (using declare...set) or in actual are you having parameters?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000