SQLTeam.com | Weblogs | Forums

Insert and Return record if Select fails to find Record


#1

I have a stored procedure that is passed an ID of a row. If that ID can't be found (i.e., the select returns no rows) it will insert a new record and then return that row via a select. I think the logic is right, but if the ID can't be found it isn't returning anything.

Any ideas what is wrong?

Select * From [Portfolio Register] Where ID = @Key
If @@ROWCOUNT = 0
Begin
	-- Key didn't exist so insert
	Declare @ID int
	INSERT INTO [Portfolio Register]
		([Project Reference], [Status], [System], [Temp])
		VALUES (NewID(), 'Open', 1, 1)
	Set @ID = SCOPE_IDENTITY()
	Select * From [Portfolio Register] Where ID = @ID
End

Thanks

Phil


#2

When the matching row does not exists, your code will execute two select statements. If you are processing the first recordset that comes back from the stored proc, that would be an empty set. One way to rewrite it would be as follows:

DECLARE @ID INT = @Key;
IF NOT EXISTS
(
	SELECT * FROM [Portfolio Register] WHERE ID = @Key
)
BEGIN
	INSERT INTO [Portfolio Register]
		([Project Reference], [Status], [System], [Temp])
		VALUES (NewID(), 'Open', 1, 1)
	Set @ID = SCOPE_IDENTITY()
END 
SELECT * FROM [Portfolio Register] Where ID = @ID;

#3

Many thanks - that works perfectly now

Phil


#4

This is not RDBMS at all. You are writing 1970's mag tape files in SQL. You need to stop and do a correct data model. We have no DDL or specs with which to help you, tho. Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements.

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells

I have a stored procedure that is passed an ID [sic] of a row [sic]. If that ID [sic] can't be found (i.e., the select returns no rows) it will insert a new record [sic] and then return that row via a select. I think the logic is right, but if the ID can't be found it isn't returning anything. <<

There is no generic “id” in RDBMS. Rows are not records. We retrieve them with a key. Why do you think that the count of physical insertion attempt (not even successes!) is a valid attribute of an entity? Answer: it is exactly what the record number on a mag tape is!

ISO does not allow spaces in data element name, but old COBOL programmers do this so they can keep display formatting in the same tier, like they do with monolithic COBOL programs.

I see you declare local variables; but we hate local variables in a declarative language like SQL. In RDBMS, a data element has “[]”; there is no generic “status”, “id”, “system”, etc. They have to be "_status”, "_id”, "_system”, etc.

Unlike the records in COBOL, a column is part of the whole schema. It does not have a local hierarchy to define it. You need to design the data and come up with an encoding for the “project_reference” that can be validated and verified.

Maybe a CREATE SEQUENCE could help, but who knows?