Solved

creating a view from a table created by a stored procedure

Posted on 2011-02-17
10
752 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34917457
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34917471
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
ID: 34917668
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:lcohan
ID: 34918403
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
ID: 34918790
That sounds interesting.  Could you give me a quick example of a user defined table function?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34918886
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
ID: 34919032
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 40

Expert Comment

by:lcohan
ID: 34919134
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
ID: 34920078
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
ID: 34925335
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 84
Set the max value for a column 7 41
How to import SQL 2000 database to SQL 2014 5 157
My Query is not giving correct result. Please help 5 55
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

749 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