[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

creating a view from a table created by a stored procedure

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
Emilie
Asked:
Emilie
  • 5
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
of course, you would need to handle the drop of the "some_table_name" first, eventually pass the table name as argument etc.
0
 
EmilieAuthor Commented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
lcohanDatabase AnalystCommented:
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
 
EmilieAuthor Commented:
That sounds interesting.  Could you give me a quick example of a user defined table function?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
EmilieAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
EmilieAuthor Commented:
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
 
EmilieAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now