469,612 Members | 2,232 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,612 developers. It's quick & easy.

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_RetrieveData
@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_Tab2ID,CreatedAt,Table2ID,Description, 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! =-----
Jul 20 '05 #1
2 1823
KG wrote:
CREATE PROCEDURE dbo.sp_Test_RetrieveData
@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_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt
(the attribute CreatedAt appears twice.)


Try this instead:

CREATE PROCEDURE dbo.sp_Test_RetrieveData
@ID int
AS
SET NOCOUNT ON

select Table1ID, FK_Tab2ID, Table1.CreatedAt as Table1CreatedAt, Table2ID,
Description, Table2.CreatedAt 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
Jul 20 '05 #2
KG (kg@greenmail.ch) writes:
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?


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Cezary | last post: by
4 posts views Thread by Brian | last post: by
1 post views Thread by Darren Blackley | last post: by
40 posts views Thread by Harlan Messinger | last post: by
24 posts views Thread by Day Bird Loft | last post: by
3 posts views Thread by J1C | last post: by
4 posts views Thread by Jim Hammond | last post: by
1 post views Thread by Maziar Aflatoun | last post: by
1 post views Thread by simon2x1 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.