Trouble with ALTER PROC

Question

Post reply

Trouble with ALTER PROC

chrisleonard

SSCertifiable

Points: 5030
More actions
March 12, 2004 at 4:25 pm

#107941

I got this question emailed to me, and thought, "why didn't they post it?" This has happened before, but I'm going to try something new here. Instead of replying via email, I'm going to double-post, with the first post being the question that was sent to me, and the second being my response. Then I will email the person and tell them to come look in the Forums for an answer. This way, they can get more than just one person's opinion, if necessary.
OK, here's the email (names removed to protect the innocent):
----- Original Message -----
From: xxxxx
To: yyyyy
Sent: Wednesday, March 10, 2004 1:50 PM
Subject: Question
Hi Chris,
I have been reading your input @sqlservercentral-com
Actually I am new to SQL Development and I have a question for you...rather silly one though;
I am having a scenario which is constantly giving me problems, let me illustrate with an example
CREATE TABLE [mytable1]
(
a char (2)
, b char (2)
, c char (2)
)
----------------
Create proc myproc1
as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[mytable1]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)
drop table [mytable1]
CREATE TABLE [mytable1]
(
a char (2)
, b char (2)
, c char (2)
)

declare @cur_c char(2)
Select @cur_c = c from mytab1
print @cur_c
end
---------------
Works fine till here...but now, When I alter the SP
with the below script.
ALTER proc myproc1
as
begin
if exists (select * from dbo.sysobjects where id =object_id(N'[mytable1]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)
drop table [mytable1]
CREATE TABLE [mytable1]
(
a char (2)
, b char (2)
, c char (2)
---NEW COLUMNS ADDED
,d char (2)
,e char (2)
)

declare @cur_d char(2)
Select @cur_d= d from mytab1
print @cur_d
end
---------------
It gives me error stating that invalid column.
However I am just altering the SP ..and new table structure will get created upon its execution. but...unfortunately ....it is not allowing me to alter the Sproc1 ?? any info will be
appreciated.
I will be waiting eagerly for your reply
Thanks
xxxxxxx

Viewing 4 posts - 1 through 3 (of 3 total)

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

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

And here is my reply. Do I get extra psycho points for responding to my own post?

Dear xxxxx,

Just looking at your sample, without understanding exactly what business or logic problem you need to solve, I may not be giving you the exact answer you want. Let me start by saying that your original sample had two different table names in it, and I had to change them all to be the same (mytable1, not mytab1) to reproduce your problem. Apparently SQL is checking column names if the table exists. You can see this in the even simpler code below:

create table mytab2 (x int)
go
create proc myproc2
as
declare @y int
select @y = y from mytab2
print @y
go

One way to work around your problem is to use sp_executesql with OUTPUT parameters. See http://support.microsoft.com/default.aspx?scid=KB;EN-US;q262499 for details, and see below for a rewrite of your code which I think will do what you want. I snuck in a call to sp_executesql to do an insert, so that there is data to show, and I also included an execution of the proc so that you can see that it works, but the main change is the call to sp_executesql to do the SELECT. I also used @d as the variable name inside the sp_executesql call (instead of calling it @cur_d, which would still work) so that you could more easily see flow of the syntax.

CREATE TABLE [mytable1]
(
a char (2)
, b char (2)
, c char (2)
)
go
Create proc myproc1
as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[mytable1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [mytable1]
CREATE TABLE [mytable1]
(
a char (2)
, b char (2)
, c char (2)
)

declare @cur_c char(2)
Select @cur_c = c from mytable1
print @cur_c
end
go
ALTER proc myproc1
as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[mytable1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [mytable1]
CREATE TABLE [mytable1]
(
a char (2)
, b char (2)
, c char (2)
---NEW COLUMNS ADDED
,d char (2)
,e char (2)
)

exec ('insert into mytable1 values (''a'', ''b'', ''c'', ''d'', ''e'')')
declare @cur_d char(2)
exec sp_executesql N'Select @d = d from mytable1', N'@d char(2) OUTPUT', @d = @cur_d OUTPUT
print @cur_d
end
go
exec myproc1

So now, I'm off to email this person and to tell them to come here to see this. Have a good weekend, everybody.

Cheers,

Chris

HansVE Mr or Mrs. 500 Points: 524 More actions · Answer 2

Could just read "Deferred Name Resolution and Recompilation" in BOL.

There is problem with instantiating local variable as here. If table has more then 1 row, value may be unexpected.

chrisleonard SSCertifiable Points: 5030 More actions · Answer 3

> If table has more then 1 row, value may be unexpected.

Yes, that's true, especially since there's no ORDER BY clause (which would make the result predictable). But in this case, where I'm just trying to show the guy the technique for sp_executesql with OUTPUT parameters, it will always be the same result, so it's kind of a moot point.

Remember, I'm just posting this here primarily because I think people should post to the forums, not to individuals' inboxes. But unfortunately, I haven't been able to notify the person who originally emailed me - I think Hotmail may be blocked from where I am right now...

Chris