Split words with space and search multiple columns in SQL Server

Here I have created sample that will help you out.

SQL

-- EXEC GetDetails 'David'
CREATE PROC GetDetails(@SearchName VARCHAR(200))
AS
BEGIN 
	CREATE TABLE #t_Organization(OrganizationID INT,Name VARCHAR(100),DescriptionShort VARCHAR(100),DescriptionLong VARCHAR(100),Comment VARCHAR(100),DateUpdated DATETIME)

	INSERT INTO #t_Organization VALUES(1,'David Morkel','Player','Player','Comment1','2015-02-02')
	INSERT INTO #t_Organization VALUES(2,'Kevin','Developer','Developer','Comment2','2015-02-03')
	INSERT INTO #t_Organization VALUES(3,'Peter','Engineer','Engineer','Comment3','2015-02-04')

	IF ISNULL(@SearchName,'') = ''
	BEGIN
		SELECT * FROM #t_Organization
	END
	ELSE
	BEGIN
		DECLARE @Count INT;
		DECLARE @i  INT = 0

		WHILE (@i) < LEN(@SearchName)
		BEGIN      
			SELECT @Count = COUNT(*) FROM #t_Organization
			WHERE (Name LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i)  + '%') 
			OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i)  + '%')
			OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName)- @i)  + '%')
			OR (Comment LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i)  + '%')
			IF @Count > 0
			BEGIN
				SELECT * FROM #t_Organization
				WHERE (Name LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i)  + '%') 
				OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i)  + '%')
				OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i)  + '%')
				OR (Comment LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i)  + '%')
				BREAK;
			END
			SET @i = @i + 1
    
		END
	END
	DROP TABLE #t_Organization
END

Screenshot