March 14, 2008 at 5:57 am
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
March 14, 2008 at 7:00 am
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
March 14, 2008 at 7:12 am
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
March 14, 2008 at 7:20 am
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
March 14, 2008 at 8:15 am
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]
March 14, 2008 at 8:39 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply