Stored Procedures - 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

Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-02 : 10:46:39

Pieter writes "I am almost finished with my Comprehensive Programming Diploma and I have started with SQL server 2000 a week a go, and I am struggling with stored procedures. How to let a user add records to a table and change records. If it is possible I would like an example of such a Procedure. I don't have an example of this in my text book(SQL Server 2000 Programming).
Thank You
Have a great day
Pieter van der Linde"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 10:54:29

Refer Books On Line (SQL Server help file) for basic systaxes
Refer this also

http://webdevelopment.developersnetwork.com/Articles.asp?Article=223

Madhivanan

Failing to plan is Planning to fail

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-02 : 11:14:10

Let's say that you have something like this:

CREATE TABLE [dbo].[xtblCities] (
[intCityID] [int] IDENTITY (1, 1) NOT NULL ,
[txtCityCode] [varchar] (50) NULL ,
[txtCityName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

You might have stored procedures to insert and update that look like this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryCitiesIns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qryCitiesIns]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryCitiesUpd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qryCitiesUpd]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/**********************************************************************************************
Query Name : qryCitiesIns
Description : Insert script for xtblCities
***********************************************************************************************/

CREATE PROCEDURE qryCitiesIns
@intUserId INT = -1,
@txtCityCode varchar(50) = NULL,
@txtCityName varchar(50)

AS

INSERT INTO xtblCities
VALUES (
@txtCityCode,
@txtCityName
)
RETURN SCOPE_IDENTITY()

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[qryCitiesIns] TO [Whatever_DataBase_Users_You_Have]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/**********************************************************************************************
Query Name : qryCitiesUpd
Description : Update script for xtblCities
***********************************************************************************************/

CREATE PROCEDURE qryCitiesUpd
@intUserId INT = -1,
@intCityID int,
@txtCityCode varchar(50) = NULL,
@txtCityName varchar(50)

AS

UPDATE xtblCities SET
txtCityCode = @txtCityCode,
txtCityName = @txtCityName

WHERE
intCityID = @intCityID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[qryCitiesUpd] TO [Whatever_DataBase_Users_You_Have]
GO

So, your stored procs will accept a list of parameters. With your insert proc, you just do an "INSERT INTO" into your table with the list of parameters, minus the column intCityID. The reason we don't insert into intCityID is because in my system that column is a primary key and will dump a value into it automatically, I don't have to handle it.

The update script simply says I am updating this column with this value: ColumnName = @Value.

At the end of both scripts, you will probably set execute permissions and give them to which ever users will need to execute the stored procs.

There is a lot more to this, but hopefully this give you a starting point.

Aj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources