Get the Number of Columns from a Dataset

Question

Post reply

Get the Number of Columns from a Dataset

Neuro

Ten Centuries

Points: 1063
More actions
March 14, 2008 at 5:57 am

#204242

I have a Stored Proc That returns either 1, 3, 5 columns:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_RS_TESTCOLS]
@account int =1
AS
If @Num = 1
SELECT 'A'
If @Num = 2
SELECT 'A','B'
If @Num = 3
SELECT 'A','B','C'
What I would like to do is be able to determine the number of columns returned
so that the it can be held in either a table with 1 2 or 3 columns
Essentially I could have another stored procedure to determine the number of columns returned but I'd like to be able to put this into custom code.
I think I've had enough thinking time and im still no furher forward...
Any help would be appreciated
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

How about using an OUTPUT parameter in your stored procedure to return the column count?

For example:

CREATE PROCEDURE usp_myProc (@p1 INT, @p2 INT OUTPUT)

AS

IF @p1 = 1

BEGIN

SELECT col1 FROM myTable

SET @p2 = 1

END

IF @p1 = 2

BEGIN

SELECT col1,col2 FROM myTable

SET @p2 = 2

END

-- Calling usp_myProc

DECLARE @p2 INT

EXEC usp_myProc 1, @p2 OUT

PRINT @p2

Neuro Ten Centuries Points: 1063 More actions · Answer 2

that isn't really what I need , as i said I could use another sp to tell me the number of Columns,

But what I need is for the Number of columns to be calculated in reporting services as i still would like the original sp to return the data from the column(s)

this data would then go into a table, I wanted to be able to let the table change dimensions to accomodate the difference in Column Numbers

Neuro Ten Centuries Points: 1063 More actions · Answer 3

Maybe I got too stuck on my train of thought and just wanted to focus on this one idea.

What i could also do is add the column names manually, into the table that would yeild the result,

and as long as I put all the fields into the table , it won't matter that the dataset only includes 1 column out of the three , becuase data wont be generated and thus not used in the table

then I could set those empty columns to a width of 0 so that they dont appear!

okay so it gives an error, but for the moment it works.

however I'd still be interested to know how I can interrogate the dataset before it is used in a container

toolman-352714 SSCrazy Points: 2495 More actions · Answer 4

I doubt you'll be able to actually programmatically set the number of table columns based on the dataset. At least I've never seen the means to do so outlined here or in any other forums...and the question gets asked a lot.

That being said, another alternative to your solution is to condition the visibility of the columns based on a value.

Make sure your dataset contains a field with the total number of columns returned. Create your table with the max number of columns. Next, using the column handle, select an entire column. Then, under the Visibility>Hidden property place something like this expression:

=IIF(Fields!ColNum.Value >= 2, False,True)

for the 2nd column. Repeat for all subsequent columns e.g.

=IIF(Fields!ColNum.Value >= 3, False,True), etc.

I don't know if this is easier or more efficient than your 0 width method but I know either will work and it's always good to have choices. 🙂

HTH

[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]

Neuro Ten Centuries Points: 1063 More actions · Answer 5

ooops closed browser before sending , strange saw your nickname on another website on custom code , funny that lol

thanks for that, I was about to dismiss that idea but now I realise I dont have to!!! 🙂 as in the end the data is to be exported to CSV, it has to be able to hide the coulmn showing the number of columns, but then they dont have to be shown anyway.....

Fianlly brain started functioning again amazing what 2 cans of relentless can do for you.