1

Here is the problem.

I have a stored procedure that transforms normalized data into a standard dataset.

I need to report on some data which consists of a view, and two data sets from the stored procedure with different parameters being submitted to the stored procedure which returns different columns.

Example

If My view Contains something like:

ID  Title   Status  Date
1   How To Party Like a rockstar    Approved    7/15/1989
2   Too much of a good thing    Approved    7/25/2001
3   Needs More Cowbell  Denied  11/11/2011
4   Here Today Gone Tommorrow   Approved    8/13/1969
5   The way She moves   Approved    12/13/2011

And the procedure run with one paramater returns:

ID  Do you like the ice Cream?  How much would you pay for the ice cream?
1   Yes 2
2   Yes 5
3   Yes 7
4   No  2
5   No  3

And the same procedure run with a different parameter returns:

ID  Total Number of Bell Peppers    Total Number of Apples  Total Number of Oranges
1   7   6   6
2   6   8   8
3   32  7   5
4   7   3   1
5   12  1   1

Assuming that the ID column is a key that can be used to join the data sets, how would i go about getting:

ID  Title   Status  Date    Do you like the ice Cream?  How much would you pay for the ice cream?   Total Number of Bell Peppers    Total Number of Apples  Total Number of Oranges
1   How To Party Like a rockstar    Approved    7/15/1989   Yes 2   7   6   6
2   Too much of a good thing    Approved    7/25/2001   Yes 5   6   8   8
3   Needs More Cowbell  Denied  11/11/2011  Yes 7   32  7   5
4   Here Today Gone Tommorrow   Approved    8/13/1969   No  2   7   3   1
5   The way She moves   Approved    12/13/2011  No  3   12  1   1

Keeping in mind that the stored procedures cannot be done with inline sql due to the nature of the normalization and the transformation of the normalization, and I do not believe that they can be done as a table variable udf due to the fact that the stored procedure returns a variable number of columns depending on the parameter that is passed to it when it is run (if someone can prove me wrong on this and point me in the direction of a dynamic column table-valued udf i would be very appreciative)

If i can someway bring the view and the two stored procedures into one stored procedure and return the dataset in that fashion, that would be my ultimate goal.

I also need to mention that Ad Hoc Distributed Queries cannot be turned on on this server (not my decision)

Resolution

With a little help from Stuart Ainsworth's answer I was able to work it out. My initial problem with the whole thing was that the columns were dynamic for each of the crosstab tables. so as a part of that I had created a function that returned the columns as a comma deliminated string...

        DECLARE
            @PivotColumns VARCHAR(MAX)

        SET @PivotColumns = dbo.fnGetFormPivotColumns(9)

Where the 9 in this case is the parameter which lets me know which item i'm building for. From there it was just a repeat of the functionality in the base sp to build the temp tables dynamically so I came up with this:

IF object_id('tempdb..#temp_DEP') IS NOT NULL
    DROP TABLE #temp_DEP


CREATE TABLE #temp_DEP (APPLICATION_ID int)
EXEC ('ALTER TABLE #temp_DEP ADD ' + @PivotColumns)

INSERT INTO #temp_DEP
    EXEC GetFormCrossTab 9

A simple SELECT * FROM #temp_DEP shows that I'm getting exactly what I want.

All I have to do is repeat the process for the other parameter and I get my two data sets within the same stored procedure.

Patrick
  • 7,512
  • 7
  • 39
  • 50
  • I think the challenge you will run into is your variable output from the proc. http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure If it was a consistent shape, you could insert the results into a table and mash results together like that. Otherwise, I think you're looking at copy-paste inheritance to bring all bits of logic together. – billinkc Dec 07 '11 at 20:32
  • I should probably add unfortunately that Ad Hoc Distributed queries is turned off and I will not be able to turn it on so openrowset/opendatasource are not options. – Patrick Dec 07 '11 at 20:39

3 Answers3

1

Could you have the stored procedure with the conditional result set insert its results into one of two scratch tables, depending upon the parameter that was passed in?

You could then create another stored procedure which joins your view to the scratch tables.

E.G, this would be the new stored procedure:

exec stored_proc_with_conditional_output

select * from
view v
left outer join scratch1 s1 on v.ID = s1.ID
left outer join scratch2 s2 on v.ID = s2.ID
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jmacinnes
  • 1,589
  • 1
  • 11
  • 21
  • I could see creating a temp table using the parameter to make it unique and then querying from it... but wouldnt the temp table just be destroyed when the calling sp is done? its a thought though... maybe a global temp table would survive long enough to be of use... – Patrick Dec 07 '11 at 20:49
  • Temp tables might be scoped to the individual sproc. If so, you would have to use an actual table and consider the concurrency issues if your sproc could be called more than once at a time. If you are using SQL Server 2008 and above, you could also look into the possibility of using table variables. – jmacinnes Dec 07 '11 at 20:54
  • I would use a table variable, but i cannot find a way to decare them dynamically... even though i'm building the temp table dyanmically... if i did figgure it out how woudl you suggest i use it? as an output parameter from the stored procedure? would that even work? – Patrick Dec 07 '11 at 21:03
  • so far what Ive read states that table variables as parameters in stored procedures are input readonly only. – Patrick Dec 07 '11 at 21:13
  • Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Dec 07 '11 at 22:11
1

You could build a stored procedure and use an IF statement to satisfy each of your parameters, eg:

CREATE PROC someproc @parameter AS

CREATE TABLE #scratch1 (columns)...
CREATE TABLE #scratch2 (columns)...

IF @parameter = 1
BEGIN 
INSERT INTO #scratch1 
exec oldproc @parameter
END

IF @parameter = 2
BEGIN 
INSERT INTO #scratch1 
exec oldproc @parameter
END


SELECT *
FROM view v
LEFT JOIN #scratch1 s1 ON v.ID =s1.ID
...etc
Stuart Ainsworth
  • 12,792
  • 41
  • 46
  • I would prefer to build the temp table dynamically... there could theoretically be an unlimited number of parameters ... then again for this report it is only going to be 2 specific items... so it might be doable for this particular report, i was just hoping for a more dynamic solution. – Patrick Dec 08 '11 at 12:23
  • not to mention that i one of the stored procedures returns something like 13 columns and the wording for the column names tends to change often – Patrick Dec 08 '11 at 12:36
  • actually... if i take the code from the base sp that builds the temp table dynamically... and put it into this parent sp for the report it should work... ill take a crack at it. – Patrick Dec 08 '11 at 12:44
0

You should run the queries and smash them together with linq. This will be quite fast and can even be setup to run "on demand" so the memory and processing footprint should be small.

Do you need an example of how to do this -- or is the suggestion enough?

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • If i was using this in .net it probably wouldn't be a problem... but they need to go to a SSRS report... and I'm not to sure about trying to implement linq into ssrs (if its even possible, i dont know) – Patrick Dec 07 '11 at 20:49
  • @Patrick - point taken -- I'll leave it here as a future reference to others who might find it useful. – Hogan Dec 07 '11 at 21:27