SQL Table Var Returning Empty - 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

SQL Table Var Returning Empty

Author

Topic

salvitti
Starting Member

2 Posts

Posted - 2009-08-13 : 14:29:56

I have created a stored proc that creates a table var, fills it with a series of results (Using SQL 2005). Each time it is run it returns "The stored Procedure executed successfully but did not return records" I then decided to test the general table var section using the following code taken from the internet. The routine first creates a table with some data. Next it creates the table var and then is suppose to insert the data from the created table into the table var. Last it should return the records from the table Var.

create table salesresults (
Category varchar(100)
, ProductName varchar(100)
, TotalSalesMoney money
, TotalSalesQuantity int
)
insert into salesresults ( Category , ProductName , TotalSalesMoney , TotalSalesQuantity )
select'Food' , 'Butter' ,11000, 678
union all select'Food' , 'Juice' ,10000, 788
union all select'Food' , 'Meat' ,4000, 75
union all select'Materials' , 'Rubber' ,14000, 567
union all select'Materials' , 'Steel' ,9000, 234
union all select'Materials' , 'Oil' ,5000, 455

declare @a table (
ID int identity (1,1) primary key clustered
,Category varchar(100)
, N int
, ProductName varchar(100)
, TotalSalesMoney money
, TotalSalesQuantity int
)

insert into @a ( Category , ProductName , TotalSalesMoney , TotalSalesQuantity )
select Category , ProductName , TotalSalesMoney , TotalSalesQuantity
from salesresults

SELECT * FROM @a

The result is that the CREATE TABLE adds a table to my DB with the data in it. The tempdb gets a new table entry in it. Again the message "The stored Procedure executed successfully but did not return records". I just do not see where the problem is

Sal

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 15:33:23

The code you posted works fine. Can you post some code that illustrates the problem?

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-13 : 15:53:47

where are you calling this stored procedure from?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 15:56:05

add SET NOCOUNT ON to the top your stored procedure.


Create Proc myProc
AS
SET NOCOUNT ON;
...
Rest of your code here
...
GO

salvitti
Starting Member

2 Posts

Posted - 2009-08-13 : 16:36:56

The SET NOCOUNT did it! Genius! The query is working fine now. What was causing the problem? Thank you everone for your replies

Sal

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 17:31:35

Your client application doesn't know how to handle multiple results returned from the SP. Each INSERT/SELECT statement inside the proc returns a rowcount.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-13 : 18:45:20

That means something like:
7 row(s) affected
would be suppressed.

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 19:30:18

Exactly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources