stored procedure result set - 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

SQL Server 2000 Forums

Transact-SQL (2000)

stored procedure result set

Author

Topic

Bryce Covert
Starting Member

12 Posts

Posted - 2005-08-16 : 13:22:08

Hi, I've created a stored procedure. Most of the time, my stored procedures will show the columns of the result set in Server Explorer in Visual Studio. However, this last stored procedure creates temporary tables and for this reason, no "column schema" is made from the stored procedure. Does that make sense? For example, since I have multiple queries going on in the stored procedure, I need to "set" which one is the result set. I need to be able to do this in order to interact with the stored procedure in Visual Studio.

Any ideas? I can post screens if this doesn't make sense.

create table #currentperiods (period_name varchar(50), static_available bit, cycle_available bit)
insert into #currentperiods(period_name,static_available,cycle_available)
exec sp_current_periods

/*we now have a list of current periods. We're going to figure
  out the food for today, and then only return the periods that are today */
    
  declare @date_to_use datetime
  set @date_to_use = cast(convert(varchar,getdate(),101) as datetime)
  
  create table #todaysfood (structure_title varchar(50), food_item varchar(50),period_name varchar(50),food_category varchar(50))
  insert into #todaysfood(structure_title,food_item,period_name,food_category)

  exec sp_get_food_for_date @date_to_use, @only_entrees
  

  /*foods for cycles that are in the current period.*/
  select structure_title, food_item, #todaysfood.period_name, food_category from #todaysfood 
  inner join #currentperiods on #todaysfood.period_name=#currentperiods.period_name
  WHERE #currentperiods.cycle_available=1
  
  union
  
  /*foods for cycles that have no period! */
  select structure_title, food_item, #todaysfood.period_name, food_category from #todaysfood
  where period_name is null
  
  union
  
  /*foods for the static menu for that period */
  select static_menu.period_name + ' Static Menu' as structure_title, food_item, static_menu.period_name, food_category from static_menu
  inner join #currentperiods on #currentperiods.period_name=static_menu.period_name
  where #currentperiods.static_available = 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 01:42:07

Creating temporary table inside the procedure wont exist in the database
It will be dropped if the procedure scope is over

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-08-17 : 09:53:12

exec sp_get_food_for_date @date_to_use, @only_entrees
Run that first. Then declare your tables and manipulate them. They should not go out of scope that way. Also look at making them Table Variables instead of Temp tables.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Bryce Covert
Starting Member

12 Posts

Posted - 2005-08-17 : 12:03:33

Well, actually, the query works fine. I've tested it, and seems to work fine.

How will this help with the actual problem? It returns rows, but in Server Explorer in Visual Studio the schema (I'm not sure if this is the correct terminology) is non-existant.

Bryce Covert
Starting Member

12 Posts

Posted - 2005-08-17 : 12:24:41

Let me explain the query better.

This bit should get available periods from a table into a temporary table:


create table #currentperiods (period_name varchar(50), static_available bit, cycle_available bit)
insert into #currentperiods(period_name,static_available,cycle_available)
exec sp_current_periods

This bit will get some data into another temporary table


  declare @date_to_use datetime
  set @date_to_use = cast(convert(varchar,getdate(),101) as datetime)
  
  
  create table #todaysfood (structure_title varchar(50), food_item varchar(50),period_name varchar(50),food_category varchar(50))
  insert into #todaysfood(structure_title,food_item,period_name,food_category)
  exec sp_get_food_for_date @date_to_use, @only_entrees

Now, this last bit is the query that I want returned. These are the results I'm after.


  /*foods for cycles that are in the current period.*/
  select structure_title, food_item, #todaysfood.period_name, food_category from #todaysfood 
  inner join #currentperiods on #todaysfood.period_name=#currentperiods.period_name
  WHERE #currentperiods.cycle_available=1
  
  union
  
  /*foods for cycles that have no period! */
  select structure_title, food_item, #todaysfood.period_name, food_category from #todaysfood
  where period_name is null
  
  union
  
  /*foods for the static menu for that period */
  select static_menu.period_name + ' Static Menu' as structure_title, food_item, static_menu.period_name, food_category from static_menu
  inner join #currentperiods on #currentperiods.period_name=static_menu.period_name
  where #currentperiods.static_available = 1

The other data isn't really used outside of the procedure, so the scope issue shouldn't be a problem, right?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources