SQL - Get Results of Insert

I needed to create a SQL procedure to automate adding a new user to a user table, and then also adding a relationship for this new user to a department. When I add a new user, SQL creates the user’s unique id (UserID). I need this new id to create the department relationship, but how do I get it (all in one procedure)? 

I made use of the OUTPUT option of the INSERT command as follows:

SQL Stored Procedure (SQL 2008 r2)

-- ============================================= -- Author:        <Carl Neer> -- Create date:   <11/20/2013> -- Description:   <Creates a new user and links them to a department.> -- ============================================= CREATE PROCEDURE  [dbo].[pr_AddUser_yourdbname]        -- Define the passed parameters        @UserName varchar(40),        @FirstName varchar(20),        @LastName varchar(20),        @Department varchar(100) AS BEGIN SET NOCOUNT ON;        -- Define a table and int variable to hold the assigned ID        DECLARE @TempTable TABLE (UserID int);        DECLARE @UserID int;        -- Insert the new user         INSERT             INTO User                 (                     UserName,                     FirstName,                     LastName,                     IsActive                  )             OUTPUT –- *** Output the inserted ID to our table ***                 inserted.UserID                 INTO                     @TempTable             VALUES                 (                     @UserName,                     @FirstName,                     @LastName,                     'TRUE'                 );        -- Get the new user ID into our int variable                         SET @UserID = (SELECT UserID FROM @TempTable);               -- Get Department ID for the passed in department name        DECLARE @DepartmentID int =             (SELECT DepartmentID                  FROM Department                  WHERE Department = @Department);        -- Insert into our department relationship table, using the         -- new UserID just created and the department id from above        INSERT            INTO User_Department                (                     UserID,                     DepartmentID                )            VALUES                (                    @UserID,                    @DepartmentID                ); END GO

SharePoint Hyperlink Fields

I seem to always forget how to access, set, and update a SharePoint 2010 Hyperlink field. I use these frequently to link items from one list to another. So here is my generic but tried and true code for these fields:

PowerShell

# Make sure the SharePoint namespace is loaded Add-pssnapin Microsoft.SharePoint.PowerShell –erroraction silentlycontinue; # Set some sample variables $yourservername = "contoso.local"; $yoursitepath = "sites/Accounting/Audits"; $yourlistname = "Links"; $youritemid = 18; $yourhyperlinkcolname = "RelatedCase"; # Steps to get the Url and Text from an existing list item # 1 – Get a reference to the listitem $web = get-spweb ("http://{0}/{1}" –f $yourservername, $yoursitepath); $list = $web.lists.trygetlist($yourlistpath); $item = $list.getitembyid($youritemid); # 2 – Create an SPFieldUrlValue object from the listitem column $itemHyperLink = new-object Microsoft.SharePoint.SPFieldUrlValue($item[$yourhyperlinkcolname].ToString()); # 3 - Display the text and url Write-host –f green "Text: " –nonewline; Write-host –f yellow $itemHyperLink.Description; Write-host –f green "Url : " –nonewline; Write-host –f yellow $itemHyperLink.Url; # Steps to create a new item with a hyperlink # 1 – Similar to #1 above get reference to a list $web = get-spweb ("http://{0}/{1}" –f $yourservername, $yoursitepath); $list = $web.lists.trygetlist($yourlistpath); # 2 – Create an empty SPFieldUrlValue $HyperLinkField = $list.fields[$yourhyperlinkcolname] -as [Microsoft.SharePoint.SPFieldUrl]; # 3 – Create an empty SPFieldUrlValue $newHyperLink = new-object Microsoft.SharePoint.SPFieldUrlValue; # 4 – Populate the description (text) and Url as desired $newHyperLink.Description = "Google"; $newHyperLink.Url = "http://google.com"; # 5 – Create a new list item $newItem = $list.items.add(); # 6 – Set the field using parse and set value method #     Note: You would also have to set any other required fields $HyperLinkField.parseandsetvalue($newItem,$HyperLinkField.tostring()); # 7 – Save the update $newItem.update(); $list.update(); # Dispose objects $web.dispose();

C# (Console Application)

// <>Partial namespace listing using Microsoft.SharePoint; // Set some sample variables string yourservername = "contoso.local"; string yoursitepath = "sites/Accounting/Audits"; string yourlistname = "Links"; int youritemid = 18; string yourhyperlinkcolname = "RelatedCase"; // <>Code omitted, insert code into Main // Access the site object using (SPSite site = new SPSite(string.Format("http://{0}/{1}", yourservername, yoursitepath))) {     // Access the web object     using (SPWeb web = site.OpenWeb())     {         // Steps to get the Url and Text from an existing list item         // 1 – Get a reference to the listitem         SPList list = web.Lists.TryGetList(yourlistname);         SPListItem item = list.GetItemById(youritemid);         // 2 – Create an empty SPFieldUrlValue         SPFieldUrlValue itemHyperLink =             new SPFieldUrlValue(item[yourhyperlinkcolname].ToString());         // 3 - Display the text and url         Console.WriteLine(string.Format("Text: {0}",             itemHyperLink.Description));         Console.WriteLine(string.Format("Url : {0}",             itemHyperLink.Url));         // Steps to create a new item with a hyperlink         // 1 – Create an empty SPFieldUrlValue         SPFieldUrlValue  newHyperLink = new SPFieldUrlValue();         // 2 – Populate the description (text) and Url as desired         newHyperLink.Description = "Google";         newHyperLink.Url = "http://google.com";         // 3 – Create a new list item         SPListItem newItem = list.Items.Add();         // 4 – Set the column to the SPFieldUrlValue         //     Note: You would also have to set any other required fields         newItem[yourhyperlinkcolname] = newHyperlink;         // 5 – Save the update         newItem.Update();    } // end using web } // end using site // Let the user terminate the program Console.Write("Press return to quit... "); Console.ReadLine();

Read XML with PowerShell

I have found that the ability to define and read an xml file with PowerShell is very useful. Often times I will generate XML from an Excel spreadsheet to drive a PowerShell script.

For example, what if we wanted to read this xml file using PowerShell? Let’s say it was in a file named “sample.xml”

<?xml version="1.0" encoding="utf-8"?> <albums>   <album name="Time Out">     <artist>The Dave Brubeck Quartet</artist>     <released>12/14/1959</released>   </album>   <album name="Red Headed Stranger">     <artist>Willie Nelson</artist>     <released>5/1/1975</released>   </album> </albums>

Here’s how I access all the attributes and nodes.

# path/file name of xml file     $xmlfile = "sample.xml";         # Read an xml file     [xml]$musiccatalog = get-content $xmlfile;     foreach ($album in $musiccatalog.albums.album) {         # Display what we've read         write-host;         write-host -f green "   Album Name: " -nonewline;         write-host -f yellow $album.name;         write-host -f green "   Artist    : " -nonewline;         write-host -f yellow $album.artist;         write-host -f green "   Released  : " -nonewline;         write-host -f yellow $album.released;     }

And here is the output

Album Name: Time Out    Artist    : The Dave Brubeck Quartet    Released  : 12/14/1959    Album Name: Red Headed Stranger    Artist    : Willie Nelson    Released  : 5/1/1975