Concatenate multiple row values in one string

Objective:
Here is another one. Suppose you have one table with two columns, ID & Name. However, you need to select values from the Name column and separate the values with a comma like this:
Name1, Name2, Name3…

Here is an example:

You have table with two fields:


You want the result set to look like the following:

Solution:
The solution is very simple, as is always :)

1. Create one User Defined Function (UDF) that will accept the comma separated ID and return the comma separated name.

CREATE FUNCTION Func_GetNameFromCommaSepId(

@Id VARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

DECLARE @Name NVARCHAR(MAX)

SELECT @Name = COALESCE(@Name+ ', ', '')+ Name From tblMaster where Convert(varchar(5),Id) in(Select DataItem From dbo.Func_SplitString(@ID,','))

RETURN @Name

END

2. Now use this UDF as your query.

select dbo.Func_GetNameFromCommaSepId('1,2,3,4,5,6,7')as Name

Your result set will look like this:

Split String in SQL Server UDF with custom delimiter

Objective:
Ok friends, suppose you are writing a stored procedure and you have no idea how many parameter values to pass to it. Now what to do?

Solution:
There are a few options you can try here. The first option, you can write an iterative procedure that calls the stored procedure several times; however, this option makes unnecessary calls to your stored procedure and reduces the performance of the application. Alternatively, you can pass one single string to the stored procedure with a delimiter and split the string inside the stored procedure. This will give you a result set in tabular format providing fewer stored procedure calls and better performance that the first option.

Here I am going to show you how to pass a string and get the result set.

1. Create one User Defined Function (UDF) that accepts two parameters
1. StringToSplit – String you want to split
2. DelimiterToSplit – Delimiter

The function will return one table that includes a column called “DataItem”.

CREATE FUNCTION Func_SplitString(

@StringToSplit VARCHAR(MAX),

@DelimiterToSplit VARCHAR(5) = ','

) RETURNS @ReturnList TABLE (DataItem VARCHAR(100))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@DelimiterToSplit,@StringToSplit,0) <> 0

BEGIN

SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@StringToSplit,1,CHARINDEX(@DelimiterToSplit,@StringToSplit,0)-1))),

@StringToSplit=RTRIM(LTRIM(SUBSTRING(@StringToSplit,CHARINDEX(@DelimiterToSplit,@StringToSplit,0)+LEN(@DelimiterToSplit),LEN(@StringToSplit))))

IF LEN(@sItem) > 0

INSERT INTO @ReturnList SELECT Replace(@sItem,'''','')

END

IF LEN(@StringToSplit) > 0

INSERT INTO @ReturnList SELECT Replace(@StringToSplit,'''','')

RETURN

END

2. Now, you have several options available for using this UDF
1. In T-SQL -

select * from dbo.Func_SplitString('sujeet#kumar#Richmond#Virgina#23060#USA','#')

2. In the Stored Procedure -

CREATE Procedure Proc_AddEdit_GroupMaster

@GroupName VARCHAR (MAX)

AS

BEGIN

Declare @NewGroupName AS Varchar(MAX)

SET @NewGroupName = REPLACE(@GroupName,'"','''')

--Insert Return value from Func_SplitString to tblGroupMaster

Insert into tblGroupMaster

(GroupName) (Select DataItem From dbo.Func_SplitString(@NewGroupName,','))

END

Now, all you need to do is to determine the best way to use the UDF in your application.
Note: There are a few limitations with this solution. For homework, try to see if you can locate these. :)

Export & Import SharePoint Site, Library & Lists using PowerShell

If you want to take backup of your SharePoint Site, Library and List… there are several methods available. The more common methods include:

1.SharePoint Central Admin.

2.Command Line Utility

a. PowerShell.

b. STSADM

Here we will discuss how we can do it using PowerShell.
First, let me tell you about PowerShell. PowerShell is new tool that consists of a command line shell and associated scripting language. Similar to DOS in its use of a command line interface, PowerShell is more powerful than other command line shell. With PowerShell you can run a single command as well as batch file commands. If you know DOS then the good news is that you can use some of the same DOS commands on PowerShell. Microsoft highly recommends use PowerShell. Why so? Theory behind this is: You can’t manage SharePoint 2010 completely without using PowerShell. STSADM commands can get you most of the things done and are useful for migration but not all.

Here are some tips for using PowerShell:
1.Get list of all PowerShell commands : Get-Command
2.Get help of any PowerShell command: Get-Help "Command Name" or "Command Name" -?

Let’s move to our main topic: how to use PowerShell to Export & Import SharePoint 2010 objects.
In this example we are creating one site with a list and library.

First, we add a blank site to the site collection.
Now, follow the steps below to open the PowerShell command interface on your system.
1. On the Start menu, click All Programs.
2. Click Microsoft SharePoint 2010 Products.
3. Click SharePoint 2010 Management Shell.
4. Your screen will look like


Take a look at the TechNet article, Add-SPShellAdmin, for a list of minimum requirements needed to run the PowerShell command.
5. Now run this command for Export:
Export-SPWeb -Identity "Site URL / GUID" -Path "Path and file name"

6. It will create two files on specified location.

Your site is now exported :)

The Export –SPWeb command includes several options for exporting sites, lists and libraries. For more information, read the article Export -SPWeb on TechNet.

Now, let’s create a new site in the SharePoint environment by importing the site we exported in the previous steps. It is important to create the new site using the same template; otherwise, the process will generate an exception during the import.
1. Open PowerShell.
2. Now run this command for Import
Import-SPWeb -Identity "Site URL / GUID" -Path "Export file name"
If you want to overwrite existing site use “-Force” option.
The Import –SPWeb command also includes several options for importing sites, lists and libraries. For more information, read the TechNet article: Import-SPWeb
3. Now your new site will look the same as your source site.

4. It will create one file for the import log.

Now eNjoy :)