should this t-sql raise error?

Question

should this t-sql raise error?

pedro.ribeiro

Hall of Fame

Points: 3201
More actions
April 13, 2009 at 10:55 am

#208318

Hi masters,
I have the t-sql below.
This t-sql is generating an error, because i don't have the tables:
bulk_contribuintesest
bulk_contribuintee
bulk_contribuinteinst
If i have the create of a view inside a EXEC , will the query analiser still parse the t-sql inside the EXEC?
Is there any way for not parsing while creating?
t-SQL :
exec('create VIEW CTBS_BULK_VALIDOS
AS
select a.rgc as rgc1, a.nifctb as nifctb1
from bulk_contribuintesest as a
where
a.codrf is not null and a.rgc is not null
and a.valido_bulk is null and (nifctb like ''2%'' or nifctb like ''1%'')
union all
select b.nif_antigo as rgc2, b.nifctb as nifctb2
from bulk_contribuintee as b
where
b.codrf is not null and b.nif_antigo is not null
and b.valido_bulk is null and nifctb like ''5%''
union all
select c.nif_antigo as rgc3, c.nifctb as nifctb3
from bulk_contribuinteinst as c
where
c.codrf is not null and c.nif_antigo is not null
and c.valido_bulk is null and nifctb like ''7%''
')
Tks,
Pedro

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can't create a view on tables that don't exist. Won't work. You need to create the tables first, then create the view.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 2

Although I'm not sure why you would want to do this there is a way. You can create a stored procedure which calls your exec('create view...') statement.

create procedure createMyView as

exec ('create view foobar as Select * from TableNotExistsYet')

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 3

pedro.ribeiro (4/13/2009)
Hi masters,
I have the t-sql below.
This t-sql is generating an error, because i don't have the tables:
bulk_contribuintesest
bulk_contribuintee
bulk_contribuinteinst
If i have the create of a view inside a EXEC , will the query analiser still parse the t-sql inside the EXEC?
Is there any way for not parsing while creating?
...

You can also use something like:

IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'bulk_contribuintesest') AND type in (N'U'))

-- Using dynamic SQL protects the query from syntax check failure

-- when the tables don't exist in the source

EXEC sp_ExecuteSQL N'CREATE VIEW ...'

We use this sort of structure to dynamically select columns when they are present in the source, and provide substitute values otherwise.

I'm not sure if the sp_ExecuteSQL is needed...

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi Pedro,

You need to create the permanent VIEW Table?

Or

The data retrieved thro dynamical tables?

ARUN SAS

pedro.ribeiro Hall of Fame Points: 3201 More actions · Answer 5

Hi Masters,

Thank you very much for all the advices.

I made it, other way....

I have created the table (bulk_contribuintesest), taht the view needs to be constructed.

Then, i crate the view. After the view criation, i delete this table.

It's other way of doing it.

Everytime that i want to use this view, i recreate the table.

tks,

Pedro

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 6

pedro.ribeiro (4/16/2009)
Hi Masters,
Thank you very much for all the advices.
I made it, other way....
I have created the table (bulk_contribuintesest), taht the view needs to be constructed.
Then, i crate the view. After the view criation, i delete this table.
It's other way of doing it.
Everytime that i want to use this view, i recreate the table.
tks,
Pedro

Why can't you just truncate the table rather than dropping it?