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 #TempTableCreatedDynamically 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!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.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 #TempTableCreatedDynamically 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|||Rolando Barberis (rolandobarberis@.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 #TempTableCreatedDynamically 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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 #TempTableCreatedDynamically 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!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.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 #TempTableCreatedDynamically 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

Collation Recommendation

I work for an international company and we have to join between databases in different locations. I want a situation where the collations are the same so that we don't have to worry about stating which collation to use in cross server queries. Can someone tell me what we shoud choose. We can't right now change the system to use unicode. If we choose windows collation then does it take the collation of the os on the server? Will we have a problem if different servers have different Windows collations? Is it better to choose a Sql collation and make sure everyone chooses the same one? Thanks for your help.What other DB platforms/collations will you be working with?
Do you have the ability/authority to dictate the collation used at these other sites?

I'm not sure what you mean by 'Windows collation'. When creating a new DB in SQL2k, you have the ability to set the code page/collation for that db and 'windows collation' is not an option.|||I can dictate what collation the other servers should use. When you setup the Sql Server you can choose either windows collation and the sort order or choose a Sql Collation and the sort order. What I want to know is whether or not it will simplify things to choose the Sql Collation. It seems to me that if I use the windows collation I may run into problems if the windows collation of the os is different on the other servers.|||Unless I have a reason not to, I use the default (SQL_Latin1_General_CP1_CI_AS). The 1252 character set, case insensitive, dictionary order, accent sensitive.

Being an international company, though , you may wish to use the 850 (multi-linqual) character set, but I'd stick with case insensitive, dictionary order, accent sensitive, unless you have another reason not to.

Unicode would be preferable, but you've already mentioned that you cannot use it.|||Hi!

I am looking for help concerning collation problem, so if You can help me I would be thankful...

I am changing from sql7 to sql2k and i would like to be sure i have maintaned the same sort order etc. i had in sql7:

Unicode data sorting
-------
Locale ID = 33280

case sensitive

Sort Order Description
----------------------------------------
Character Set = 4, cp1250
Code page 1250
Sort Order = 80, bin_cp1250
Binary Sort Order for the Code Page 1250 Character Set

Characters, in Order
----------------------------------------
! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@. A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
_
_



So i think in sql2k i should set:

Polish_BIN

i need to have bin sorting and polish letters.

on collation list:

select * from ::fn_helpcollations()

there is not available:

sql_latin1_general_cp1250_bin

Please let me know if i am right or not.

Thanks in advance

by the way anybody knows how to get the list of ordered characters used by the sql server 2k same as the one extracted from the sql7. standard procedure sp_helpsort was used to receive the result at the start of this message, but for sql2k gives slightly different answer as:

only: Polish, binary sort

?