>>>
>>>You're confusing Creating sproc and Executing sproc.
>>
>>Why are you surprised? <g>
>
>In order to create/update sproc in DB you do
>
>CREATE PROCEDURE myproc
>...
>
>ALTER PROCEDURE myproc
>...
>After that you can excute sproc
>myproc ...
>
>EXEC myproc ...
>
The EXEC[UTE] is required if there're multiple statement ij a batch. I always use it for concistency.
Adding to Sergey's excellent advice, it is probably better to drop and recreate the stored procedure in the script each time. This allows you to work off of a single script file whether the object is already there or not and it will force a recompile the next time that the procedure is executed (sometimes SQL Server will keep the old execution plan if it thinks that the text of the stored procedure hasn't changed significantly enough to generate a new execution plan).
E.g.:
USE [MyDatabase]
GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE [name] = 'MyProc' AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
DROP PROCEDURE dbo.[MyProc]
CREATE PROCEDURE dbo.[MyProc] (
@p1 int
,@p2 int
) as
...