IF
		@SalesID IS NULL			
			SET @SalesID = NEWID()  ---SQL doesn't seem to like this line, removed it will compile and run but doesn't return the newid when one is created
			INSERT INTO Sales
				SELECT @SalesID, @ProductID, @EmployeeID, @Quantity, @SaleDate
	ELSE
		UPDATE
			Sales
		SET 
			 ProductID = @ProductID, EmployeeID = @EmployeeID, Quantity = @Quantity, SaleDate = @SaleDate
		WHERE
			SalesID = @SalesID

CREATE PROC UpsertSales
	@ProductID int 
	,@EmployeeID int
	,@Quantity int
	,@SaleDate datetime
	,@SalesID uniqueidentifier = NULL OUTPUT
AS
	IF
		@SalesID IS NULL			
			
			INSERT INTO Sales
				SELECT NEWID(), @ProductID, @EmployeeID, @Quantity, @SaleDate
	ELSE
		UPDATE
			Sales
		SET 
			 ProductID = @ProductID, EmployeeID = @EmployeeID, Quantity = @Quantity, SaleDate = @SaleDate
		WHERE
			SalesID = @SalesID
GO


DECLARE @SalesID uniqueidentifier

EXEC UpsertSales    
	 1, 6, 5, '2006-05-05 00:00:00.000', @SalesID OUTPUT
	
PRINT 'NewID: ' + CAST(@SalesID AS nvarchar(50))
GO

	IF
		@SalesID IS NULL
                BEGIN	------------ THIS		
			SET @SalesID = NEWID()  ---SQL doesn't seem to like this line, removed it will compile and run but doesn't return the newid when one is created
			INSERT INTO Sales
				SELECT @SalesID, @ProductID, @EmployeeID, @Quantity, @SaleDate
                END   ------------ AND THIS
	ELSE
		UPDATE
			Sales
		SET 
			 ProductID = @ProductID, EmployeeID = @EmployeeID, Quantity = @Quantity, SaleDate = @SaleDate
		WHERE
			SalesID = @SalesID

quote:

---

Originally posted by James K

As an addendum to what Visakh suggested, if you use the default constraint and still want to return the NEWID generated to the calling program via the @SalesID variable, you can use the OUTPUT clause. There is documentation and examples on MSDN: http://msdn.microsoft.com/en-us/library/ms177564.aspx

My own side note: If the newid column is your primary key in your Sales table (which looks like it is) and if it is also the clustering key (which it probably is), that would not be a great choice - especially in a table such as Sales, where there may be lot of insertions. So pick a different clustering key - I don't know what that might be - or, if you have no choices, make sure you defrag your indexes very regularly.

---

quote:

---

This is just a test table/data to get used to creating sprocs - working through training videos and these are the tables they use.

---

quote:

---

i'm aware of defaults, but if i set the NEWID to be NULL then the 'user' wouldn't be able to update existing queries by using this sproc?

---

quote:

---

Originally posted by James K

quote:

---

This is just a test table/data to get used to creating sprocs - working through training videos and these are the tables they use.

---

Understood! :)

quote:

---

i'm aware of defaults, but if i set the NEWID to be NULL then the 'user' wouldn't be able to update existing queries by using this sproc?

---

You are right. I think what Visakh meant was that in the case where you are trying to insert, you wouldn't need to explicitly create the new value using the "SET @SalesID = NEWID()" statement. If this is the only place where you are inserting rows into that table, it may not be worth the effort to add the default value and use the output clause.

---