1

If you have a (Sybase) stored procedure that does "select * from ... where ..." how does that work? When is the * expanded to all the columns? At sproc creation time, at sproc compilation time, execution time, etc?

I am using a sproc like that which is not returning one of the columns and it makes me think that it is at sproc creation time but I can't find any references to confirm that.

Edit: I have verified that recompiling a sproc doesn't cause the * to be re-evaluated which pretty much confirms my initial thoughts but still looking for a reference where this behavior is documented.

naumcho
  • 18,671
  • 14
  • 48
  • 59
  • Can you post your sproc? – Daniel A. White Feb 11 '11 at 16:21
  • Are you at liberty to change the stored proc? I usually avoid `select *`, and this is one of the reasons. – John M Gant Feb 11 '11 at 16:21
  • I don't think its at sproc creation time, more likely when it builds the execution path. – asawyer Feb 11 '11 at 16:21
  • 2
    @user779 - Are you using `SELECT *` inside a View or is it directly inside the stored procedure itself? – Martin Smith Feb 11 '11 at 16:26
  • 1
    +1 @Martin - if you have a view, make sure you execute `sp_refreshviews` to update the view definitions. – JNK Feb 11 '11 at 17:24
  • It is inside a sproc, not view. I had assumed it's at compile time and that someone added a new column and didn't run sp_recompile on it, but i heard that server was also restarted recently so that would mean it was recompiled after that which leads me to believe it's on creation time. I have very limited access to the server so I can't really investigate much. – naumcho Feb 11 '11 at 18:58
  • @user779: Unless things have changed (or I'm just wrong) recompiles only happen if the procedure or view is modified. A server restart shouldn't have anything to do with it. – Dave Feb 11 '11 at 19:51
  • @Dave: I was told by a dba that in such cases it is recompiled... Either way, I just tried create table; create sproc; call sproc; add column to table; recompile sporc; call sproc and it still shows only the initial columns. – naumcho Feb 11 '11 at 20:01
  • @user779: Wow, that is not intuitive. You have my sympathy. You could run sp_configure and see if that provides any leads, but I'm out of ideas. – Dave Feb 11 '11 at 20:18

2 Answers2

2

It should be execution time. You can test your theory by following these steps:

  1. Create a dummy table MY_DUMMY_TABLE with 1 field (let's just call it "ID")

  2. Write a stored proc that does nothing but select * from MY_DUMMY_TABLE

  3. Observe that the stored proc does indeed return your one field

  4. Alter the table to add another field (let's call it "NOTES")

  5. Run your stored proc again

  6. Observe whether "NOTES" is included in the output

JosephStyons
  • 57,317
  • 63
  • 160
  • 234
  • 1
    -1 This answer adds nothing. The question says they have done this and at step 6 they observe that the column is not included! They are asking is this behaviour documented/expected. – Martin Smith Feb 11 '11 at 16:31
  • It should be noted that at the time I posted this answer, their question did not include those steps. – JosephStyons Feb 11 '11 at 20:15
  • good point (+1) - I tried as you suggested and proved my suspicion was right ... still looking for where the behavior is actually documented/described – naumcho Feb 16 '11 at 03:56
0

The * is expanded when the procedure is compiled, which happens at the first execution. After that, the compiled version is used.

Avoid using select * in production code for reasons like this. See this related question.

Community
  • 1
  • 1
Dave
  • 4,546
  • 2
  • 38
  • 59
  • -1: this is definitely not true - I tried adding a column to a table and recompiling the sproc and it still returns the old column set – naumcho Feb 11 '11 at 19:57
  • I'm pretty sure it *is* true, but it may not be the source of your problem. I'm just answering the question you asked. – Dave Feb 11 '11 at 20:00
  • I just tried create table; create sproc; call sproc; add column to table; recompile sporc; call sproc and it still shows only the initial columns – naumcho Feb 11 '11 at 20:18