Parameterized SQL Query error

Question

I have an application that builds dynamic parameterized SQL queries. The following query returns an inner exception of "syntax error at or near "="...

I am thinking it is in the way that I am assigning the parameter to the column name but, I'm not sure. Code follows.

SELECT TOP 50 77 AS Repository, DocID, LastFileDate, ImageCount,
 LockedUserID,   DocClass, Offline, FileType, OrigFileName, C_CredCardSSN, C_CredCardName,C_CredCardDocType, C_CredCardDocDate, C_CredCardStatus 

FROM D77 

WHERE C_CredCardSSN 

LIKE C_CredCardSSN = @sp1% 

ORDER BY C_CredCardSSN,C_CredCardName

EDIT: Thanks to everyone. All of these answers helped. =)

MarkusQ · Accepted Answer · 2009-03-26 20:26:05Z

5

Try just

WHERE C_CredCardSSN LIKE @sp1

or

WHERE C_CredCardSSN LIKE @sp1+'%'

depending on why you had a "%" there (thanks to Joel Coehoorn's comment for pointing out the "%" I'd missed).

Or (in response to your comment) maybe:

WHERE C_CredCardSSN LIKE ''+@sp1

edited Mar 26, 2009 at 20:26

answered Mar 26, 2009 at 19:53

MarkusQ

21.9k4 gold badges58 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joel Coehoorn Over a year ago

only 1 of 2 problems fixed here

Jon Ownbey Over a year ago

The problem I get here is that it will now return an error that says I must declare a scalar variable \"@sp1"\. This was where I started at and had added the "C_CredCardSSN = @sp1" to fix. If it helps, this is all constructed in a string builder and then passed a command text to a SqlCommand object

Joel Coehoorn · Accepted Answer · 2009-03-26 19:57:23Z

4

The LIKE statement should look like this:

WHERE C_CredCardSSN LIKE @sp1 + '%'

answered Mar 26, 2009 at 19:57

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Comments

Eppz · Accepted Answer · 2009-03-26 19:57:34Z

2

Try this:

WHERE C_CredCardSSN like @sp1 + '%'

This will only find your C_CredCardSSN if the first few characters are correct. To find anything within that value try it like this

WHERE C_CredCardSSN like '%' + @sp1 + '%'

answered Mar 26, 2009 at 19:57

Eppz

3,2362 gold badges21 silver badges26 bronze badges

Comments

scottm · Accepted Answer · 2009-03-26 20:37:56Z

1

Try adding the '%' to the text before adding it as a parameter to the query:

SqlParameter p = new SqlParameter("@sp1");
p.value = C_CredCardSSNText + "%";

answered Mar 26, 2009 at 20:37

scottm

28.7k22 gold badges109 silver badges161 bronze badges

Collectives™ on Stack Overflow

Parameterized SQL Query error

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related