Works in View not in SP - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Works in View not in SP

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-04 : 11:05:39

The following works fine in my view but not if I copy and paste it into my sp - which compiles ok but when I run it it removes the greater than and less than signs

SELECT TOP (100) PERCENT MOP, SUM(AmtPaid) AS Expr1
FROM dbo.Saless WHERE (DatePurchased >= @SDate AND DatePurchased <= @EDate)
GROUP BY MOP
ORDER BY MOP

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-04 : 11:18:44

Error or wrong data?

"but when I run it (what is this it?) it (what is this it?) removes the ..."

Corey

I Has Returned!!

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-04 : 11:20:48

SELECT TOP (100) PERCENT MOP, SUM(AmtPaid) AS Expr1
FROM dbo.Saless WHERE (DatePurchased = @SDate AND DatePurchased = @EDate)
GROUP BY MOP
ORDER BY MOP

It takes out the > and < see above.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-04 : 11:26:23

So you're saying that when you create the stored procedure, it alters the where clause by itself???

Create Procedure MyProc
@SDate datetime,
@EDate datetime

As

SELECT TOP (100) PERCENT MOP, SUM(AmtPaid) AS Expr1
FROM dbo.Saless WHERE (DatePurchased = @SDate AND DatePurchased = @EDate)
GROUP BY MOP
ORDER BY MOP

Go

So MyProc doesn't have the '<' or the '>' when you go to look at it??

Corey

I Has Returned!!

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-04 : 11:30:50

If r click on ths pand execute it I get this

USE [BridgeTickets_Test]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[CashUp]
@SDate = N'2011-04-04 14:52:00.000',
@EDate = N'2011-04-06 16:52:00.000'

SELECT 'Return Value' = @return_value

GO

Sorry - time zone diference, Ihave to go now but will check tomorrow if you have replied

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-04 : 13:10:13

Pinto:
two things.
1. do me a favor and get rid of "top" clause!!!! it's wrong and it's 100% to give you the right result. please rewrite your query without using TOP and ORDER clause.
2. your query is extremely simple and there is absolutely no need that SDate and EDate should not include all the dates. i'm curious enough to ask, whether you know your data good enough?

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-05 : 04:15:43

slimt - I created the query on the table in management studio where it worked and copied the sql into my stored procedure. I am going to have to select records for shifts so if someone started work on 1st April at 21.00 hrs and finished on 2nd April at 13.00 I need to extract those records. I need a summary by Method of Payment and total paid against each MOP.
Do not be so harsh - this forum is titled 'New to SQL Server Programming' - not 'Very Experienced SQL Server Programming'

McDebil
Starting Member

23 Posts

Posted - 2011-04-05 : 05:30:33

Hi Pinto,

use the BETWEEN in the WHERE clause:

SELECT MOP, SUM(AmtPaid) AS SumAtmPaid
FROM dbo.Saless
WHERE DatePurchased BETWEEN @SDate AND @EDate
GROUP BY MOP
ORDER BY MOP

McDebil

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-05 : 05:54:20

If use between and my start date is 01/04/11 and my end date 05/04/11 - will it include the 1st and the 5th ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 07:14:51

Yes. between is inclusive - it includes both start and end dates.

Be sure to give the earlier date first, and the later date second. "between '05/04/11' and '01/04/11'" will return nothing at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources