Where to Store or Save SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where to Store or Save SP

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-26 : 23:02:45

Guys,

Im new to SP. I want to create SP and where i should store or save my screated stored procedure? it can be stored to my c drive? your inputs guys.

Thanks.

JOV

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-26 : 23:43:23

The script to compile your stored procedure can be saved anywhere you want on the file system. However, to be able to execute your stored procedure you need to compile it. Compiling it is to run your "create proc" script in a query window. Once you have done that you don't need your script - you will be able to execute it by running this in a query windoe: EXEC mySP. Of course you should save your "create proc" script so you have a backup and can re-compile it whenever and where ever you need.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 02:46:27

I see many (most?) people just execute the Sproc on the server and then when they want to edit it doing RightClick and Edit ...

Persoanlly I think this is a terrible way of working. With two or three developers the changes will faull over each other. SOoner or later you'll have two people make different changed to an Sproc and one person's work will be lost.

There have been bugs (like having a nested comment before the CREATE statement, that have meant that SQL can no longer script-out the Sproc. SO then you can't get to it at all.

But more important to me is:

a) having all my source code in a revision control system (we use CVS and Tortoise)
b) having all my source code on disk enables me to structure release packs, and service patches, more easily than if it is just in the DB. e.g. "Make a package of all changes since 01-Jan"

We also use WITH ENCRYPTION on all our public / user facing servers to make life more difficult for a hacker (and to prevent a well meaning junior DBA for "just fixing" something)

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-09-27 : 04:26:21

You can store them locally as .sql file. further they can be encrypted and i want to add more that you can use TFS to for revision control system and maintain history of version. Do let me know if you want more detail.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-27 : 05:21:51

Thank you guys for the reply..

So. if i will compile the said script into SP do i need to specify the folder.
btw, what is the extension of SP after it was compiled.

anyway, i have my old folder were all my script was stored. can i used this one to save my SP?
it was located in my drive D:\MySQLFile

Thank you.

JOV

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-27 : 05:49:26

Guys is this the procedure on how to create SP?

1. Database name : JOVMaster
2. Table : PHTable
3. Under the programmamability, Stored Procedures, then right click then choose New Stored Procedure?

Or is there any other way to create SP?

BR.

JOV

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 06:00:05

"if i will compile the said script into SP do i need to specify the folder"

No, it doesn't work quite like that.

Your script will be:

CREATE PROCEDURE MyProcedureName ...

and you just execute / run that script. That will create the stored procedure in the database.

You can do this in a New Query window in SSMS (or you can probably use Right Click and Create New Procedure to use the GUI tools)

I recommend you then save the script as MyProcedure.SQL in a suitable folder on your disk / network (and include that in a revision control system if you have one), but either way you will be able to Right Click the procedure in the GUI and use MODIFY (but I still think you should then save it to disk, as well)

You can then use

EXECUTE MyProcedureName ...

whenever you want to use it (or you can do that from your application etc.)

There is no "compile" process as there would be with a language such as C/C++ where you star with some .C and .H, or .CPP and .HPP, files that are compiled to .OBJ files and then linked to .EXE and all that rigmarole!

Just execute the CREATE statement on the database and then EXECUTE the procedure once it has been created

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-27 : 23:58:01

Thank you Kristen.

Just done my first Stored Procedure.
The result is like this.

1. Under DBMaster I just created SP using right click at programmability->Stored Procedures->New Store Procedure
and display a default SP scripts which i modified for my requirements.
2. Save the SP at my folder in c:/MySQLBackup as backup and at the same time it has created a file
in stored Procedure, the file name is something like this dbo.sp_sample. so i have a copy from my
c drive then in server side.
3. to run SP I jst right click the dbo.sp_sample then click execute stored procedure
then there is a new window display it indicates the parameters from the scripts. the the value column
i place the date pertaining to my date parameters then i click ok.
4. when i click "OK" automatic it will display the result of script from my SP?
how to i view or see the result of my query in stored procedure if correct?

i try also this scripts to run my sp
a. EXEC sp_sample
2. EXEC sp_sample '2011-01-01 00:00:00','2011-01-05 23:59:59'

5. I got an error:

Msg 201, Level 16, State 4, Procedure sp_sample, Line 0
Procedure or function 'sp_sample' expects parameter '@StartDate', which was not supplied.
(1 row(s) affected)

CREATE PROCEDURE sp_sample
-- Add the parameters for the stored procedure here
@StartDate Datetime,
@EndDate Datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Select
esn,
model,
supplier,
verified_failure
received_date
from DBMaster.dbo.USOpenPO
where received_date between (@Startdate) and (@EndDate)
END
EXEC sp_sample
GO

Regards

JOV

Kristen
Test

22859 Posts

Posted - 2011-09-28 : 03:24:36

"i try also this scripts to run my sp
...
2. EXEC sp_sample '2011-01-01 00:00:00','2011-01-05 23:59:59'"

This should work, but it would be better to say:


EXEC sp_sample @StartDate='2011-01-01 00:00:00', @EndDate='2011-01-05 23:59:59'

just in case you add parameters in the future

You need to remove the EXEC sp_sample from the end of your code:


where received_date between (@Startdate) and (@EndDate)
END
EXEC sp_sample <--- REMOVE THIS!
GO

You should not start your stored procedure names with "sp_" because that is used by Microsoft for system stored procedures.

Most people use "tomorrow" as the End Date and then use


where     received_date >= @Startdate
      and received_date < @EndDate -- e.g. '2011-01-06'

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-30 : 03:05:00

Thank you very much Kristen.
very helpful information.

anyway, when ever i use this date script
the @enddate transaction or records is missing especially the 2011-9-30 records. that's why im using where received_date between('2011-09-01') and ('2011-09-30') all records is captured.

Is this useful with datetime format?
where received_date >= @Startdate --2011-09-01
and received_date < @EndDate --2011-09-30

BR.

JOV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources