[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

creating a view from a table created by a stored procedure

Posted on 2011-02-17
10
Medium Priority
?
755 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 1500 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
Technology Partners: 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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