Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-13 : 14:04:34
|
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 outputCREATE PROC UpsertSales @ProductID int ,@EmployeeID int ,@Quantity int ,@SaleDate datetime ,@SalesID uniqueidentifier = NULL OUTPUTAS 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 = @SalesIDGODECLARE @SalesID uniqueidentifierEXEC UpsertSales 1, 6, 5, '2006-05-05 00:00:00.000', @SalesID OUTPUT PRINT 'NewID: ' + CAST(@SalesID AS nvarchar(50))GO |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-13 : 14:19:01
|
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 |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-13 : 14:47:15
|
Thanks a lot, work perfectly! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-13 : 22:32:45
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-14 : 03:11:08
|
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.aspxMy 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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 07:56:44
|
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.aspxMy 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? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-15 : 08:16:48
|
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. |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 08:20:58
|
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 :) |
|
|
|
|
|
|
|