?
Solved

T-SQL Procedure to create objects dynamically

Posted on 2012-03-22
13
Medium Priority
?
260 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 70

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 70

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 70

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 70

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard 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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

649 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