Update with SP and Pass Parameter

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)

Update with SP and Pass Parameter

Author

Topic

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-16 : 22:25:44

hi,

i have table with 2 field (idDepartemen uniqueidentifier,nmDepartemen)
and i have the code like below for update the table


IF @StatementType = 'Update'
	BEGIN
		SELECT @idDepartemen=idDepartemen FROM Departemen WHERE nmDepartemen=@nmDepartemen
		UPDATE Departemen 
		SET nmDepartemen=@nmDepartemen			
		WHERE idDepartemen=@idDepartemen
		SELECT nmDepartemen FROM Departemen
	END

how to update nmDepartemen with passing parameter and how to give the error handling if the record is nothing or is empty but the error can send to variable so i can get the value of variable to my program

example for call sp:
exec sp_isudDepartemen 'Tes','Tes2','Update' --but error too many parameters..???

or if another example for the sp plzz welcome..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 02:51:46

Is your procedure using three parameters?

Madhivanan

Failing to plan is Planning to fail

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-17 : 02:54:06

yes i know, so i need help how to use update SP with three parameters..?? or still use the SP above but how to call the SP if i want to update in middle record..??

coz if i use the above SP the last record is always update..

for example i have data :
id(uniqueidentifier), nmdepartemen
1,tes
2,tes2
3,tes3
4,tes4
5,tes5
6,tes6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 03:38:45

You need to use the third parameter that accepts "update" as a value and use the code



IF @StatementType = 'Update'
	BEGIN
		SELECT @idDepartemen=idDepartemen FROM Departemen WHERE nmDepartemen=@nmDepartemen
		UPDATE Departemen 
		SET nmDepartemen=@nmDepartemen			
		WHERE idDepartemen=@idDepartemen
		SELECT nmDepartemen FROM Departemen
	END

Madhivanan

Failing to plan is Planning to fail

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-17 : 03:45:35

quote:
Originally posted by madhivanan

You need to use the third parameter that accepts "update" as a value and use the code

IF @StatementType = 'Update'
BEGIN
SELECT @idDepartemen=idDepartemen FROM Departemen WHERE nmDepartemen=@nmDepartemen
UPDATE Departemen
SET nmDepartemen=@nmDepartemen
WHERE idDepartemen=@idDepartemen
SELECT nmDepartemen FROM Departemen
END

Madhivanan

Failing to plan is Planning to fail

can u give me sample code for third parameters ..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 03:47:21

create procedure procname (@param1 datatype, @param2 datatype, @StatementType varchar(20))
as
<your code>

Madhivanan

Failing to plan is Planning to fail

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-17 : 04:01:15

quote:
Originally posted by madhivanan

create procedure procname (@param1 datatype, @param2 datatype, @StatementType varchar(20))
as
<your code>

Madhivanan

Failing to plan is Planning to fail

for the update statement in SP
what i must to set..

sample code,plz to correction

IF @StatementType = 'Update'
	BEGIN
		DECLARE 
			@nmDepartemenX nvarchar(50)
		
		SELECT @idDepartemen=idDepartemen FROM Departemen WHERE nmDepartemen=@nmDepartemen
		SELECT @nmDepartemenX=nmDepartemenX FROM Departemen
		UPDATE Departemen 
		SET 
			nmDepartemen=@nmDepartemen,
			nmDepartemenX=@nmdepartemenX
		WHERE 
			idDepartemen=@idDepartemen
			AND nmDepartemen=@nmDepartemen
		SELECT nmDepartemen FROM Departemen
	END

coz when i execute the SSMS give me error :
Invalid column name 'nmDepartemenX'.

that's right cozz i don't have a column nmDepartemenX

so can u give me how to make third parameter for update statement..??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 05:13:40

When you do not have a column, how can you update it? Can you please post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-17 : 21:04:36

quote:
Originally posted by ddtopgun

yes i know, so i need help how to use update SP with three parameters..?? or still use the SP above but how to call the SP if i want to update in middle record..??

coz if i use the above SP the last record is always update..

for example i have data :
id(uniqueidentifier), nmdepartemen
1,tes
2,tes2
3,tes3
4,tes4
5,tes5
6,tes6

above it's sample data

and i want to update sample id 3 being tes7
if i use my SP always the last record updated..
my question is how to update first or middle record to update..

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-17 : 23:48:43

quote:
Originally posted by ddtopgun

quote:
Originally posted by ddtopgun

yes i know, so i need help how to use update SP with three parameters..?? or still use the SP above but how to call the SP if i want to update in middle record..??

coz if i use the above SP the last record is always update..

for example i have data :
id(uniqueidentifier), nmdepartemen
1,tes
2,tes2
3,tes3
4,tes4
5,tes5
6,tes6

above it's sample data

and i want to update sample id 3 being tes7
if i use my SP always the last record updated..
my question is how to update first or middle record to update..

case closed i found my problem..

thnx for yr help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources