ALTER table in a SP

Hi. I have a stored procedure where I'm creating a Temporary table,
inserting data into it from another stored procedure, than altering it by
adding another column, then doing more stuff within the temp table.
My problem is after my Alter statment, I need to use a GO statement to the
table will be altered...but then the stored procedure thinks it's done and
doesn't run anything else.
Either: How do I insert the new column in after inserting into the table?
OR create the table with the extra column and tell the SP to run...and not
bomb when it thinks it's missing a column?
Thanks.
-Rob T.
PS...it work fine just in the Query Designer, but the GO is killing me when
I'm created the SP...
Here's a snippit of the SP:
CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
-- Create a #Temp table --
create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
int, Level1 int, ParentSeq int)
insert into #Temp exec GLGroup @.GrpID
ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
GO
--Figure out all the values --
... a bunch of other stuff in here...
select * from #Temp
drop table #Temp
GOWhy not just create the table with the ultimate # of columns from the
get-go'
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Hi. I have a stored procedure where I'm creating a Temporary table,
> inserting data into it from another stored procedure, than altering it by
> adding another column, then doing more stuff within the temp table.
> My problem is after my Alter statment, I need to use a GO statement to the
> table will be altered...but then the stored procedure thinks it's done
> and doesn't run anything else.
> Either: How do I insert the new column in after inserting into the table?
> OR create the table with the extra column and tell the SP to run...and
> not bomb when it thinks it's missing a column?
> Thanks.
> -Rob T.
> PS...it work fine just in the Query Designer, but the GO is killing me
> when I'm created the SP...
> Here's a snippit of the SP:
> CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
> -- Create a #Temp table --
> create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
> int, Level1 int, ParentSeq int)
> insert into #Temp exec GLGroup @.GrpID
> ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
> GO
> --Figure out all the values --
> ... a bunch of other stuff in here...
> select * from #Temp
> drop table #Temp
> GO
>|||You can't put GO in an SP.
The solution is to do it this way:
CREATE TABLE #Temp (grpdtlid INT, seq INT, dtldesc VARCHAR(40), parentid
INT, level1 INT, parentseq INT, total NUMERIC(18,2) NOT NULL DEFAULT 0)
INSERT INTO #Temp
(grpdtlid, seq, dtldesc, parentid, level1, parentseq)
EXEC GLGroup
... etc
David Portas
SQL Server MVP
--|||I tried that...but when I run "insert into #Temp exec GLGroup @.GrpID" it
bombs since the number of columns I'm getting from GLGroup doesn't match the
number of columns I'm inserting to...
I thought I was being clever by creating a table that matched the structure
of the SP and then altering it. ;-)
Is there a way to insert into my table where the number of columns don't
match?
"Michael C#" <howsa@.boutdat.com> wrote in message
news:u0B6E4aUFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why not just create the table with the ultimate # of columns from the
> get-go'
> "Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
> news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||Specify column names in the INSERT.
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:%23YgU27aUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I tried that...but when I run "insert into #Temp exec GLGroup @.GrpID" it
>bombs since the number of columns I'm getting from GLGroup doesn't match
>the number of columns I'm inserting to...
> I thought I was being clever by creating a table that matched the
> structure of the SP and then altering it. ;-)
> Is there a way to insert into my table where the number of columns don't
> match?
>
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:u0B6E4aUFHA.3620@.TK2MSFTNGP09.phx.gbl...
>|||Well, you could do something like
SELECT *, CONVERT(datatype_of_new_col, 'newColVal') INTO
#secondTempTable
FROM #firstTempTable
Or try to avoid the need for the additional columns in the first place
Or create a second stored procedure that includes the additional columns you
want
Or see http://www.sommarskog.se/share_data.html
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Hi. I have a stored procedure where I'm creating a Temporary table,
> inserting data into it from another stored procedure, than altering it by
> adding another column, then doing more stuff within the temp table.
> My problem is after my Alter statment, I need to use a GO statement to the
> table will be altered...but then the stored procedure thinks it's done
> and doesn't run anything else.
> Either: How do I insert the new column in after inserting into the table?
> OR create the table with the extra column and tell the SP to run...and
> not bomb when it thinks it's missing a column?
> Thanks.
> -Rob T.
> PS...it work fine just in the Query Designer, but the GO is killing me
> when I'm created the SP...
> Here's a snippit of the SP:
> CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
> -- Create a #Temp table --
> create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
> int, Level1 int, ParentSeq int)
> insert into #Temp exec GLGroup @.GrpID
> ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
> GO
> --Figure out all the values --
> ... a bunch of other stuff in here...
> select * from #Temp
> drop table #Temp
> GO
>|||Yes, see David's reply. Just make sure the columns you leave out are either
NULLable or NOT NULL with a DEFAULT.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:%23YgU27aUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I tried that...but when I run "insert into #Temp exec GLGroup @.GrpID" it
>bombs since the number of columns I'm getting from GLGroup doesn't match
>the number of columns I'm inserting to...
> I thought I was being clever by creating a table that matched the
> structure of the SP and then altering it. ;-)
> Is there a way to insert into my table where the number of columns don't
> match?
>
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:u0B6E4aUFHA.3620@.TK2MSFTNGP09.phx.gbl...
>|||What is the error you get? You can alter a temporary table by adding a
column or constraint in a stored procedure, and I don't see anything wrong
with your code so far.
Jacco Schalkwijk
SQL Server MVP
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Hi. I have a stored procedure where I'm creating a Temporary table,
> inserting data into it from another stored procedure, than altering it by
> adding another column, then doing more stuff within the temp table.
> My problem is after my Alter statment, I need to use a GO statement to the
> table will be altered...but then the stored procedure thinks it's done
> and doesn't run anything else.
> Either: How do I insert the new column in after inserting into the table?
> OR create the table with the extra column and tell the SP to run...and
> not bomb when it thinks it's missing a column?
> Thanks.
> -Rob T.
> PS...it work fine just in the Query Designer, but the GO is killing me
> when I'm created the SP...
> Here's a snippit of the SP:
> CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
> -- Create a #Temp table --
> create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
> int, Level1 int, ParentSeq int)
> insert into #Temp exec GLGroup @.GrpID
> ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
> GO
> --Figure out all the values --
> ... a bunch of other stuff in here...
> select * from #Temp
> drop table #Temp
> GO
>|||> What is the error you get? You can alter a temporary table by adding a
> column or constraint in a stored procedure,
But you can't reference it directly, as the parser doesn't read ahead.
CREATE PROCEDURE dbo.foo
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #foo (id INT)
ALTER TABLE #foo ADD bar VARCHAR(32)
-- this works fine
SELECT * FROM #foo
-- this fails:
SELECT id, bar FROM #foo
-- so does this:
UPDATE #foo SET bar = 5
DROP TABLE #foo
END
GO
EXEC dbo.foo
GO
DROP PROCEDURE dbo.foo
GO
Server: Msg 207, Level 16, State 3, Procedure foo, Line 14
Invalid column name 'bar'.
Server: Msg 207, Level 16, State 1, Procedure foo, Line 17
Invalid column name 'bar'.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Defered resolution at its best...Though, this is an easy fix. ;-)
CREATE PROCEDURE dbo.foo
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #foo (id INT)
exec('ALTER TABLE #foo ADD bar VARCHAR(32)')
-- this works fine
SELECT * FROM #foo
-- this fails:
SELECT id, bar FROM #foo
-- so does this:
UPDATE #foo SET bar = 5
DROP TABLE #foo
END
GO
EXEC dbo.foo
GO
DROP PROCEDURE dbo.foo
GO
-oj
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:O2D2gCbUFHA.1432@.TK2MSFTNGP09.phx.gbl...
> But you can't reference it directly, as the parser doesn't read ahead.
> CREATE PROCEDURE dbo.foo
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #foo (id INT)
> ALTER TABLE #foo ADD bar VARCHAR(32)
> -- this works fine
> SELECT * FROM #foo
> -- this fails:
> SELECT id, bar FROM #foo
> -- so does this:
> UPDATE #foo SET bar = 5
> DROP TABLE #foo
> END
> GO
> EXEC dbo.foo
> GO
> DROP PROCEDURE dbo.foo
> GO
>
> Server: Msg 207, Level 16, State 3, Procedure foo, Line 14
> Invalid column name 'bar'.
> Server: Msg 207, Level 16, State 1, Procedure foo, Line 17
> Invalid column name 'bar'.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>