Using SQL Server stored procedure to match column name to variable

Answer to the current version of your question (Please do not update it again):

You don't need to SELECT the column name from the table by itself first and then try to join it to the query you want. Your SELECT * FROM tbl_Client_List_Pricing already contains the column 'FOREMAN-REGULAR TIME (8hr Day Monday-Friday)* (HR)', so you can use dynamic sql to do this:

For example:

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'SELECT [Client], [' + @Labor_Type + ']' + '
FROM tbl_Client_List_Pricing
WHERE [CompanyNum] = @CompanyID'

--PRINT @sql

EXEC (@sql)

To provide you with the results:

+---------------------+---------------------------------------------------+
|       Client        | FOREMAN-REGULAR TIME(8hr Day Monday-Friday)* (HR) |
+---------------------+---------------------------------------------------+
| Alston Construction |                      45                           |
+---------------------+---------------------------------------------------+

Your original question:

You asked why you were getting the following error, when trying to execute a stored procedure like this:

sp_Get_Labor_Rates_By_CompanyID '5204' 'STRAW WATTLE > 10000 LF-INSTALLED-Photo (LF)'

Incorrect syntax near 'STRAW WATTLE > 10000 LF-INSTALLED-Photo (LF)'

My Answer:

When you execute a stored procedure with more than one parameter, use a comma between your parameters.

Check out Execute a stored procedure or function

As you'll see there, you could either supply the parameter names:

[ [ @parameter = ] { value   
                           | @variable [ OUTPUT ]   
                           | [ DEFAULT ]   
                           }  
        ] 

Execution:

sp_Get_Labor_Rates_By_CompanyID @CompanyID = '5204', @Material_Type = 'STRAW WATTLE > 10000 LF-INSTALLED-Photo (LF)'`

OR

Supply them in the order given in the CREATE PROCEDURE statement (at the time of execution):

[ ,...n ] 

sp_Get_Labor_Rates_By_CompanyID '5204', 'STRAW WATTLE > 10000 LF-INSTALLED-Photo (LF)'

If you supply parameters in the form @parameter = value, you can supply them in any order. You can also omit parameters for which defaults have been supplied. If you supply one parameter in the form @parameter = value, you must supply all subsequent parameters this way. If you do not supply parameters in the form @parameter = value, you must supply them in the order given in the CREATE PROCEDURE statement.