SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SPROC help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Robowski
Posting Yak Master

101 Posts

Posted - 01/13/2013 :  14:04:34  Show Profile  Reply with Quote
Hi,

I'm studying for the 70-461 querying SQL 2012 cert and bit stuck with the below stored procedure; can someone advise where I am going wrong please?

Below is my proc - it works fine except it doesn't output the NEWID, I understand why it doesn't, I haven't set the NEWID to have a value anywhere, if I set it within the IF statement SQL doesn't like the syntax.



	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



Full code working SPROC code except for the @SalesID will not output


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

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/13/2013 :  14:19:01  Show Profile  Reply with Quote
You need a begin and end block if there is more than one statement within the if block (or within the else block)
	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
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/13/2013 :  14:47:15  Show Profile  Reply with Quote
Thanks a lot, work perfectly!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/13/2013 :  22:32:45  Show Profile  Reply with Quote
as a side note you can attach the NEWID() to column via a DEFAULT constraint so that we dont have to explicitly pass it in INSERT each time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/14/2013 :  03:11:08  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/14/2013 :  03:34:11  Show Profile  Reply with Quote
see below to understand what James was talking about

http://connectsql.blogspot.in/2009/07/uniqueidentifier-column-as-primary-key.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  07:56:44  Show Profile  Reply with Quote
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.




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

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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/15/2013 :  08:16:48  Show Profile  Reply with Quote
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.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  08:20:58  Show Profile  Reply with Quote
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.



OK, Yeah that's how I wrote it for the insert one when testing that, didn't have the multi statement issue in that which you advised on, it was only falling over with the 'Upsert' one shown above.

Thanks anyhow though :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000