New to SQL Server, need help with a SP

[mgid]

I'm very much a novice at SQL Server programming, but I do have experience with access and oracle. Basically, I'm trying to return records from a stored procedure after first executing two other stored procedures. It looks like:

Create Procedure "myproc"

@date text
AS

"Call two other stored procedures..."

"SELECT statement here based on changes the other procedures just made."

RETURN

The SELECT statement returns records if I put it in its own sp, but here the sp does not return records. Any help is appreciated.

 

[tlbroadbent]

You really need to provide more information. There is no way to tell what is happening without seeing the query and perhaps even the entire SP. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[mgid]

Here's the code:

Create Procedure dailysalesreport_sp

@date text
As

DELETE FROM tblWeeklySales;
DELETE FROM tblDailySales;

EXECUTE weeklysales_sp @date;
EXECUTE dailysales_sp @date;

SELECT tblDailySales.Date, tblDailySales.Sales, tblDailySales.[Prior Sales], tblWeeklySales.WTD, tblWeeklySales.[WTD Prior],
tblWeeklySales.Week, tblDailySales.Comp, tblDailySales.DMA, tblDailySales.Location, tblDailySales.District
FROM tblWeeklySales LEFT JOIN tblDailySales ON tblWeeklySales.Location = tblDailySales.Location;

RETURN

 

[JNC73]

Hello gmiha,


This is according to me the solution


CREATE PROCEDURE dailysalesreport_sp
@date text
As
BEGIN

DELETE FROM tblWeeklySales
DELETE FROM tblDailySales

EXECUTE weeklysales_sp @date
EXECUTE dailysales_sp @date

SELECT tblDailySales.Date, tblDailySales.Sales, tblDailySales.[Prior Sales], tblWeeklySales.WTD, tblWeeklySales.[WTD Prior],
tblWeeklySales.Week, tblDailySales.Comp, tblDailySales.DMA, tblDailySales.Location, tblDailySales.District
FROM tblWeeklySales LEFT JOIN tblDailySales ON tblWeeklySales.Location = tblDailySales.Location;

END

The bold code is the code you need.
The last select in a Stored Procedure should be returned as the result of the SP.


Hope this helps,

JNC73

 

[mgid]

JNC73, thanks for the reply, but it did not correct the problem. The tables used in the SELECT statement are modified in the two SP's that execute before it. Might that be an issue here?

 

[tlbroadbent]

Have you confirmed that the tables contain data after exuting the two procedures? Do the tables contain records that meet the criteria tblWeeklySales.Location = tblDailySales.Location?

I just noticed that @date was data type text. Change to char, varchar or perhaps datetime. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[mgid]

The tables contain data that meet the join criteria and the datatype seems fine. In fact, the SELECT statement returns the expected records if run separately in a new SP.

 

[tlbroadbent]

I've just created a similar procedure and it runs without a problem. Do you get any messages in the output result when you execute the stored procedure? Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[mgid]

As I said, I am definitely a novice at SQL, for it was the "SET nocount ON" command that solved the problem. Thanks for the replies.