473,574 Members | 2,553 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Column Count from Stored Procedure

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreat edDynamically EXEC
@UserProcCalled . With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!
Jul 20 '05 #1
2 15281
ro************* @hotmail.com (Rolando Barberis) wrote in message news:<ca******* *************** ****@posting.go ogle.com>...
I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreat edDynamically EXEC
@UserProcCalled . With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!


If you really need to do this, the easiest way would be to do it on
the client side - for example, retrieve an ADO RecordSet, then use the
RecordSet metadata to create a table.

However, there are some issues with this general approach - if you
don't know the format of the result set in advance, then it's
difficult to do anything meaningful with it. You can't write SQL code
to process the temp table, because you don't know anything about the
number of columns, the data types, the row count (you might want to
use paging for a large result set, for example) etc. And this is in
addition to the obvious issues (security, performance, maintenance)
which may arise from allowing users to create their own code in the
database.

But since you don't give any detailed information about your goals or
your environment, it's possible that you do have good reasons for
looking at this solution. If you can give more information about what
you're trying to do, though, someone may have an alternative idea to
propose.

Simon
Jul 20 '05 #2
Rolando Barberis (ro************ *@hotmail.com) writes:
I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreat edDynamically EXEC
@UserProcCalled . With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?


This is a dead end. There is no way you can do this in SQL only. As Simon
says, you need to go client-side and deal with the the procedures there.

Not even client-side there is any good way to determine the number of
columns without running the procedure. In some contexts, ADO uses
SET FMTONLY ON which causes SQL Server to only sift through the statements
without executing them, but still return information about the result
sets. But there are several unexpected things that can happen with SET
FMTONLY ON, so in my opinion it's useless.

Then again, once you are client-side, it is not problem to run the
procedures. SQL Server will return information about the result sets,
and you will get recordsets, data tables or whatever depending on
which client library you use. (My personal preference is for ADO .Net.)

--
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4370
by: Milo Woodward | last post by:
I have some columns of data in SQL server that are of NVARCHAR(420) format but they are dates. The dates are in DD/MM/YY format. I want to be able to convert them to our accounting system format which is YYYYMMDD. I know the format is strange but it will make things easier in the long run if all of the dates are the same when working between...
1
795
by: Rolando Barberis | last post by:
I am trying to determine the number of columns that are returned from a stored procedure using TSQL. I have a situation where users will be creating their own procedures of which I need to call and place those results in a temp table. I will not be able to modify those users procedures. I figure if I have the number of columns I can...
2
1688
by: Chris Auer | last post by:
I am trying to run a query where my Table and my request are variables (Will be used in Stored Procedure) Declare @Email VARCHAR(100) Declare @Table VARCHAR(50) Declare @Count VARCHAR(8) DECLARE @cmd VARCHAR(500) set @Table = 'tblManager' set @Email = 'cauer@tampabay.rr.com'
2
15429
by: Lauren Quantrell | last post by:
I am using a stored procedure as the recordsource on an MS-Access2000 form: Forms!frmName.RecordSource = "dbo.myStoredProcedure" The stored procedure creates a temp table #Contacts and then inserts matching KeyID values contacts into it. Then I use a union query in the same stored procedure to create the actual recordset. What I want to...
1
1264
by: .Net Sports | last post by:
i have an aliased column in an sql statement that works fine when displaying its output in a datagrid, but when I transfer the sql statement into a stored procedure , the datagrid can't see it. I get an error "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name myaliasedcolumn." }
1
1276
by: Fir5tSight | last post by:
Hi All, This seems to be a difficult problem for me. Hope you can help me out... I have a program whose major part is a grid that displays several columns from data obtained from a stored procedure. There is a dialog, and if the user clicks on the "OK" button, an extra column should be appended to the grid. BTW, a separate stored...
2
3722
by: rn5a | last post by:
A SQL Server 2005 stored procedure expects a parameter 'UserID' depending upon which it retrieves the no. of records & OrderIDs corresponding to the 'UserID' from a DB table (note that OrderID & UserID are two of the columns in the DB table). So for e.g. consider a user whose UserID=6 & the DB table has 3 records whose UserID=6. In other...
2
1249
by: Fir5tSight | last post by:
Hi, I have a stored procedure that looks like the follows: ------------------------------------------------------------------------------------- SELECT ClientName AS 'Client Name', Location, ReportInstanceID FROM
4
7226
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which - "Modified" of type DateTime - is hidden since it should not be edited by a user. The system handles the update for this column. So, I have hidden...
0
3972
by: jeoffh | last post by:
Background: I am trying to "merge" some attributes into an existing XML column in my MS SQL 2005 database. The general idea is that I have an XML column in a table and I would like to update/delete some values while leaving the other values alone. I am designing this database/table/column so maybe I could use attributes or elements/nodes, the...
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8257
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8113
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6476
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5634
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3765
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2253
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1358
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1076
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.