Stored Procedure with Parameters

Question

Stored Procedure with Parameters

smthembu

Hall of Fame

Points: 3464
More actions
May 16, 2008 at 7:54 am

#129079

Dear All
I'm a new dba in training, i was asked to write a procedure that will take parameters also to update a column with a NULL for a specific staff, using a parameter of a staff_numb. Please help. Tx!!
It's better to fail while trying, rather than fail without trying!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 1

I think you are looking for SP with parameters, there are volumes of article out there to learn, probably you are looking for something like

CREATE PROCEDURE SP_UpdateStaff

@staf_num int

AS

Update tbl_staff

SET ColA = 'My Update'

WHERE staff_num = @staf_num

EXECUTE SP_UpdateStaff '1234'

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

smthembu Hall of Fame Points: 3464 More actions · Answer 2

Thanks for your response, i applied the below with few modifications.

After exec, no record affected. Please help. Thanks once again

CREATE PROCEDURE SP_UpdateStaff

@staf_num VARCHAR

AS

Update SECURITY_USER1

SET PASSWORD_FULL = NULL

WHERE staff_numb = @staf_num

EXECUTE SP_UpdateStaff '1012343'

It's better to fail while trying, rather than fail without trying!!!

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

What doesn't change? are you sure the record didn't have a null?

Run the query without the stored procedure, just the update. Does it work?

smthembu Hall of Fame Points: 3464 More actions · Answer 4

Thanks for your time and help, i found the problem it now works.

I need help on the same issue with regards in updating the domain account. Example

Staff_numb USER_ID WINDOWS AUTHENTICATION

123456 DOMAIN\asmith 1

i need to create a stored proc to update the userid, e.g. lets say she changes a surname from smith to johnson, so my stored proc should use parameters with @staff_numb and with a new @domain\userid to apply new userid.

This is tricky for me, Please help!!

Thanks

It's better to fail while trying, rather than fail without trying!!!

Animal Magic SSChampion Points: 13964 More actions · Answer 5

smthembu (5/21/2008)
Thanks for your time and help, i found the problem it now works.
I need help on the same issue with regards in updating the domain account. Example
Staff_numb USER_ID WINDOWS AUTHENTICATION
123456 DOMAIN\asmith 1
i need to create a stored proc to update the userid, e.g. lets say she changes a surname from smith to johnson, so my stored proc should use parameters with @staff_numb and with a new @domain\userid to apply new userid.
This is tricky for me, Please help!!
Thanks

create procedure USP_UpdateUser

@staff_num int,

@previousName varchar(40),

@newName varchar(40)

AS

update staff set user_id = replace(user_id,@previousname,@newName) where staffNum = @staff_num

go

So if your record was staff_num 1 and username 'domain\johnmo' and you ran USP_Updateuser 1,'mo','test' you would get staff_num 1, username 'domain\johntest'

smthembu Hall of Fame Points: 3464 More actions · Answer 6

Thank you very much for your help it worked. Now it's on production

It's better to fail while trying, rather than fail without trying!!!