none
Creating a dynamic View (union) with a stored procedure

    问题

  • Hi all!

    I'm developing a program with the function to do imports from one database (Microsoft SQL Server Database) into another database (Nexus Database).

    In the program itself the user selects the columns of the tables he wants to import. They should be brought together in one big table, so that each row consist one record. The data of the columns should be related to the specific record, so that the right data will be imported in the Nexus Database later. I want to create a dynamic solution, so that the program can do imports from any Microsoft SQL Server Database.

    My problem is, that I don't know how to create the table with the columns the user wants to export. At first I tried it to realize with joins, but I think its really hard to get a dynamic solution with it. After this I tried to do it with the select statement. I selected some columns from some tables and tested the command in MS SQL Server Management Studio. It lasted really long or even not came to an end. Furthermore there were many rows that consisted the same data, except in one column. So this solution was not really useful.

    Today I got a good advice from a professor of my university. He said I should realize it with the use of dynamic views and the Union-Statement. These commands should be executed with the help of a stored procedure.

    So does anybody know how to solve this problem?

    • 已移动 Bob BeaucheminMVP 2012年7月5日 17:20 Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    2012年7月5日 14:49

答案

  • With "SQL Window" I was simlpy thinking of something like the query editor in Management Studio. But the picture you posted is something similar.

    If you want to guide users with displaying available tables and columns, you can use something like the Query Designer in SSMS. Well, in my opinion the Query Designer is an awful tool, at least in the context it appears. SSMS is a tool for developers, but here we are talking end users.

    Microsoft used to have MS Query, which had a similar designer, but I think it has been discountinued. But there are likely to be other designer components out there, free or commercial. Implmenting your own query designer seems to me like a waste of energy. Although, I get the impression that this is part of an academic endeavour and not paid work.

    Overall, it's difficult to give advice when the spec seems to be so broad and open-ended.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月6日 13:11

全部回复

  • It is not clear to me, why the selection of data needs to be materialised in a table. In the end you will get a SELECT statement, and export could run that SELECT. The only reason to save the data would be that the result of the SELECT could change, but the user wants to exports the current snapshot.

    I want to create a dynamic solution, so that the program can do imports
    from any Microsoft SQL Server Database.

    This is a fairly tall order, and there is reason to question whether this level of ambition maps to the actual user requirements.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月5日 22:22
  • Good Morning, Erland Sommarskog!

    At first: Thanks for your reply!

    I forgot to say, that the user can decide wether he wants to do a filtering by a specific column and also to do a calculation on a specific column. Especially for the filtering I think one table with all selected columns is useful.

    Or what do you think ?

    ... If I will find the time I will upload a picture to explain my problem in a more understanding way at the weekend.

    2012年7月6日 8:32
  • Still sounds like a query to me. There could be reasons to materialise if the user wants to furhter query the results and the original query took a lot of time.

    But in such case, why not offer the user an SQL window? :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月6日 9:08
  • I googled for SQL-window and found this picture:

    So you recommend me, that I should integrate the SQL window in my program and let the user select the columns he wants for his import. The filtering and the calculations also can be done with the SQL window as I assume.

    I also assume that there is a way to save this command in a stored procedure (SP) when its finished. So the user has to create on-time the command, save it via SP in the Microsoft SQL Server Database and can execute it then anytime he wants.

    Is there code in visual basic for creating such a sql window and is it free to use or do I have to create it on my own? 

    2012年7月6日 11:56
  • With "SQL Window" I was simlpy thinking of something like the query editor in Management Studio. But the picture you posted is something similar.

    If you want to guide users with displaying available tables and columns, you can use something like the Query Designer in SSMS. Well, in my opinion the Query Designer is an awful tool, at least in the context it appears. SSMS is a tool for developers, but here we are talking end users.

    Microsoft used to have MS Query, which had a similar designer, but I think it has been discountinued. But there are likely to be other designer components out there, free or commercial. Implmenting your own query designer seems to me like a waste of energy. Although, I get the impression that this is part of an academic endeavour and not paid work.

    Overall, it's difficult to give advice when the spec seems to be so broad and open-ended.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月6日 13:11