Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlslick
Yak Posting Veteran
83 Posts |
 Posted - 2011-10-27 : 09:14:46
|
| Hello gang!Is it possible to create a View using a Stored Procedure? The reason I ask is because you cannot use temporary tables in a View. Also, I want to use a View so I can create a Data Connection from Excel to SQL Server and the only object types available to connect to are TABLE and VIEW. Stored Procedures are not available.Thanks in advanced!J |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-27 : 09:17:24
|
| You can do it in dynamic sql.You can also populate a table and jion to that in a view if you want to restrict rows dynamically.If you want to change the structure of the data returned by the view then I would look for another way.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-27 : 10:22:14
|
quote:
Originally posted by sqlslick
Hello gang!Is it possible to create a View using a Stored Procedure? The reason I ask is because you cannot use temporary tables in a View. Also, I want to use a View so I can create a Data Connection from Excel to SQL Server and the only object types available to connect to are TABLE and VIEW. Stored Procedures are not available.Thanks in advanced!J
Why not just populate a table with the SP and create a connection to the table with Excel ?PBUH |
|
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2011-10-28 : 09:12:52
|
| After giving this more thought, I don't think a View will work because it will be based on Temporary tables, but I could be wrong? I might just create a Table as Sachin suggested using the Stored Procedure and populate it using the temporary tables. Then, I can create the Data Connection from Excel directly to the new table I have created.To answer Brett's questions, I would create the View if I didn't need to use Temporary tables. Also, I need the data in Excel so it can drive other sheets in the same Workbook.Thank you all for your feedback!!J |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2011-10-31 : 08:56:51
|
| Sorry if I am not being descriptive enough, but believe me when I say that this is a complex scenario that is difficult to explain in writing, but I will do my best.First of all, I am only working with 5 tables. The tables that I am working with are easy to join with the majority of the data in 2 tables. The problem starts with how the data is stored in these 2 tables. The data is stored as rows but I need the output as columns joined to the other tables. The database that I am querying is SQL Server 2000 so the PIVOT function is out of the question. I've tried other suggestions from wizards in this forum but it is difficult to group the data the way it is stored in the database in relation to the way that I need it displayed.The purpose of this solution is to automate quality audits of the data that is being inputed into the system. I need to check for NULL values, missing rows, if certain groups of rows are present, etc. I am not sure if you're getting the idea but to summarize, I am trying to do many things that are just simply too complex to acomplish with a single script and not have to incorporate the use of temporary tables. I, however, have made great progress, thanks to all of your posts.Thanks again and cheers!J |
|
|
|
|
|
|
|
|
Can you create a VIEW using a Stored Procedure?