locked
Creating a view inside a stored procedure RRS feed

  • Question

  • Hello all,

    I have a stored procedure that is creating some temp tables from the underlying database tables and I want to create a view from one of the temp table columns.At the end of the Stored procedure I have included like this

    declare

    @sqlcode varchar(max)

    select * into ILI_Temp3 from #ILI_ML_Summary

    @sqlcode = 'create view [dbo].[v_ILI_MetalLoss_Summary] as select * from ILI_Temp3'


    exec

    (@sqlcode)               Initially I was using #ILI_ML_Summary instead of ILI_Temp3 but It was throwing an error as cannot create a view from a temp table. Then I have copied the records from temp table to ILI_Temp3 and using this table to create the view but it is agaimn throwin an error invalid object name ili_temp3. When I execute whole script inside the Stored procedure I am not getting any error and the view is getting created. But when I place the script inside the SP and execute the SP then I am getting the error as invalid object ili_temp3.  Please help me how to achieve this.

     

    select

    Wednesday, September 12, 2012 3:19 PM

Answers

  • >> I have a stored procedure that is creating some temp tables from the underlying database tables ..<<

    Stop doing that. This is how bad programmers fake the use of scratch tapes in SQL. This is not 1950's computing any more. You do not even know that rows are not records. 

    >> .. and I want to create a VIEW from one of the temp table columns. <<

    And it just gets worse! This attempt at modifying the schema will be executed over and over. And you are doing it with dynamic SQL! Since we have no idea what the procedure looks like, what is it supposed to do and anything about it, how can we help you? 

    Unlike COBOL, Autocoder, and other procedural language programming models, the SQL model has a separation of the User privileges and the Admin privileges. Users have the DML statements; they cannot create schema objects. Your approach to SQL and RDBMS is [i]fundamentally wrong[/i]. 

    Again, where is the DDL? The specs? Please read any book on RDBMS before you try to code again. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Wednesday, September 12, 2012 9:36 PM
    • Marked as answer by Kalman Toth Tuesday, September 18, 2012 2:31 PM
    Wednesday, September 12, 2012 9:22 PM

All replies

  • I don't think this problem has a solution as presented. You can not create a view for the non-existing table or for temporary table. So, if that table doesn't exist and the SP doesn't know about it, you will not be able to create the view.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    • Edited by Naomi N Wednesday, September 12, 2012 3:29 PM
    Wednesday, September 12, 2012 3:23 PM
  • Instead of doing these, you can

    1) Keep the table ILI_temp3 in the database

    2) Each time only truncate it and then fill it with the data in the SP

    3) Keep the view as it is in the database and no need to create it in the SP


    Many Thanks & Best Regards, Hua Min

    Wednesday, September 12, 2012 3:26 PM
  • >> I have a stored procedure that is creating some temp tables from the underlying database tables ..<<

    Stop doing that. This is how bad programmers fake the use of scratch tapes in SQL. This is not 1950's computing any more. You do not even know that rows are not records. 

    >> .. and I want to create a VIEW from one of the temp table columns. <<

    And it just gets worse! This attempt at modifying the schema will be executed over and over. And you are doing it with dynamic SQL! Since we have no idea what the procedure looks like, what is it supposed to do and anything about it, how can we help you? 

    Unlike COBOL, Autocoder, and other procedural language programming models, the SQL model has a separation of the User privileges and the Admin privileges. Users have the DML statements; they cannot create schema objects. Your approach to SQL and RDBMS is [i]fundamentally wrong[/i]. 

    Again, where is the DDL? The specs? Please read any book on RDBMS before you try to code again. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Wednesday, September 12, 2012 9:36 PM
    • Marked as answer by Kalman Toth Tuesday, September 18, 2012 2:31 PM
    Wednesday, September 12, 2012 9:22 PM