Identification between SP returning rows and SP doing DML (INSERT, UPDATE, DELTE)

Question

Post reply

Identification between SP returning rows and SP doing DML (INSERT, UPDATE, DELTE)

mohd.nizamuddin

Ten Centuries

Points: 1328
More actions
December 26, 2008 at 3:15 am

#197788

Hi,
We have SPs which execute INSERT, UPDATE and DELETE statements and some SPs return rows using SELECT statement at the end of SP.
Is there any information stored in sysObjects etc to identify that which SP is returning rows using SELECT.
Actually I need to make seperate list of SPs, which returns rows using SELECT.
Thanks in advance.
Nizam.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

select * from syscomments

Shamless self promotion - read my blog http://sirsql.net

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

It is difficult to find that information as a stored procedure that returns rows can also insert, update, or delete rows or include insert, update, and delete statements on temp tables or table variables.

Also in SQL Server 2005 querying sys.sql_modules would be the recommended place to query that information.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 3

Thanks Nicholas and Jack,

But I could not find any useful information, through which I could find whether the SP is returning rows.

Even though I could able to find from sysdepends. The scipt is attached below:

begin

declare @objname varchar(100)

set @objname = 'GetRecord' -- any SP name in your schema

select 'name' = (s6.name+ '.' + o1.name),

type = substring(v2.name, 5, 16),

updated = substring(u4.name, 1, 7),

selected = substring(w5.name, 1, 8),

'column' = col_name(d3.depid, d3.depnumber)

from sys.objectso1

,master.dbo.spt_valuesv2

,sysdependsd3

,master.dbo.spt_valuesu4

,master.dbo.spt_valuesw5 --11667

,sys.schemass6

where o1.object_id = d3.depid

and o1.type = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'

and u4.type = 'B' and u4.number = d3.resultobj

and w5.type = 'B' and w5.number = d3.readobj|d3.selall

and d3.id = object_id(@objname)

and o1.schema_id = s6.schema_id

and deptype < 2

select * from sysdepends where id = object_id(@objname)

end

In the above script, I think if all the rows are no in "updated" column and yes in any row in "selected" column. Then I could say that the SP is returning rows.

Because I think we hardly update / insert / deleteany information, when we intended to return some rows. (I know this would not be a rule, we do DML on temp table or memory tables, though).

Please check and suggest your valuable comments.

Thanks in advance.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

The only issue I would raise with your script, and I did not study it in detail, is that sysdepends is not dependable. Because SQL Server will allow you reference non-existent objects in a stored procedure you may miss some.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Keep in mind you could "return rows" of data without ever accessing any other table. Also - just because you might select rows from a table while in a stored proc does not mean they are being returned to the user (i.e SELECT....INTO).

Not sure how to do what you're asking, but sysdepends is not the way to get there.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 6

Thanks everybody for your valuable time and reply.

Since I am not getting values through wich I could decide whether SP is returning rows to user or simply updating table/s.

I decided that those are only selecting rows means that SP return rows to the user and those updating only will be updating table/s. Apart from that those SP selecting as well as updating I am showing to the user and decide that in which group will it belong. User will know better.

I made it like this thinking of that SP which are doing both will be around 30%-40% in an standard application database because the complex logic for selecting and updating kind of thing will happen at business layer.

Thanks a lot.

aamirr Newbie Points: 1 More actions · Answer 7

aamirr

Newbie

Points: 1

December 30, 2008 at 5:29 am

#918947

🙁