Creating Stored Procedure in SQLServer2005

I have been using SQL Server 2000 and creating Stored Procedure. I need these Stored Procedure to create Reports in Crystal Reports.

In SQL Server 2000: it is very simple to create a stored procedure: Go to Enterprise Manager->Databases-> Choose a database-> stored procedure->New-> Simply Add to the following syntax:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS

GO

My problem is that it is More Complex to use Stored Procedure In SQL Server 2005:

the stored procedure is under Databases->choose a database->programmability->create new stored procedure: But how can I use it: I need your help

It is more complex than the Stored Procedure in SQL Server 2000

Thank you

Your help is highly appreciated

You can create the SP in Query Analyzer i.e. Sql Server Management Stodio.

In the query window select the database and execute the Sql code 'Create Procedure ...'

Senthil

|||

I think that this will be a matter of becoming more familiar with the interface. If you are well aquainted with creating stored procedures in SQL 2000 then you will adjust to the 2005 interface rather quickly.

Are you perhaps being confused by the template that you can get when you create a new procedure?

|||

Can you provide me with more details?

As how to reach the Stored Procedure and how to Execute it ?

Thank you

|||

Are you getting the point to where you see something like this:

Code Snippet

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@.Param1, sysname, @.p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
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 <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END
GO

|||

Thank you all,

I have found the Answer

I wasnt understanding the complex syntax

so i simply deleted it all and kept the Following

CREATE PROCEDURE [dbo].[NCompOpp] AS BEGIN

Select from opmgr where opmgr.rectype="OP"

END

my major problem is that there are slight difference between writing Stored Procedures in SQL Server 2000 and in SQL Server 2005

|||

there arent really, its all the same language. theres some functions etc that can be utilized in 2005 that arent there in 2000, but the basics are the same.

I would advice getting a basic SQL book or going on a course to get familiar with the language syntax and structure etc.

The code in the template is pretty simple as it gives you the option of creating parameters, setting ANSI_NULLS on or off etc.

most statements also have an IF Exists at the top so you can work on the sql without having to keep changing the CREATE and ALTER part of the sql.

|||

If you are not comfortable with the template that is provided when you choose to create a stored procedure, you can change it <s>.

From the View menu, choose Template Explorer.

In the Template Explorer, drill down to Stored Procedures...

>L<