ISUD in one SP - 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 2008 Forums

Transact-SQL (2008)

ISUD in one SP

Author

Topic

ddtopgun
Starting Member

26 Posts

Posted - 2014-05-28 : 00:16:50

i want to Insert,Select,Update and Delete in one Stored Procedure(SP) passing by parameter but when i want to exec sp error : Procedure or function sp_savedepart has too many arguments specified.

this is code for call the sp :

exec sp_savedepart '','',@StatementType='Select'

and this is code for the sp :

ALTER PROCEDURE [dbo].[sp_savedepart]
@nmDepartemen varchar(30),
@Deskripsi varchar(30)

AS
DECLARE 
	@StatementType varchar(20)='',
	@idDepartemen int
BEGIN	
	IF @StatementType ='Insert' 
	BEGIN
		SET NOCOUNT ON;
		INSERT INTO [dbo].[Location](nmDepartemen,Deskripsi) VALUES (UPPER(@nmDepartemen),@Deskripsi)
		--SELECT SCOPE_IDENTITY()
		SET @idDepartemen=SCOPE_IDENTITY()
		--RETURN @idDepartemen
	END
	
	IF @StatementType = 'Select'
	BEGIN
		SELECT * FROM Location
	END
	
	IF @StatementType = 'Update'
	BEGIN
		UPDATE Location 
		SET nmDepartemen=@nmDepartemen, 
			Deskripsi=@Deskripsi
		WHERE idDepartemen=@idDepartemen
	END
	
	IF @StatementType = 'Delete'
	BEGIN
		DELETE FROM Location WHERE idDepartemen=@idDepartemen
	END
		
END

iddepartemen is int and identity

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-28 : 00:54:34

Change procedure to following and execute as illustrated


ALTER PROCEDURE [dbo].[sp_savedepart]
@nmDepartemen varchar(30)= NULL,
@Deskripsi varchar(30)= NULL,
@idDepartemen int = NULL,
@StatementType varchar(30)
AS
BEGIN	
	IF @StatementType ='Insert' 
	BEGIN
		SET NOCOUNT ON;
		INSERT INTO [dbo].[Location](nmDepartemen,Deskripsi) VALUES (UPPER(@nmDepartemen),@Deskripsi)
	END
	
	IF @StatementType = 'Select'
	BEGIN
		SELECT * FROM Location
	END
	
	IF @StatementType = 'Update'
	BEGIN
		UPDATE Location 
		SET nmDepartemen=@nmDepartemen, 
			Deskripsi=@Deskripsi
		WHERE idDepartemen=@idDepartemen
	END
	
	IF @StatementType = 'Delete'
	BEGIN
		DELETE FROM Location WHERE idDepartemen=@idDepartemen
	END
END

-- To Insert data --EXEC [dbo].[sp_savedepart] 'abc' , 'xyz' , NULL , 'Insert'
-- To Select data --EXEC [dbo].[sp_savedepart]  NULL ,  NULL , NULL , 'Select'
-- To Update data --EXEC [dbo].[sp_savedepart] 'abc' , 'xyz' ,  6   , 'Update'
-- To delete data --EXEC [dbo].[sp_savedepart]  NULL ,  NULL ,  6   , 'Delete'

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

ddtopgun
Starting Member

26 Posts

Posted - 2014-05-28 : 01:43:27

thanks for yr help..but i have another question...
if i use delete and i insert again why iddepartemen continuos not back to id which i deleted..

example : i deleted 1 record with id=18 and i do the insert the id continuos to 19 not 18..

how to reset the id so when i insert id can sequential..

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-28 : 02:16:31

Yes you can reset the seed value just use this

DBCC CHECKIDENT ('EmployeeDetails', RESEED,17)

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-28 : 02:22:50

This is what you asked for



ALTER PROCEDURE [dbo].[sp_savedepart]
@nmDepartemen varchar(30)= NULL,
@Deskripsi varchar(30)= NULL,
@idDepartemen int = NULL,
@StatementType varchar(30)
AS
BEGIN	
	IF @StatementType ='Insert' 
	BEGIN
		SET NOCOUNT ON;
		INSERT INTO [dbo].[Location](nmDepartemen,Deskripsi) VALUES (UPPER(@nmDepartemen),@Deskripsi)
	END
	
	IF @StatementType = 'Select'
	BEGIN
		SELECT * FROM Location
	END
	
	IF @StatementType = 'Update'
	BEGIN
		UPDATE Location 
		SET nmDepartemen=@nmDepartemen, 
			Deskripsi=@Deskripsi
		WHERE idDepartemen=@idDepartemen
	END
	
	IF @StatementType = 'Delete'
	BEGIN
		DELETE FROM Location WHERE idDepartemen=@idDepartemen
		SELECT CASE WHEN @@rowcount <>0 THEN 'Record  WITH ID -->'+CAST(@idDepartemen AS varchar(MAX))+'<-- Delted Sucessfully' 
					ELSE 'No record is found With given ID'
					END
		AS [Action] 
		DECLARE @sedValue INT= (SELECT MAX(idDepartemen) FROM Location)
		DBCC CHECKIDENT ('Location', RESEED,@sedValue)
		SELECT * FROM Location
	END
	
END

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

ddtopgun
Starting Member

26 Posts

Posted - 2014-05-28 : 03:23:26

thanks again MuralikrishnaVeera for yr help..that is what exactly i need..but why for action ID always 0 if i delete record not for the max last record

i use

CAST(RIGHT(@idDepartemen,2) AS varchar(MAX))

still same the value remark ID always 0 in action..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-28 : 11:48:15

Is there any reason why you want one sproc for all CRUD operations and why you want to reseed the identity? If you delete from the middle of the table are you going to check for missing values and insert new ones there? I guess what I'm getting at is you are introducing complexity and fragility for no apparent reason. So, just mentioning it incase you are not locked into your design. Unless this is for educational purposes, then learn away.. :)

Qualis
Posting Yak Master

145 Posts

Posted - 2014-05-28 : 14:16:45

One thing that ought to be pointed out here. If your identity field is your clustered index and you try to re-use removed identity values that are not sequential (you have 20 records, removed record 15 and want to re-use ID 15), you will incur significant IO overhead. SQL Server will need to re-arrange the data on disk to move your record into place.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-28 : 16:34:55

quote:
Originally posted by Qualis

One thing that ought to be pointed out here. If your identity field is your clustered index and you try to re-use removed identity values that are not sequential (you have 20 records, removed record 15 and want to re-use ID 15), you will incur significant IO overhead. SQL Server will need to re-arrange the data on disk to move your record into place.

How do you figure that? If a row was deleted the space will still be there, assuming the indexes weren't rebuilt or something. So, theoretically, there shouldn't be any page splits. But, I still think it is unnecessary to do that or reseed.

Qualis
Posting Yak Master

145 Posts

Posted - 2014-05-29 : 09:02:59

It is a concern because data size (he is only showing a few columns) and index rebuilds. If the data size issue comes up, reordering pages / page splits are very expensive. Next, removing items and then adding in-place causes a lot of external index fragmentation which can slow performance. Fragmentation = index rebuilds. Therefore, do not do this as it is a performance hit!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources