SQL Using parameter in WHERE statement

I'm posting this as answer, as the comments aren't enough to explain, however, there is far too little detail here to actually answer the OP's question.

Firstly, the error the OP is getting would be impossible to get with the tiny amount of SQL they have provided. If we expand it into a full Pseudo-SQL query, we get:

SELECT {Columns}
FROM Customer
WHERE Customer.Name = (' + @Name + ');

This would return rows in the Customer table, where Name has the literal string value ' + @Name + '. Very unlikely.

I suspect that the OP simply needs to do:

SELECT {Columns}
FROM Customer
WHERE Customer.Name = @Name;

HOWEVER, the error the OP is getting strong implies they are using Dynamic SQL. This means they have a query like this:

DECLARE @SQL nvarchar(MAX);
DECLARE @Name varchar(20) = 'John';
SET @SQL = N'SELECT {Columns} FROM Customer WHERE Customer.Name = (' + @Name + ');';
EXEC (@SQL);

The problem here is that translates into the SQL:

SELECT {Columns} FROM Customer WHERE Customer.Name = (John);

Notice, no quotes around John, and why the error.

What you are doing here, however, is a very bad idea. Raw string concatenation like that leaves your SQL wide open to injection(SQL injection). Parametrise your SQL:

DECLARE @SQL nvarchar(MAX);
DECLARE @Name varchar(20) = 'John';
SET @SQL = N'SELECT {Columns} FROM Customer WHERE Customer.Name = @dName;'
EXEC sp_executesql @SQL, N'@dName varchar(20)',@dName = @Name;`

Edit: Ok, the OP has provided us with their query. Honestly, it's a mess, far worse that I wanted it to be. Like i said before, raw string concatenation is an awful idea, it leaves you open to injection. You can't get rid of concatenation for dynamic objects, but you can use QUOTENAME, which makes the code safe(r).

You're also missing a FROM; no idea what that needs to be, so i've left that as pseudo-sql. That RIGHT JOIN is very messy too, but I've no idea what you're trying to achieve there.

Anyway, this need to replace your EXEC command. Read the below, understand the below, and don't make the mistake of concatenating raw strings in your dynamic SQL:

DECLARE @SQL nvarchar(MAX)

SET @SQL = N' USE ' + QUOTENAME(@Database) + N';' + NCHAR(10) +
           N'SELECT Customer.Name,'  + NCHAR(10) +
           N'       [Product Categories].[Product Number]' + NCHAR(10) +
           --where is your FROM?
           N'FROM {Something}' + NCHAR(10) + --This needs fixing
           N'     RIGHT JOIN((' + QUOTENAME(@Schema) + N'.Customer' + NCHAR(10) + --This whole thing is messy, but i'm not fixing it, as I have no data
           N'                 INNER JOIN ' + QUOTENAME(@Schema) + N'.Client ON Customer.Name = Client.Name)' + NCHAR(10) +
           N'                 LEFT JOIN ' + QUOTENAME(@Schema) + N'.Product ON Client.Name = Product.Client)ON [Product Categories].[Product Number] = Client.Product' + NCHAR(10) +
           N'WHERE (Customer.Name = @dName' + NCHAR(10) +
           N'   AND Customer.[Order] = ''Y'')' + NCHAR(10) +
           N'   OR (Client.Name = @dName' + NCHAR(10) +
           N'   AND Customer.[Order] = ''Y'');';

EXEC sp_executesql @SQL, N'@dName varchar(20)', @dName = @Name;