How to skip checking the existence of objects when creating Stored Procedure

Question

How to skip checking the existence of objects when creating Stored Procedure

Viewing 7 posts - 16 through 21 (of 21 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Roust_m (10/1/2009)
The reason I want this is because I need to run an import script which runs better when indexed views are not there yet, which relies on a stored procedure script (one of the stored procedures used in the import script.) Some of the other stored procedures rely on the views, which should not be there yet for the import script to work. I understand that I could split the stored procedures script into the part used in the import script and the rest and break the circle, but I do not want to do this.

I'm not sure if it would help you but these things are only checked at the time of the SP's creation. The columns don't have to exist after that. (IE, you may be able to alter the table, create the columns etc., create the SP and then drop them again so they're added when you need them added).

Not exactly the most elegant or maintainable solution, but it might solve the current issue.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

These will compile fine if the entire table does not exist. They will not compile if the table exists but the column you're referencing does not. It will error. Try it.

Yes, thaks Garadin. Its a new thing that I learnt 🙂 It does error out if there is table object and the column is not there. I am running it on SS2k5 (compat mode 90)

---------------------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

Garadin (10/2/2009)

These will compile fine if the entire table does not exist. They will not compile if the table exists but the column you're referencing does not. It will error. Try it.

I agree that it will error. I'm just not sure why you would want to do such a thing to begin with. You're not adding temporal based columns are you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 4

Jeff Moden (10/3/2009)

Garadin (10/2/2009)
These will compile fine if the entire table does not exist. They will not compile if the table exists but the column you're referencing does not. It will error. Try it.
I agree that it will error. I'm just not sure why you would want to do such a thing to begin with. You're not adding temporal based columns are you?

If you have a look at the OP's reply (i think it's the fourth post or something) they say:

The reason I want this is because I need to run an import script which runs better when indexed views are not there yet

So I suggested maybe that dropping the indexes on the views while importing would be a better idea than dropping the views entirely...

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

The point Seth was trying to make is that if a column in a table doesn't exist but the table does, you'll get an error. Appologies for not following all the posts in this thread, but I saw that and figured that the OP was trying to build columns on the fly as well as stored procedures. I was curious as to why someone might be building columns on the fly which, with some small exception, is usually a really bad idea.

Heh... I also missed it... I thought it was the op that made the post and not Seth. Not enough coffee.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 6

Jeff Moden (10/4/2009)
The point Seth was trying to make is that if a column in a table doesn't exist but the table does, you'll get an error. Appologies for not following all the posts in this thread, but I saw that and figured that the OP was trying to build columns on the fly as well as stored procedures. I was curious as to why someone might be building columns on the fly which, with some small exception, is usually a really bad idea.
Heh... I also missed it... I thought it was the op that made the post and not Seth. Not enough coffee.

Crack open the Lavazza. 😀

I just thought I'd try and make the point because I thought this thread was possibly leading the OP up the garden path a bit...

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 7

Nothing like a little coffee in the garden. 🙂

Everytime I've seen such a post, it's because the OP is trying to add columns to a table for something like "month" or "day". Guess we need to find out which garden we're in for sure...

@Roust_m,

Can you be a little more specific about why you need to do what you ask? There may be a way around the whole thing...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)