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

  • 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.

  • select * from syscomments



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

  • 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.

  • 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.

  • 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.

  • 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?

  • 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.

  • 🙁

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

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