September 10, 2003 at 7:06 am
Hi,
I want to write an update query that will call a stored procedure for every record on a table, and update a field on that table based on the output from the stored procedure.
I already have a stored procedure which generates a random password, and I would like to call it for every record on my users table, updating the "password" field on that table as I go.
Can you help please?
September 10, 2003 at 7:18 am
I can't think how you would do it with a proc but you could
use a function to update each row in the table.
Convert the proc to a function.
Update Table set PWDCol = dbo.FuncName(Maybe some param)
[Maybe some where]
This'll work fin but could / will slow down if there are a large number of rows.
Why not use the logic in the proc in the update statement?
That would be the fastest route.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
September 10, 2003 at 7:21 am
May I suggest something
I would create a function that returns the value that you want so you could do this
CREATE FUNCTION FULLUSERNAME()
RETURNS VARCHAR(20) AS
BEGIN
DECLARE @UserName AS VARCHAR(20)
SET @UserName = UPPER(LTRIM(RTRIM(SUSER_SNAME())))
RETURN @UserName
END
-- now in your stored procedure
INSERT INTO MyTable
(CurrentDate,
UsersName)
VALUES
(GETDATE(),
dbo.FULLUSERNAME()
)
thats it.. If you want to use a stored procedure then you have to create a variable that will hold the value
DECLARE
@returnVal
EXEC Procedure {params} , @returnVal OUTPUT
Now you would be able to reference that value
INSERT INTO MyTable
(CurrentDate,
UsersName)
VALUES
(GETDATE(),
@returnVal)
but this may take a little longer because you will need to loop through all the records in your table, with the function you can do a set baised update no need for a loop much faster.
Hope this helps
Will
September 10, 2003 at 7:25 am
Hi,
Many thanks for your help
I want to keep a separate "generator" function, so changing from a Stored Proc to a Function looks like the best way to go.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply