How do i create a stored procedure in SQL Server05

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

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How do i create a stored procedure in SQL Server05

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 10:53:02

Yes it looks like a stupid question
but when i right click stored procedures
and click new stored procedure, it gives me a
QRY analyzer style window and all i can do is save
the qry as a regular .qry file ?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-08 : 11:08:18

http://msdn2.microsoft.com/en-us/library/ms187926.aspx

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-02-19 : 11:14:57

why won't this work
[CODE]
--
-- 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,,Daniel Mac Alister>
-- Create date: <2008-02-19,,>
-- Description: <Test SP, never will be used .. delete if you want.,,>
-- =============================================
CREATE PROCEDURE <TEST_St_Pr, sysname, TEST_St_Pr>
-- Add the parameters for the stored procedure here
<@Startdate, sysname, @p1> <Datatype_For_Param1, , datetime> = <Default_Value_For_Param1, , 0>,
<@Enddate, sysname, @p2> <Datatype_For_Param2, , datetime> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select * from interaction
where status in (9)
and start_moment between @Startdate and @Enddate
order by start_moment desc
SELECT <@Startdate, sysname, @p1>, <@Enddate, sysname, @p2>
END
GO
[/CODE]
I am getting this error
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '<'.
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@Startdate".

which is rubbish as it is declared ?
why did Microsoft do this ?
Its so easy in 2000 , why would they f*ck everything up by doing this ?

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-19 : 11:53:26

Try:


CREATE PROCEDURE TEST_St_Pr
(
	-- 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;

    select * from interaction 
	where status in (9)
	and start_moment between @Startdate and @Enddate 
	order by start_moment desc
END

talleyrand
Starting Member

35 Posts

Posted - 2008-02-19 : 11:56:25

Yeah, screw them for changing how we create stored procedures in 2005.

Wait a minute, it's the same in 2000 as it is in 2005.

When you had Management Studio create a new stored procedure, did you happen to read the comments or did you cobble your pieces onto it?

-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.

I had never tried the feature out before but it worked just fine for filling in the template. On the other hand, if you have experience with stored procs in 2000, skip the Management Studio help and do what you know.

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-02-20 : 03:53:18

Same as in 2000 ?

how ? when i was in 2000 i could just click new SP and paste my code right there and save it, I can't
do that in 2005, i have to execute a QRY to have it created ..

anyway thanks for above .. i will save this and use as my template...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources