?
Solved

T-SQL Procedure to create objects dynamically

Posted on 2012-03-22
13
Medium Priority
?
258 Views
Last Modified: 2012-04-15
Hi,
I am using a procedure  below to create a dyncamic view. The tablenames will change over time  and need to be dynamic. I'm receiving an error below.

Msg 102, Level 15, State 1, Procedure create_view, Line 1
Incorrect syntax near ')'.

create proc create_view()
as
begin
declare @sql nvarchar(4000)
select @sql = 'exec dbname1'+ '.dbo.sp_executesql(''create view xyz as SELECT * from table1 Union SELECT * from table2'' )'
print( @sql )
exec( @sql )
end
0
Comment
Question by:crazywolf2010
[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
  • 6
  • 4
  • 3
13 Comments
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37752065
You cant create a VIEW inside a Stored procedure.. Few DDL are not possible in Stored Procedure..

Please refer below URL for more details:
http://msdn.microsoft.com/en-us/library/ms187926.aspx
0
 

Author Comment

by:crazywolf2010
ID: 37752126
Hi,
Can you please suggest me another way to do this?
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37752141
The way the Stored procedure samples is illustrated above seems, there is no need for a stored procedure rather. You can directly go ahead create a VIEW by yourself using the Tables you like.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Accepted Solution

by:
crazywolf2010 earned 0 total points
ID: 37752175
Hi,
Code below worked for me. So one can create a view inside procedure. I am now trying to work out passing tablenames as parameter.

alter proc test_proc as
begin
   exec('create view test_view as SELECT * from table1 Union ALL SELECT * from table2')
end
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37752286
i still don't understand, why do you go for a Stored procedure for creating a VIEW, which is not a dynamic query or View rather.

Though the above View was created using Stored Procedure.You cant execute the same Stored procedure again, which eventually throws an error.
0
 

Author Comment

by:crazywolf2010
ID: 37752622
Hi,
I did say the tablenames will be dynamic  in view defn earlier. I need to put that in place.

I ran this procedure around 10 times and it all works fine on SQL 2008R2.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37752696
Not sure what walter is talking about.

The first error is because of this:

create proc create_view()

You can't have the ( ) unless you also declare parameters.  So do this instead:


create proc create_view
as
...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37752703
You can certainly use *dynamic* SQL (as in your example) to create a view from within a proc.

Yes, if the view already exists you will get an error.

If that is a possibility, add an EXISTS() check in a separate statement before the one that creates the view and condition execution of the view based on the results returned by the EXISTS() check. [If you need guidance on setting that up, just let me know.]
0
 

Author Comment

by:crazywolf2010
ID: 37752736
Thanks ScottPletcher. I just did that.

Do you have a snippet to accept tablenames (n tables) as a input variable and replace them in create view stmt?

Thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753196
Not 100% sure what you mean.

You mean to be the source table(s) for the view?

I usually use a template-style approach, then substitute in values from the params.

For example:

DECLARE @sql varchar(8000)
SET @sql_template = 'exec dbname1'+ '.dbo.sp_executesql(''create view xyz as SELECT * from $table1$ Union SELECT * from $table2$'')'


SET @sql = REPLACE(REPLACE(@sql,
    '$table1$', COALESCE(@param_table1, 'table1')),
    '$table2$', COALESCE(@param_table2, 'table2'))

EXEC(@sql)
0
 

Author Comment

by:crazywolf2010
ID: 37753633
I tried above code and compiled ok.
Unfortunately when I tried executing it, nothing is happening.
How can I catch and print errors as well as variable values?
Is there an easy way to debug the procedure under SSMS?

Thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37754068
First easy thing to do is print the statement before it runs.

Then you can run it stand-alone to see the error.

You can add TRY ... CATCH if needed to capture and display the actual error.


DECLARE @sql varchar(8000)
SET @sql_template = 'exec dbname1'+ '.dbo.sp_executesql(''create view xyz as SELECT * from $table1$ Union SELECT * from $table2$'')'


SET @sql = REPLACE(REPLACE(@sql,
    '$table1$', COALESCE(@param_table1, 'table1')),
    '$table2$', COALESCE(@param_table2, 'table2'))

PRINT @sql

EXEC(@sql)
0
 

Author Closing Comment

by:crazywolf2010
ID: 37847948
Answered Correctly
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

777 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