stored proc to create a table
-
Edit: I put this in a more appropriate forum under 2005 general questions.
I am hardly a newbie but this seems like a noob question. I suspect I will feel pretty humbled by the answer and my lack of knowledge.
Using 2005. Trying to create a stored proc that will create a table. Actually I already have the script and the table already exists but we just want that script with the other stored procs. (A previous developer left. As a matter of cleanup, we want to get all his scripts that may be used as stored procs into stored procs.)
So I create the stored proc. I don't really want to run it but I am not familiar with another way of getting the thing saved. So I blow away the table and execute the stored proc. The table is build fine. But when I modify the stored proc I notice that all the code that built the table is gone (i.e. the CREATE TABLE statement). Please tell me what is going on here. What am I doing wrong?
TIA.
-
Post the entire proc and the steps you are going through to alter it.
And then again, I might be wrong ...
David Webb -
(I have taken various steps to try this but here is one.)
1) Delete the table.
2) Created and execute the proc. (I removed a bunch of fields in the CREATE TABLE statement below).
CREATE PROCEDURE usp_f_job_create_table
AS
/****** Object: Table [dbo].[f_job] Script Date: 12/22/2006 13:39:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[f_job](
[job_id] [bigint] NOT NULL,
[mas_job_no] [int] NULL,
[company_id] [bigint] NULL,
[branch_id] [bigint] NULL,
[site_id] [bigint] NULL)
GO
SET ANSI_PADDING OFF
GO
3) The table is built fine.
4) But when I right click on the stored proc and select Modify all I get is this -- with no CREATE TABLE statement...
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
ALTER
PROCEDURE [dbo].[usp_f_job_create_table]
AS
/****** Object: Table [dbo].[f_job] Script Date: 12/22/2006 13:39:10 ******/
SET
ANSI_NULLS ON
-
Your problem is the GO statements after the CREATE PROCEDURE and before the CREATE TABLE, try this:
CREATE PROCEDURE usp_f_job_create_table
AS
CREATE TABLE [dbo].[f_job](
[job_id] [bigint] NOT NULL,
[mas_job_no] [int] NULL,
[company_id] [bigint] NULL,
[branch_id] [bigint] NULL,
[site_id] [bigint] NULL)
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply