Modify all store proc in DB in one shot

Is it possible that i can use a store proc to modify all the rest of my store procedures that i have in my DB ??

I have so many created allready and it will be to long to go throught each one of them to modify my text inside.

what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!

any advise or example..

thanx.My strong suggestion is to script all of the stored procedures (including their permissions) using SQL Enterprise Mangler, and save two copies of the script. Edit one of the scripts to suit your fancy, then play it into your database. Test, test, test, then test some more.

When you discover what you mangled, play in the pristine script and start over!

-PatP|||Are the procedures stored in files or do they solely exist in sqlserver? If the latter, you have perhaps a challenge. I'm not sure if this will work: select the inside from syscomments (column text), then change what you want. You could commit the change to the syscomments table but you'll have to reconfigure sql server to allow this and I am not sure if that's a good idea. Besides, you'll have to think about having to let sql server know the procedure changed so it'll recompile (in order to make sure it'll use the latest version). Instead you could try to have the contents of the procedure executed using sp_executesql, don't forget the drop and create commands though.
I would not recommend either one since you'll have some issues to consider prior actually doing the changes.|||btw: Pat's suggestion is way preferred over mine|||Hey look at that...a cross post :D

SQLTeam (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35267)

Check out the link in the link...let me know what you think...

Missing SP Parameter?

I've got a completely goofy problem on one development machine running Vista, SQL 2005 sp2, and Orcas Beta 2. I'm calling a stored proc from within my C# and my ExecuteDataReader() command fails saying I'm missing a parameter (which I'm not).

The c# looks like this:

if (SqlContext.IsAvailable)

{

using (System.Data.SqlClient.SqlConnection cnx =

new System.Data.SqlClient.SqlConnection("context connection=true"))

{

cnx.Open();

using (SqlCommand xCmd = new SqlCommand("dbo.TestMe", cnx))

{

xCmd.CommandType = CommandType.StoredProcedure;

xCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("myParam", SqlDbType.Int));

xCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("myStr",

SqlDbType.VarChar, 150));

xCmd.Parameters["myParam"].Value = 123;

xCmd.Parameters["myStr"].Value = "Hello";

xCmd.ExecuteNonQuery();

}

}

}

My Stored Proc look like this:

CREATE PROCEDURE [dbo].[TestMe]

@.myParam as int,

@.myStr as nvarchar(150)

AS

BEGIN

SELECT 1 as myCol

END

The result is a an exception that looks like this:

A .NET Framework error occurred during execution of user-defined routine or aggregate "StoredProcedure1":

System.Data.SqlClient.SqlException: Procedure or function 'TestMe' expects parameter '@.myParam', which was not supplied.

If I run the SP from the Sql Workbench, it looks fine. Any ideas? I just repaved SQL and Orcas on this machine to see if I can fix the problem -- no luck.

The problem is with how you have created the parameters in your C# code. When you are using the Sql provider and you are clling parameterized queries or stored proc with queries, the parameters are sent in by name. Your T-SQL code has two parameters: @.myParam and @.myStr, in your T-SQL code you create a param called myParam and myStr - you need the @. as well in the parameter name.

Niels
|||

Well that certainly fixed the problem. Thanks!

But has something changed in the SqlClient plumbing? I went back to look at our test machine, which is a Windows Server 2003 R2 / SQL 2005 SP2 (64-bit) system. The C# code works without the "@." in the param names as it does on other similarly configured systems. I even took the little routine from my code sample and deployed it -- sans "@." -- on our test machines and it works. Hmm.

Again thanks for the reply.

-Erik

Missing results from stored proc

ok...I must be blind.
I have this procedure (which I didn't write):
CREATE PROCEDURE dbo.spWebqryAccountList41257
(@.StartDate nvarchar(15)
,@.EndDate nvarchar(15)
,@.ClientID int
,@.ArpCode nvarchar(15)
,@.brchid nvarchar(5)
,@.username nvarchar(10)
)
AS
SELECT *
FROM
dbo.vwWebClaimDetailNext4
WHERE
([Date Handled] between @.StartDate AND @.EndDate)
AND ([Client ID] = @.ClientID)
and ARPCODE in (@.ArpCode)
and BranchID = @.brchid
and username = @.username
Note that all it is doing is selecting from a view and narrowing the results
I have two executions:
EXEC spWebqryAccountList41257
@.StartDate = '2/1/2006',
@.EndDate = '3/3/2006',
@.ClientID = 2287,
@.ARPCode = 'A',
@.BrchID = 'A#79',
@.Username = 'ME2287'
Result = 4 records
EXEC spWebqryAccountList41257
@.StartDate = '2/1/2006',
@.EndDate = '3/3/2006',
@.ClientID = 2287,
@.ARPCode = 'A',
@.BrchID = 'Mil#104',
@.Username = 'ME2287'
Result = 0 records
The only difference between the two is the @.BrchID parameter.
The underlying view run with the same parameters as the second execution in
the WHERE clause returns records (7, to be exact) for the second set, but
the stored proc does not.
If I do this:
SELECT *
FROM
dbo.vwWebClaimDetailNext4
WHERE
([Date Handled] between '2/1/2006' and '3/3/2006')
AND ([Client ID] =2287)
and ARPCODE in ('A')
and BranchID = 'Mil#104'
and username = 'ME2287'
I get the right results...
I've been looking at the # sign as the culprit, but both branches have one
(as do many others).
Help?
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htmNevermind...the @.brchid parameter isn't big enough to hold what is being
passed...
Thanks for looking :-)
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Kevin3NF" <Kevin@.DontNeedNoSpam3NF-inc.com> wrote in message
news:ORLUL$ZQGHA.3896@.TK2MSFTNGP15.phx.gbl...
> ok...I must be blind.
> I have this procedure (which I didn't write):
> CREATE PROCEDURE dbo.spWebqryAccountList41257
> (@.StartDate nvarchar(15)
> ,@.EndDate nvarchar(15)
> ,@.ClientID int
> ,@.ArpCode nvarchar(15)
> ,@.brchid nvarchar(5)
> ,@.username nvarchar(10)
> )
> AS
> SELECT *
> FROM
> dbo.vwWebClaimDetailNext4
> WHERE
> ([Date Handled] between @.StartDate AND @.EndDate)
> AND ([Client ID] = @.ClientID)
> and ARPCODE in (@.ArpCode)
> and BranchID = @.brchid
> and username = @.username
>
> Note that all it is doing is selecting from a view and narrowing the
> results
> I have two executions:
> EXEC spWebqryAccountList41257
> @.StartDate = '2/1/2006',
> @.EndDate = '3/3/2006',
> @.ClientID = 2287,
> @.ARPCode = 'A',
> @.BrchID = 'A#79',
> @.Username = 'ME2287'
> Result = 4 records
> EXEC spWebqryAccountList41257
> @.StartDate = '2/1/2006',
> @.EndDate = '3/3/2006',
> @.ClientID = 2287,
> @.ARPCode = 'A',
> @.BrchID = 'Mil#104',
> @.Username = 'ME2287'
> Result = 0 records
> The only difference between the two is the @.BrchID parameter.
> The underlying view run with the same parameters as the second execution
> in the WHERE clause returns records (7, to be exact) for the second set,
> but the stored proc does not.
> If I do this:
> SELECT *
> FROM
> dbo.vwWebClaimDetailNext4
> WHERE
> ([Date Handled] between '2/1/2006' and '3/3/2006')
> AND ([Client ID] =2287)
> and ARPCODE in ('A')
> and BranchID = 'Mil#104'
> and username = 'ME2287'
> I get the right results...
> I've been looking at the # sign as the culprit, but both branches have one
> (as do many others).
> Help?
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
>
>