Solved

creating a view from a table created by a stored procedure

Posted on 2011-02-17
10
749 Views
Last Modified: 2012-05-11
I have defined the following procedure and it outputs data in the form of a table.  Is there any way that I can save this output to a view?  I cannot declare a temporary table in the procedure itself since the number of columns is dynamic.  

If I cannot create a view from a dynamic table output, is there any way that I can query the data in the table created by the procedure?

I am using Microsoft SQL Server 2005.
CREATE PROCEDURE ermPivotAdditionalDetails AS
DECLARE @columns VARCHAR(8000)
DECLARE @query VARCHAR(8000)
SELECT @columns =
COALESCE
(
@columns + ',[' + cast(title as varchar) + ']',
'[' + cast(title as varchar) + ']'
)
FROM 
ermMapping
GROUP BY 
title 

SET @query = 
'SELECT *
FROM (SELECT ed.appId, af.errId, custId, title, value FROM
ermAdditionalFields af, ermMapping, ermErrorDetails ed
WHERE fieldId = mapId and af.errId = ed.errId) piv
PIVOT (max(value) FOR title in (' + @columns + ') ) as chld'



EXECUTE(@query);

Open in new window

0
Comment
Question by:Emilie
  • 5
  • 3
  • 2
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
the procedure does not create a table, but just runs a query (which is build dynamically).

you could change the procedure to actually create a table:
CREATE PROCEDURE ermPivotAdditionalDetails 
AS
DECLARE @columns VARCHAR(8000)
DECLARE @query VARCHAR(8000)
SELECT @columns =
COALESCE
(
@columns + ',[' + cast(title as varchar) + ']',
'[' + cast(title as varchar) + ']'
)
FROM 
ermMapping
GROUP BY 
title 

SET @query = 
'SELECT *
 INTO some_table_name
FROM (SELECT ed.appId, af.errId, custId, title, value FROM
ermAdditionalFields af, ermMapping, ermErrorDetails ed
WHERE fieldId = mapId and af.errId = ed.errId) piv
PIVOT (max(value) FOR title in (' + @columns + ') ) as chld'

EXECUTE(@query);

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
of course, you would need to handle the drop of the "some_table_name" first, eventually pass the table name as argument etc.
0
 

Author Comment

by:Emilie
Comment Utility
Ok, so basically, everytime I want to open the view, I'd have to execute the procedure to create the table, call the view on that table name and then drop the table?  How would I then deal with concurrent access?  If someone is already using my application to access the view, then the tmp table is already created.  If someone else tries to call that same procedure, it'll give out an error stating that the table object already exists.  I can always catch the error and go directly to the view call when that occurs, but I'm worried about refreshing the data if the view if I repeatedly skip the call to the stored procedure. .
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Why not use SQL user defined Table Functions instead of the Stored Procedure? You can use that function inside a view if you like.
0
 

Author Comment

by:Emilie
Comment Utility
That sounds interesting.  Could you give me a quick example of a user defined table function?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
problem is that you want a dynamic query, so a UDF won't work.

now, back to the original problem: why do you want to create a table/view?
running the (original) procedure will run the query and return the results...

however, you might need to add 1 line of code to make sure your final select is "all" what is returned to the calling code, and no "intermediate/empty" resultsets from that initial "select" that builds up the column list.

let's see:
CREATE PROCEDURE ermPivotAdditionalDetails 
AS

SET NOCOUNT ON --- this is the line

DECLARE @columns VARCHAR(8000)
DECLARE @query VARCHAR(8000)
SELECT @columns =
COALESCE
(
@columns + ',[' + cast(title as varchar) + ']',
'[' + cast(title as varchar) + ']'
)
FROM 
ermMapping
GROUP BY 
title 

SET @query = 
'SELECT *
FROM (SELECT ed.appId, af.errId, custId, title, value FROM
ermAdditionalFields af, ermMapping, ermErrorDetails ed
WHERE fieldId = mapId and af.errId = ed.errId) piv
PIVOT (max(value) FOR title in (' + @columns + ') ) as chld'


EXECUTE(@query);

Open in new window

0
 

Author Comment

by:Emilie
Comment Utility
I want to get the data in a table/view in order to bring the data together to simplify reporting and make it more user-friendly to others querying the data.

The procedure does return the data I need, but I'm looking for a way to be able to query on that data as well, hence the need for a table or a view (unless there's another way).  The database is designed to be configurable, so every row in the table ermAdditionalFields is actually an additional column for a specific application.  The reason they are not simply stored in additional empty columns in the ermErrorDetails table they extend is because every application can have as many columns as the user wants it to have.  I didn't want to have to hold say, 50 columns, if only one application requires it, while others only need 20.

I'm beginning to wonder if I should simply use the select into table to create a new table every time a new application definition is added to the system and dynamically add columns to these tables whenever the user defines a new field for that application...  What do you think?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
You can write another wrapper stored procedure that garters all data you want. Think that you can create a temp table in the wrapper SP then populate it like:

INSERT into temptable EXEC other_storedproc....


Then add all additional info you need into it and last just return select from that table.
0
 

Author Comment

by:Emilie
Comment Utility
I'm able to create a temptable using angellll's first post, but to be refreshed, it would have to be continually recreated. Still that might be my best solution at this point.

Unless it's possible to execute a select statement to populate an in clause. . . that way, I could get the data through a single select statement without parameters, and create a view off of it.

See the last line in the code below (which doesn't work) and tell me if there's a way to populate an in clause from the result of another query?
SELECT *
FROM (
SELECT ed.appId, af.errId, custId, title, value FROM
ermAdditionalFields af, ermMapping, ermErrorDetails ed
WHERE fieldId = mapId and af.errId = ed.errId) piv
PIVOT (max(value) 
FOR title in ( SELECT distinct title from ermMapping))as chld;

Open in new window

0
 

Author Closing Comment

by:Emilie
Comment Utility
It's not exactly what I was looking for, but it's what comes the closest!  

Thanks for your help.  It got me thinking. ;)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now