Can I get rid of SELECT output with a stored procedure?

Question

Post reply

Can I get rid of SELECT output with a stored procedure?

ken reeder

Valued Member

Points: 63
More actions
July 8, 2005 at 12:50 pm

#164782

Greetings,
Since I am a newbie writing stored procedures, perhaps I am missing some obvious solution, but I've been unable to find it so far.
My stored procedures will be called from Visual FoxPro, if that matters, but I don't think it does.
My stored procedure contains a basic SELECT statement of the form:
SELECT * FROM table WHERE fieldname = @value
This produces an output result set which is returned to the calling application.
99.99% of the time I will find the one entry that I am looking for. For the .01% of the time that the record is not there, I call another stored procedure which does some analysis of various tables and then creates the record for me. That newly-created record is then the one that I want to return (via a repeat of the SELECT command) to the calling application.
In the .01% case, I would like to get rid of the empty result set created by the first SELECT command. Is there a way to do this?
(I have got one solution to the problem, which is to code the first SELECT as:
SELECT * INTO #temptable FROM table . . .
Then, when the record is already there, I SELECT FROM #temptable to build the result set for the calling application. However, this requires an extra step for the 99.99% of the cases where the target entry already exists, which it seems like I should try to avoid.)
Thanks,
Ken

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You have to avoid doing the select in the first place.

You have to do :

If not exists (Select * from dbo.YourTable where id = @Id)

begin

call create statement

end

select statement here.

ken reeder Valued Member Points: 63 More actions · Answer 2

Thanks for your response, Remi.

Perhaps I'm worrying about performance "issues" that are not worth worrying about, but, would you be able to offer an opinion as to whether it would be more efficient to:

- run 2 SELECTs against the data table (which is a small table, a few hundred entries), first to see if the desired record exists and secondly to extract the record into the result set,

as opposed to,

- run 1 SELECT against the data table to extract the desired record, if it exists, into a #temptable, and then run a second SELECT against the #temptable to create the result set.

Or, am I trying to split hairs? (or hares? or heirs?)

Thanks,

Ken

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

if exists is an extremely fast operation, especially compared to

1 - create temp table

2,3 - insert into temp (which implies another select)

4 - check to see if the insert was good (check if you need to create)

5 - remake a select to present the data.

6 - drop temp table

or

1 - if exists (fast operation) --check if need to create

2 - select

You tell me what you think is fastest .

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

ken - just to jump in and add my 2 cents - Remi's solution is the "only way to go"!!! There's no comparison at all....but if you still want to test the difference between the 2 you can always look at the execution plans....

ps: splitting hares might give you some stew but not much else.....<:-)

splitting hairs on the other hand (as Remi would tell you) is a "moo point" in this case!