Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
manojshinde78
Starting Member
1 Post |
Posted - 2008-08-13 : 02:42:58
|
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 scriptdeclare @dbamt floatdeclare @partycode varchar(10)declare @acccode varchar(10)declare @asondate datetimedeclare @yrsrno intset @partycode = 'N43'set @acccode = '000175'set @asondate = '03/31/2008'set @yrsrno = 7SET @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 @dbamtSET @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 @dbamtAs 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 muchManoj Shinde |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-13 : 02:57:29
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-13 : 03:01:37
|
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" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-13 : 03:06:25
|
[code]DECLARE @DBAmt FLOAT, @PartyCode VARCHAR(10), @AccCode VARCHAR(10), @AsonDate DATETIME, @YrSrNo INTSELECT @PartyCode = 'N43', @AccCode = '000175', @AsonDate = '03/31/2008', @YrSrNo = 7SELECT @DBAmt = SUM(fAmount)FROM VRHeader AS hINNER 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 = @PartyCodeINNER JOIN AccountHeads AS a ON a.vAccCode = d.vAccCodeWHERE h.dvrdate <= @AsonDate AND h.iyrsrno = @YrSrNo AND h.vVRType = 'BANK'SELECT @DBAmt[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-13 : 03:07:23
|
How will you be passing values to variables? is it exactly as posted (using declare...set) or in actual are you having parameters? |
|
|
|
|
|
|
|