SQL Server Performance Forum – Threads Archive
SP-looping
My store procCREATE PROCEDURE dbo.InsValErrors
(
@FileHeadersIDas integer,
@RecordTypeas char(2),
@DocNumas char(16),
@FieldInErroras varchar(50),
@ValidationCodeas integer
) AS set nocount on DELETE
ValidationErrors
WHERE
ValidationErrors.FileHeadersID = @FileHeadersID INSERT INTO ValidationErrors
(
FileHeadersID,
RecordType,
DocNum,
FieldInError,
ValidationCode
) VALUES
(
@FileHeadersID,
@Recordtype,
@DocNum,
@FieldInError,
@ValidationCode
)
GO Here though I am inserting i am able to get only 1 row, the thing is its looping and deleting the records.
what I want is delete old rows from the table and insert new rows and stay like that with new records Thanks!
"He laughs best who laughs last"
How are you looping? I don’t see the use of any while loops in here. Are you calling this from somewhere else? From what I can tell, your stored procedure simply deletes ALL rows where FileHeadersID = @FileHeadersID .. so no loops there … and just adds 1 row depending on the parameters you passed. – Tahsin
You do know that you can use any SELECT query, in lieu of a VALUES clause – right? The example you will find for INSERT INTO in BOL is indeed with a VALUES clause, which is a pretty stupid entry as it doesn’t tell you that you can also use any SELECT query you like.
You also seem to be unaware of the UPDATE action, which will replace a column value without first deleting the row. When you look up UPDATE in BOL, it doesn’t say that you can use a FROM clause to join information from all the tables you need, and a WHERE clause to select only the rows that you need to update. This way you can update a whole range of different values if you need to, without looping.
]]>