473,699 Members | 2,404 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 15313
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
4393
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 the 2 different databases. Basically, I need to take a look at the year portion (with a SUBSTRING...
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 dynamically create a temp table with the same number of columns, at which point I can then perform an INSERT...
2
1689
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
15432
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 know is how I can return the number of records in the temp table #Contacts back to MS Access and...
1
1269
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
1278
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 procedure is used to get data including this extra column.
2
3727
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 words, there are 3 OrderID records of the user whose UserID=6, say, OrderID=8, OrderID=17 & OrderID=29....
2
1264
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
7245
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 (Visible=false) this column on the grid. In order to access the value in this field, I have created a...
0
3978
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 choice is ultimately mine. The one constraint is that I have to allow for customized name/value pairs....
0
8697
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9184
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9045
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8892
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7767
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5878
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3062
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
2
2359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2013
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.