stored proc to create a table
-
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 SQL Server rather than on our file server.)
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 built 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.
-
I'm a bit confused. There are three things at work here. First off, creating a procedure
CREATE PROCEDURE x
AS
--followed by creating a table within the proc
--drop it first
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type = N'U'))
DROP TABLE [dbo].[AuditLog]
-- make the table
CREATE TABLE [dbo].[mytable] (
[ColumnName] varchar(50) NOT NULL
)
GO
Running that script creates the procedure and "stores" it in the database. Running the proc doesn't change where the proc is located:
EXEC x
It merely runs the proc and creates the table. Anything else you're seeing is something else you've done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I wonder if when you compiled your stored proc, you had a GO statement above the CREATE TABLE statement. If that was so, only the code above GO would be compiled into the proc. This is because GO is a batch separator and it cannot exist inside a proc.
You might also try simply putting commented code inside a proc. The only caveat is you have to handle the cases when your code contains quote marks.
- Paul
http://paulpaivasql.blogspot.com/
-
"I wonder if when you compiled your stored proc, you had a GO statement above the CREATE TABLE statement. If that was so, only the code above GO would be compiled into the proc. This is because GO is a batch separator and it cannot exist inside a proc."
Yep. Sad, isn't it? Thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply