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
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

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 ..
Go to Top of Page

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
Go to Top of Page

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..??
Go to Top of Page

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
Go to Top of Page

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..

Go to Top of Page

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..
Go to Top of Page
   

- Advertisement -