Hi all, One interesting question

[buh]

Hi,

We are having lot of sql server stored procedures
to be installed at client sites. Each time we will open each procedure and execute it thru query analyser.I consumes lot of implementation time.

Now, we don't want to do this. Instead, we want to have one stored procedure or sql statement thru which i should be able to execute all the procedures at one shot.

It is very urgent.

Eary reply solicited

Thanks & Regards,
Buhari

 

[LFCfan]

If i've understood you correctly, you could do the following...

for example, if you have 3 stored procs that need to be executed:

CREATE PROCEDURE proc1 @p1a INT, @p1b CHAR(3) AS <....>
and
CREATE PROCEDURE proc2 @p2a INT, @p2b CHAR(3), @p2c DATETIME AS <....>
and
CREATE PROCEDURE proc3 @p3a TINYINT, @p3b VARCHAR(3) AS <....>

then you could have a calling stored proc which contains all the parameters of all three procedures:

CREATE PROCEDURE calling_proc
@p1a INT, @p1b CHAR(3), -- from proc1
@p2a INT, @p2b CHAR(3), @p2c DATETIME, -- from proc2
@p3a TINYINT, @p3b VARCHAR(3) -- from proc3

AS
EXEC proc1 @p1a, @p1b
EXEC proc2 @p2a, @p2b, @p2c
EXEC proc3 @p3a, @p3b
GO


Hope this helps
LFCfan

 

[SQLSister]

Why don't you click on a stored procedure in Enterprise Manager. Right click and Select All Tasks and then click Script SQL. THe choos the button that says Show All and then clcick just the stored procedures option and it will script all the stored procedures for you. You can then open this script and delete the ones you don't want. Then run this script on your client machines.

Alternatively there is a neat product which isn't even expensive called SQL Compare (

http://www.red-gate.com/)

which will compare two databases and create the script t move the items you select to the other database. Set up a test database that is in the state the version onthe client machines is in. Run SQL Compare for stored procedures only. Check off the stored procedures you want and bingo. You can even do the compare just on the specific stored procedures, but I recommend the other so you can see if you missed any that need to be moved. YOu can also see the differneces if a differnt version exists on both machines. It will create a script. Easy peasy. I love this product you will get a return on investment the first time you use it in hours saved. Anyone moving things between development and production machines needs this product. Boy, I wish they gave me a commission for the number of people I've sent their way.

 

[buh]

I am thankful to both of u.

I will try both the way.

Regards,
Buh