about copy a new stored procedure to all databases

Dear all
i have been updating a new stored procedure to all database if it has
that procedure.
i have write something like to test with one database only but it
still fail.
it said i havent declare CID salary.
i cant figure out why and how. hope anyone have experience can help me
out.
Maybe can i update a procedure for a database and then copy them all
to other database.
thanks i really hope someone can help out for this
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
ALTER PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary[float]output)
AS
IF @.CID = 1234
Begin
xxxxxxxxxxxxxxxxxxxxxxx
/* it is the long statement*/
xxxxxxxxxxxxxxxxxxxxxxx
END
END
CREATE/ALTER PROCEDURE must be the only statement in the batch so
CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
followed by unconditional CREATE:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
BEGIN
DROP PROCEDURE [dbo].[cal_sal]
END
GO
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
BEGIN
/* it is the long statement*/
END
GO
GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1185968371.785331.165490@.x40g2000prg.googlegr oups.com...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
|||Thanks Dan
you have really help me out
but while i would like to execute the statement, it said Create/Alter
procedure must be the first statement
why was this happen?
so my testing statement is like below:
use test
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END
and finally i would like to use, is this possible ?
exec sp_MSforeachdb
'use [?]
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'
On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> CREATE/ALTER PROCEDURE must be the only statement in the batch so
> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> followed by unconditional CREATE:
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> BEGIN
> DROP PROCEDURE [dbo].[cal_sal]
> END
> GO
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> BEGIN
> /* it is the long statement*/
> END
> GO
> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <KelvinWon...@.gmail.com> wrote in message
> news:1185968371.785331.165490@.x40g2000prg.googlegr oups.com...
>
>
>
>
>
>
> - -
|||thanks so much
i also got that problem
On 8 1 , 10 47 , NEMA <realja...@.gmail.com> wrote:
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alterproceduremust be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
> - -
|||put a GO before Create can fix the problem
but it still cannot use sp_MSforeachdb as it said there are syntax
error on create and end
|||In article <1185968371.785331.165490@.x40g2000prg.googlegroups .com>,
KelvinWongYW@.gmail.com says...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary[float]output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
add it to model so included in all new databases created from then
forward
Graham (Pete) Berry
PeteBerry@.Caltech.edu
|||thanks Pete
but how about the exisiting databases?
On 8 2 , 1 57 , Pete Berry <PeteBe...@.Caltech.edu> wrote:
> In article <1185968371.785331.165...@.x40g2000prg.googlegroups .com>,
> KelvinWon...@.gmail.com says...
>
>
>
>
>
>
> add it to model so included in all new databases created from then
> forward
> --
> Graham (Pete) Berry
> PeteBe...@.Caltech.edu- -
> - -
|||> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
You need a GO batch separator before the CREATE. SQL Server tools will send
the preceding batch of statements whenever a GO is encountered.

> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
You'll need to wrap the batches individually with EXECUTE. Assuming you
want to skip system databases, try something like:
EXEC sp_MSForEachDB '
USE ?
IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
DROP PROC [dbo].[cal_sal]'')
EXEC(''CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'')
END
'
This technique is a bit of a kludge, though. IMHO, it's better to create a
script file and run for each database using OSQL or SQLCMD.
Hope this helps.
Dan Guzman
SQL Server MVP
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185979672.379344.178450@.d30g2000prg.googlegr oups.com...
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
>
>
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
|||Thanks
i am thinking of is there a way to loop through each database and then
Alter the store procedure by using OSQL or SQLCMD
however i cant find a command to loop and some database may not have
that stored procedure
On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
>
> You need a GO batch separator before the CREATE. SQL Server tools will send
> the preceding batch of statements whenever a GO is encountered.
>
>
> You'll need to wrap the batches individually with EXECUTE. Assuming you
> want to skip system databases, try something like:
> EXEC sp_MSForEachDB '
> USE ?
> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
> BEGIN
> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
> DROP PROC [dbo].[cal_sal]'')
> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'')
> END
> '
> This technique is a bit of a kludge, though. IMHO, it's better to create a
> script file and run for each database using OSQL or SQLCMD.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "NEMA" <realja...@.gmail.com> wrote in message
> news:1185979672.379344.178450@.d30g2000prg.googlegr oups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - -
|||Are you saying that you don't know which databases need the stored
procedure?
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1186030181.537666.23780@.x40g2000prg.googlegro ups.com...
> Thanks
> i am thinking of is there a way to loop through each database and then
> Alter the store procedure by using OSQL or SQLCMD
> however i cant find a command to loop and some database may not have
> that stored procedure
>
> On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>