Select WHERE @where = @term returning an empty row

Can someone explain to me where I’m going wrong with this query? Inserting the parameters into the @where and @term causes it to work fine but within the context of my application where the parameters are required it falls over.

I have a profiler watching the server and this is what is happening when the interface that activates this query is used:

  • Extracting data from MS SQL Server-2008 referring multiple tables
  • Converting Oracle code to SQL Server for a fixture list
  • Eliminate row that has null value using CTE
  • Sql query, selecting on unique identifier gives - Error converting data type varchar to uniqueidentifier
  • is there an advantage to varchar(500) over varchar(8000)?
  • How can I display a consolidated version of my SQL Server table?
  • exec sp_executesql N' 
            CC,  simNumber,  Voice,  IMSI,  Network,  SOA,  
            Customer, Description,  partNumber,  serialNumber,  Contract,  issueDate,  
            expiryDate,  Datagate, status,  originalSheet,  originalRow  
        FROM tblViews  
        WHERE @where = @term ',N'@where nvarchar(3),@term nvarchar(5)',@where=N'SOA',

    Am I Missing something? I can’t really see any Issue as to why this wouldn’t work. Is there a rule that you can’t specify what to search for and in which column in the same query?

    One Solution collect form web for “Select WHERE @where = @term returning an empty row”

    Your query is syntactically malformed, more specifically:

    WHERE @where = @term
    --    ^^^^^^
    --   this bit

    Variables (such as @where and @term) can only contain values (therefore your particular use of @term is correct), but not table names, column names, SQL syntax, etc. (therefore your particular use of @where is incorrect).

    Search Google or Stack Overflow for “Dynamic SQL“, which is the name for what you’re trying to do. One resource that I recommend for SQL Server is Erland Sommarskog’s article, “Dynamic Search Conditions in T-SQL”.

    There are various pitfalls with Dynamic SQL. One that deserves special mention is the possibility of a type of security issue, so-called SQL injection attacks. Therefore, make sure to check out the section that shows how to combine dynamic SQL query text and variables without introducing that security issue.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.