how do i write an "Update" stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how do i write an "Update" stored procedure?

Author

Topic

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-11 : 10:15:45

Hi all, how are you?

I need some help with how to write a Stored procedure that updates a tbl within a database? I am new so please forgive the poor effort.

Here are my stored procedure and table. Please shout if you need to know anything else

TblStores2Zones
column names (stores2zonesID - fkstoreID - fk_zoneID - Active)
1 1 1 True
2 1 2 True
3 1 3 True
4 1 4 True
5 1 5 True

Store procedure
ALTER PROCEDURE [dbo].[spUpdateZones]
@iStoreID DECIMAL
@ifk_storeID INT
@ifk_zoneID INT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT fk_storeID FROM tblstores2zones WHERE fk_storeID = @ifk_storeID )
BEGIN
UPDATE
tblStores2Zones
SET
fk_storeID = @ifk_storeID
fk_zoneID = @ifk_zoneID
WHERE
storeID = @iStoreID
END

END

keep getting errors with this effort

help is most welcome all

Regard
MG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-11 : 10:19:11

May be this?

CREATE PROCEDURE [dbo].[spUpdateZones] (
@iStoreID DECIMAL
@ifk_storeID INT
@ifk_zoneID INT
)
AS 
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT fk_storeID FROM tblstores2zones WHERE fk_storeID = @ifk_storeID )
BEGIN
UPDATE 
tblStores2Zones
SET
fk_storeID = @ifk_storeID,
fk_zoneID = @ifk_zoneID
WHERE
storeID = @iStoreID 
END 

END

raky
Aged Yak Warrior

767 Posts

Posted - 2009-08-11 : 10:19:39

try this



ALTER PROCEDURE [dbo].[spUpdateZones]
(
@iStoreID DECIMAL,
@ifk_storeID INT,
@ifk_zoneID INT
)
AS
SET NOCOUNT ON
BEGIN
	IF EXISTS(SELECT fk_storeID FROM tblstores2zones WHERE fk_storeID = @ifk_storeID )
	BEGIN
		UPDATE	tblStores2Zones
		SET	fk_storeID = @ifk_storeID,
			fk_zoneID = @ifk_zoneID
		WHERE storeID = @iStoreID
	END

END
SET NOCOUNT OFF

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-11 : 11:39:16

Hi all, thanks for the replies tried the second and seemed to work.

Regards
MG

raky
Aged Yak Warrior

767 Posts

Posted - 2009-08-12 : 01:07:02

Welcome....

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-12 : 04:23:36

Quick question i didnt think you had to put parameters within brackets?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources