?
Solved

T-SQL Procedure to create objects dynamically

Posted on 2012-03-22
13
Medium Priority
?
261 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
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

749 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