Creating a SPROC - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a SPROC

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-09-13 : 16:56:42

It has been a while since I created a Stored Procedure, is this the correct way. After selecting Create New Stored Procedure I proceded to do this:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[insertIntoQuin]
--	Add the parameters for the stored procedure here
--	Replace the variable type in this section with the type
--	they actually are, I did not know the type so I made them all the same	
	@Type1	varchar(50), 
	@DateRaised	varchar(50), 
	@Status1 	varchar(50), 
	@Company	varchar(50), 
	@Address1	 varchar(50), 
	@Postcode	varchar(50), 
	@Support	varchar(50), 
	@TasksCompleted	varchar(50), 
	@HoursAllocated	varchar(50),
	@HoursUsed	varchar(50), 
	@OverLim	varchar(50), 
	@HourlyRate	varchar(50), 
	@ExtendedCost	varchar(50), 
	@MonthlySupport	varchar(50), 
	@Vat	varchar(50), 
	@Total	varchar(50), 
	@qutask	varchar(50),
	@qucost	varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;
    -- Insert statements for procedure here
	-- Type, Status and Address are reserved words rename them
INSERT INTO quin
	(Type1, 
	DateRaised, 
	Status1, 
	Company, 
	Address1, 
	Postcode, 
	Support, 
	TasksCompleted, 
	HoursAllocated,
	HoursUsed, 
	OverLim, 
	HourlyRate, 
	ExtendedCost, 
	MonthlySupport, 
	Vat, 
	Total, 
	qutask,
	qucost)
Values
	(@Type1, 
	@DateRaised, 
	@Status1, 
	@Company, 
	@Address1, 
	@PostCode, 
	@Support, 
	@TasksCompleted, 
	@HoursAllocated,
	@HoursUsed, 
	@OverLim, 
	@HourlyRate, 
	@ExtendedCost, 
	@MonthlySupport, 
	@Vat, 
	@Total, 
	@qutask,
	@qucost)
END
GO

And ended up with this as a SPROC:


USE [TestDataBase]-- name of database goes here
GO
/****** Object:  StoredProcedure [dbo].[insertIntoQuin]    Script Date: 09/13/2009 16:29:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[insertIntoQuin]
	-- Add the parameters for the stored procedure here
	@Type1		varchar(50), 
	@DateRaised	varchar(50), 
	@Status1 	varchar(50), 
	@Company	varchar(50), 
	@Address1	 varchar(50), 
	@Postcode	varchar(50), 
	@Support	varchar(50), 
	@TasksCompleted	varchar(50), 
	@HoursAllocated	varchar(50),
	@HoursUsed	varchar(50), 
	@OverLim	varchar(50), 
	@HourlyRate	varchar(50), 
	@ExtendedCost	varchar(50), 
	@MonthlySupport	varchar(50), 
	@Vat	varchar(50), 
	@Total	varchar(50), 
	@qutask	varchar(50),
	@qucost	varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;
    -- Insert statements for procedure here
	-- Type, Status and Address are reserved words rename them
INSERT INTO quin
	(Type1, 
	DateRaised, 
	Status1, 
	Company, 
	Address1, 
	Postcode, 
	Support, 
	TasksCompleted, 
	HoursAllocated,
	HoursUsed, 
	OverLim, 
	HourlyRate, 
	ExtendedCost, 
	MonthlySupport, 
	Vat, 
	Total, 
	qutask,
	qucost)
Values
	(@Type1, 
	@DateRaised, 
	@Status1, 
	@Company, 
	@Address1, 
	@PostCode, 
	@Support, 
	@TasksCompleted, 
	@HoursAllocated,
	@HoursUsed, 
	@OverLim, 
	@HourlyRate, 
	@ExtendedCost, 
	@MonthlySupport, 
	@Vat, 
	@Total, 
	@qutask,
	@qucost)
END

I am just refreshing some rusty skills so the varaibles are all the same, that is not important. Shouldn't I be able to execute the SPORC by right clicking on it in the StoredProcedures folder or did I miss something?

Thanks for the help
CoachBarker

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-13 : 23:33:27

Use "EXEC insertIntoQuin ..." to execute it. You'll need to pass in the parameter values. Here's an example:

EXEC someSproc @var1 = 1, @var2 = 'Tara', ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-09-14 : 07:09:18

Would I use the EXEC right in the SPROC I am writing? When I was writing SPROCS last year we never wrote a separate EXEC statement, we just ran the SPROC from a C# application.
Example:


SqlConn.Open() 'obviously is the database connection
Dim mySqlCommand As SqlCommand
mySqlCommand.Parameters.Clear()
mySqlCommand = New SqlCommand("insertIntoQuin", SqlConn)
mySqlCommand.CommandType = CommandType.StoredProcedure

Thanks for the help
CoachBarker

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-14 : 07:46:37

You can call it from C# or any other language with the correct drivers, but to run it straight from SQL, run it in a new query window within Management Studio with ther format of the example Tara gave you.

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-09-14 : 08:25:29

I read what Tara wrote, my question is can I combine the EXEC into the SPROC so I am calling the SPROC itself and not a separate EXEC query. ANd if so using my original Create Procedure how would I write it?

Thanks for the help
CoachBarker

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-14 : 10:29:02

No, because creating the stored procedure just adds that object to the database. You only need to do this once. After you have created the stored procedure, you can run it as many times as you like with a seperate exec statement.

If you try to add it into the stored proc creation script, you will get a parsing error.

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-09-15 : 16:28:50

OK I got it, I created a small GUI to run the SPROC from and it works. Thanks for the advice.

Thanks for the help
CoachBarker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources