Meta-Information about Stored-Procedures

[KG]

Hi ,

I am looking for meta-information about the return recordset of a
stored-procedure. The procedure returns a resultset that contains columns of
more tables joined together. In all tables, I use, there is a
Record-Creation-Timestamp-Attribute. When joining two or more tables these
attribute-names appear in ther resultset but
i found no way to distinguish them.

I there a way to retrieve meta-information about the result-recordset of
such a stored-procedure?


here some details:

the tables
=======
CREATE TABLE [dbo].[Table1] (
[Table1ID] [int] IDENTITY (1, 1) NOT NULL ,
[FK_Tab2ID] [int] NULL ,
[CreatedAt] [datetime] NULL )

CREATE TABLE [dbo].[Table2] (
[Table2ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (35) NULL ,
[CreatedAt] [datetime] NULL)

the stored-procedure:
===============

CREATE PROCEDURE dbo.sp_Test_Ret rieveData
@ID int
AS
SET NOCOUNT ON

select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @ID
GO

the resultset:
==========
Table1ID,FK_Tab 2ID,CreatedAt,T able2ID,Descrip tion,CreatedAt
(the attribute CreatedAt appears twice.)







-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Steve Troxell]

Re: Meta-Information about Stored-Procedures

KG wrote:[color=blue]
> CREATE PROCEDURE dbo.sp_Test_Ret rieveData
> @ID int
> AS
> SET NOCOUNT ON
>
> select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
> where table1.ID = @ID
> GO
>
> the resultset:
> ==========
> Table1ID,FK_Tab 2ID,CreatedAt,T able2ID,Descrip tion,CreatedAt
> (the attribute CreatedAt appears twice.)[/color]



Try this instead:

CREATE PROCEDURE dbo.sp_Test_Ret rieveData
@ID int
AS
SET NOCOUNT ON

select Table1ID, FK_Tab2ID, Table1.CreatedA t as Table1CreatedAt , Table2ID,
Description, Table2.CreatedA t as Table2CreatedAt
from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @ID
GO

you will get resultset:

Table1ID, FK_Tab2ID, Table1CreatedAt , Table2ID, Description, Table2CreatedAt





--
Steve Troxell
Krell Software - Database Tools for MS SQL Server

http://www.krell-software.com

[Erland Sommarskog]

Re: Meta-Information about Stored-Procedures

KG (kg@greenmail.c h) writes:[color=blue]
> I am looking for meta-information about the return recordset of a
> stored-procedure. The procedure returns a resultset that contains
> columns of more tables joined together. In all tables, I use, there is a
> Record-Creation-Timestamp-Attribute. When joining two or more tables
> these attribute-names appear in ther resultset but i found no way to
> distinguish them.
>
> I there a way to retrieve meta-information about the result-recordset of
> such a stored-procedure?[/color]

It would have helped if you have told in which environment you are working.
Are you using ADO?

In ADO, there are some properties on the Fields on object which may have
this information.

But it is kind of obscure programming to access this data. Better is to
use column aliases. Generally, SELECT * should not be used in production
code.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp