System Stored Procedure Results to Cursor or Temp Table

System Stored Procedure Results to Cursor or Temp Table

Post by Michael McCallu » Wed, 19 Jul 2000 04:00:00



Is it possible to have a system stored procedure, like sp_columns, return
its results into a cursor (preferable) or into a temporary table from within
T-SQL?

If so, could you show a quick example.

Thanks,
Mike McCallum

 
 
 

System Stored Procedure Results to Cursor or Temp Table

Post by Adi Coh » Wed, 19 Jul 2000 04:00:00


I'm not aware of any way to populate  results of stored procedures into a
cursor.   If you want to populate the result of a stored procedure to a
temporary table,  then you have to build the table first,  and then insert
the results of the stored procedure into the table.  You can use the example
below for that stores  sp_columns into a temporary table.

--First I have to build the table.  The table structure have to be the same
as the result that I'll get from the stored procedure
create table #columns (
TABLE_QUALIFIER sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
data_type smallint,
type_name varchar(13),
percision int,
length int,
scale smallint,
radix smallint,
nullable smallint,
remarks varchar (254),
column_def varchar(254),
sql_data_type smallint,
sql_datetime_sub smallint,
char_octet_length int,
ordinal_position int,
is_nullable varchar(254),
ss_datatype tinyint)

--Inserting the results of sp_columns to the temporary table
insert #columns exec sp_columns null,null,null,null

--Here you should write the code that should work with the temporary table

--droping the temporary table
drop table #columns

  Notice that in the case of information about metadata,  most of the times
you can get it from the information_schem views.

Adi


Quote:> Is it possible to have a system stored procedure, like sp_columns, return
> its results into a cursor (preferable) or into a temporary table from
within
> T-SQL?

> If so, could you show a quick example.

> Thanks,
> Mike McCallum


 
 
 

System Stored Procedure Results to Cursor or Temp Table

Post by Michael McCallu » Wed, 19 Jul 2000 04:00:00


Thanks for the insight...
I had to make a minor modification to the create table statement, but the
concept worked great

Thanks again...

Mike


> I'm not aware of any way to populate  results of stored procedures into a
> cursor.   If you want to populate the result of a stored procedure to a
> temporary table,  then you have to build the table first,  and then insert
> the results of the stored procedure into the table.  You can use the
example
> below for that stores  sp_columns into a temporary table.

> --First I have to build the table.  The table structure have to be the
same
> as the result that I'll get from the stored procedure
> create table #columns (
> TABLE_QUALIFIER sysname,
> table_owner sysname,
> table_name sysname,
> column_name sysname,
> data_type smallint,
> type_name varchar(13),
> percision int,
> length int,
> scale smallint,
> radix smallint,
> nullable smallint,
> remarks varchar (254),
> column_def varchar(254),
> sql_data_type smallint,
> sql_datetime_sub smallint,
> char_octet_length int,
> ordinal_position int,
> is_nullable varchar(254),
> ss_datatype tinyint)

> --Inserting the results of sp_columns to the temporary table
> insert #columns exec sp_columns null,null,null,null

> --Here you should write the code that should work with the temporary table

> --droping the temporary table
> drop table #columns

>   Notice that in the case of information about metadata,  most of the
times
> you can get it from the information_schem views.

> Adi



> > Is it possible to have a system stored procedure, like sp_columns,
return
> > its results into a cursor (preferable) or into a temporary table from
> within
> > T-SQL?

> > If so, could you show a quick example.

> > Thanks,
> > Mike McCallum