No records returned from called procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

No records returned from called procedure

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-03-29 : 16:25:33

I have setup an Include file, a procedure that is called by other procedures. I am running into the following problem:

pr_TestProc1:
SET NOCOUNT ON
CREATE TABLE #Temp (TestID char(5), TestPerd datetime, TestValue decimal(18,5) Null)
INSERT INTO #Temp (TestID, TestPerd, TestValue)
SELECT ChemicalID, ChemPeriod, ChemUsage
FROM tblUsages
WHERE ChemPeriod = '1/1/2006'

INSERT INTO #Temp (TestID, TestPerd, TestValue)
SELECT 'TESTNEW', TestPerd, Sum(TestValue)
FROM #Temp
/* WHERE TestID <> 'TESTCHEM'  */
GROUP BY TestPerd

SELECT TestID, TestValue 
FROM #Temp

pr_TestProc2:
SET NOCOUNT ON
CREATE TABLE #tmp (TestID char(5), TestValue decimal(18,5))
INSERT INTO #tmp
Exec pr_TestProc1

SELECT TestID, TestValue
FROM #tmp

Running pr_TestProc2 gives me the message:
Stored Procedure ran successfully but no records were returned

If I include the following line, it works:
WHERE TestID <> 'TESTCHEM'
The 'TESTCHEM' TestValue is null, and that seems to create problems with the 'Exec' in a Group By situation.

I don't quite understand why I can run the pr_TestProc1 with correct results, whereby the pr_TestProc2 gives no results.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 17:28:46

You probably have error running the pr_TestProc1.

The TestID in #Temp is define is char(5). What is the size of ChemicalID ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 17:39:52

I think khtan is mostly right. Because TestID is char(5) and you are trying to insert values like 'TESTCHEM' and 'TESTNEW' no errors are being raise but the statement terminates because data would be truncated, so no errors and no rows.

have you tried executing the procedures individually in a query analyzer window?

Be One with the Optimizer
TG

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-03-29 : 19:56:41

Sorry ... a typo error, it should read 8 in both cases. I had mentioned that the pr_TestProc1 does return rows, but should also mention that it returns rows when run by itself.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-03-29 : 20:15:01

This is the error that appears when I run the query analyzer:

Warning: Null value is eliminated by an aggregate or other SET operation.

It doesn't seem to care if I run the procedure on it's own, just when I 'Execute' the procedure.

Thanks for directing me to Query Analyzer. Keep forgetting about that tool.

I used the 'coalesce' to get around the warning.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 21:08:27

Does this resolve your problem ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources