CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Alaa Jabre
Alaa Jabre

Reputation: 1883

SQL Server execute stored procedure in update statement

every updated record must have different value by using a procedure the procedure returns single integer value

declare @value int;
exec @value = get_proc param;
update table1 set field1 = @value;

this will work for one record but i want the procedure to get new value for each record

Upvotes: 4

Views: 8420

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Just a quick example of how to use a TVF to perform this type of update:

USE tempdb;
GO

CREATE TABLE dbo.Table1(ID INT, Column1 INT);

INSERT dbo.Table1(ID)
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;
GO

CREATE FUNCTION dbo.CalculateNewValue
(@ID INT)
RETURNS TABLE
AS
    -- no idea what this logic would be,
    -- just showing an example

    RETURN(SELECT NewValue = @ID + 1);
GO

SELECT t1.ID, n.NewValue
    FROM dbo.Table1 AS t1 
    CROSS APPLY dbo.CalculateNewValue(t1.ID) AS n;

Results:

ID NewValue
-- --------
 1        2
 2        3
 3        4

Now an update that uses the same information:

UPDATE t1 SET Column1 = n.NewValue
    FROM dbo.Table1 AS t1 
    CROSS APPLY dbo.CalculateNewValue(t1.ID) AS n;

SELECT ID, Column1 FROM dbo.Table1;

Results:

ID Column1
-- -------
 1       2
 2       3
 3       4

Upvotes: 2

xx77aBs
xx77aBs

Reputation: 4768

Does it really need to be a procedure ? If you can implement get_proc as function, then you can apply it on every record that you want ;)

Also, what are you using value1 for ? In the example that you've provided, it's not needed.

Upvotes: 0

Related Questions