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
@term causes it to work fine but within the context of my asp.net 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:
exec sp_executesql N' SELECT 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', @term=N'64367' go
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
@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.