How do I get number of fields returned by query?

Hi.

I am trying to get the results of a dynamic sql statement into a #table, in order to filter them. Given that I don't know how many fields will be returned, how do I accomplish this?

I believe I need to create the #table in advance, and then run the dynamic string as part of an insert [eg 'insert into #table exec (@.sql)' ], but in order to do this I need to know how many fields are going to be returned.

The results might also be returned by a procedure rather than a simple SQL statement, so I can't just parse @.sql to get the fields.

As an example,

declare @.sql nvarchar(200)

select @.sql = 'select "a" as ColA, "b" as ColB'

exec sp_executesql @.sql

returns two columns of data.

I think I need to get the results, count the fields, create the table and finally re-run the query with an insert to poulate the table. So how do I count the columns? And for bonus points, how do I get the column names?

Many thanks,

Neil Jones

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..)

|||

Thanks for your reply

Nitin Khurana wrote:

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

The problem here is that I can't edit the query. All I know at runtime is that it's a dynamic sql statement, which could be a simple select, or an execution of a stored procedure

Nitin Khurana wrote:

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

My question is, how do I get the number of columns?

Nitin Khurana wrote:

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..

Unfortunately it's not the number of rows that is the problem.

Cheers,

Neil Jones

|||

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

|||

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

|||

Try this:

SELECT * INTO #Temp
FROM (<Your SQL Query>) as T;

Note: If the columns returned by the query aren't properly named (no column name), this will not work. Otherwise the table will be created automatically with the column names from the query. Once the table ist created, you can access the Information about the columns from system views:

USE tempdb
GO
SELECT COUNT(*) FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name like '#Test%');

This will work with MS SQL Server 2005

Regards,

Paddy

|||

Here you go with a complete sample when having the statement as a variable:

USE AdventureWorks
GO

SET NOCOUNT ON;
GO

-- Create a Table for Testing
CREATE TABLE Test
( col1 INT NOT NULL
, col2 VARCHAR(20) NOT NULL
);
GO

-- Some useful Information
INSERT INTO Test VALUES (1, 'Test1');
INSERT INTO Test VALUES (2, 'Test2');
INSERT INTO Test VALUES (3, 'Test3');
INSERT INTO Test VALUES (4, 'Test4');
INSERT INTO Test VALUES (5, 'Test5');
INSERT INTO Test VALUES (6, 'Test6');
INSERT INTO Test VALUES (7, 'Test7');
INSERT INTO Test VALUES (8, 'Test8');
INSERT INTO Test VALUES (9, 'Test9');
INSERT INTO Test VALUES (10, 'Test10');
GO

-- The dynamic sql statement
DECLARE @.sql NVARCHAR(200);
SET @.sql = 'SELECT col2, col1 FROM Test';

-- Extend the statement for creating a temporary table (Note: Use Global Temporary Table (##TableName))
DECLARE @.sql2 NVARCHAR(250)
set @.sql2 = 'SELECT * INTO ##Test FROM (' + @.sql + ') AS T;';

-- Run the extended Statement
EXEC(@.sql2);
GO

-- View the result
SELECT * FROM ##Test;
GO

-- Get Information about the Temporary Table
USE tempdb
GO

-- Column Count
SELECT COUNT(*) FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- Names of Columns
SELECT name FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- CleanUp
USE AdventureWorks
GO

-- Drop the temporary Table
DROP TABLE ##Test;
GO

-- Drop the Test Data Table
DROP TABLE Test;
GO

SET NOCOUNT OFF;

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of you table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of your table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Lucky P wrote:

Here you go with a complete sample when having the statement as a variable:

That looks great, thanks!

I think I'm going to have to accept that it's not possible with all dynamic sql statements, such as "exec xp_proc1", and take what I can.

Cheers,

Neil