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
ENDCREATE/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.googlegroups.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.googlegroups.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 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:
>
> > CREATE/ALTERPROCEDUREmust 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
> > DROPPROCEDURE[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 ONPROCEDURE[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.googlegroups.com...
> > > Dear all
> > > i have been updating a newstoredprocedureto all database if it has
> > > thatprocedure.
> > > 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 aprocedurefor a database and thencopythem 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- -
> > - -- -
> - -|||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...
>
> > Dear all
> > i have been updating a newstoredprocedureto all database if it has
> > thatprocedure.
> > 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 aprocedurefor a database and thencopythem 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
> 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.googlegroups.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:
>> 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.googlegroups.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
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:
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust 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
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[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(''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.googlegroups.com...
>
> > Thanks Dan
> > you have really help me out
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust 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:
> >> CREATE/ALTERPROCEDUREmust 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
> >> DROPPROCEDURE[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 ONPROCEDURE[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.googlegroups.com...
> >> > Dear all
> >> > i have been updating anewstoredprocedureto all database if it has
> >> > thatprocedure.
> >> > 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 aprocedurefor a database and thencopythem 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- -
> >> - -- -
> - -|||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.googlegroups.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:
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust 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
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[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(''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.googlegroups.com...
>>
>> > Thanks Dan
>> > you have really help me out
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust 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:
>> >> CREATE/ALTERPROCEDUREmust 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
>> >> DROPPROCEDURE[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 ONPROCEDURE[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.googlegroups.com...
>> >> > Dear all
>> >> > i have been updating anewstoredprocedureto all database if it has
>> >> > thatprocedure.
>> >> > 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 aprocedurefor a database and thencopythem 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- -
>> >> - -- -
>> - -
>