Select statement not working in sql server

Question

In microsoft sql server 2005, classic asp code, I call a sql query using this:

selectHireResponseSQL = "
    SELECT HireResponseID, HireResponse, DateResponse, Comments, YearFileOpened
      , file_number, isCaseOpen, last_update, isConfidential, date_created
      , OurClient, TheirClient, ProjectName, DESCRIPTION, lawyer_lastname
      , lawyer_firstname, Conflicts.ConflictID
  FROM Hire_Response
       , Conflicts
       , Lawyers
 WHERE  Hire_Response.ConflictID = Conflicts.ConflictID
   AND Lawyers.lawyerID = Conflicts.lawyerID
   AND firmID IN (" & FirmIDString & ")
   AND HireID = " & HireID & "
   AND isStillaConflict = 1
 ORDER BY
       file_number
       , TheirClient
       , OurClient
       , lawyer_lastname
       , lawyer_firstname
"

The above isn't a stored procedure. Also the FirmIDString variable is a string that is a comma delimited list of numbers, like this for example '1,2,3'.

An example of after the string gets formatted is:

select HireResponseID, HireResponse, DateResponse, Comments, YearFileOpened, file_number, isCaseOpen, last_update, isConfidential, date_created, OurClient, TheirClient, ProjectName, description, lawyer_lastname, lawyer_firstname, Conflicts.ConflictID 
from Hire_Response, Conflicts, Lawyers 
WHERE Hire_Response.ConflictID=Conflicts.ConflictID AND Lawyers.lawyerID=Conflicts.lawyerID AND firmID IN (47,140,138,137,139) AND HireID = 594 AND isStillaConflict = 1 
ORDER BY file_number, TheirClient, OurClient, lawyer_lastname, lawyer_firstname

Now I want to turn this into a stored procedure. So I changed the asp classic code to

selectHireResponseSQL = "
               EXEC ps_selectHireResponseSQL '" & FirmIDString & "'," & HireID

And the stored procedure is:

SELECT HireResponseID, HireResponse, DateResponse, Comments, YearFileOpened
      , file_number, isCaseOpen, last_update, isConfidential, date_created
      , OurClient, TheirClient, ProjectName, DESCRIPTION, lawyer_lastname
      , lawyer_firstname, Conflicts.ConflictID
  FROM Hire_Response
       , Conflicts
       , Lawyers
 WHERE  Hire_Response.ConflictID = Conflicts.ConflictID
   AND Lawyers.lawyerID = Conflicts.lawyerID
   AND CHARINDEX(',' + CAST(firmID AS NVARCHAR) + ',',','+@FirmIDString + ',') >0
   AND HireID = @HireID
   AND isStillaConflict = 1
 ORDER BY
       file_number
       , TheirClient
       , OurClient
       , lawyer_lastname
       , lawyer_firstname

But now I am not getting any records at all (the code seems to run without errors though). I know I should be getting records, because if I switch to the non stored procedure, I get records.

Does anyone know what is wrong here?

This code is vulnerable to sql injection attacks. You're practically begging to get hacked. — Joel Coehoorn
– Joel Coehoorn, Commented May 29, 2013 at 13:42

Gordon Linoff · Accepted Answer · 2013-05-29 13:45:29Z

3

Here is an improved re-write of your query (this only fixes the aliases, the joins, and the nvarchar without a size):

select HireResponseID, HireResponse, DateResponse, Comments, YearFileOpened, file_number,
       isCaseOpen, last_update, isConfidential, date_created, OurClient, TheirClient,
       ProjectName, description, lawyer_lastname, lawyer_firstname, Conflicts.ConflictID 
from Conflics c join
     Hire_Response hr
     on hr.ConflictID=c.ConflictID join
     Lawyers l
     on l.lawyerID=c.lawyerID 
WHERE CHARINDEX(',' + CAST(firmID as varchar(30)) + ',', ',' + @FirmIDString + ',') > 0 
    AND HireID = @HireID
    AND isStillaConflict = 1 
ORDER BY file_number, TheirClient, OurClient, lawyer_lastname, lawyer_firstname;

This will not fix your problem. It would be helpful if you printed out the working version after it is formatted.

My best guess is that @FirmIDString` has commas and spaces between the ids. If so, then this should work:

WHERE CHARINDEX(', ' + CAST(firmID as varchar(30)) + ', ', ', ' + @FirmIDString + ', ') > 0

answered May 29, 2013 at 13:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

omega Over a year ago

I did a response.write on the query being made (non stored procedure), and it was EXEC ps_selectHireResponseSQL '76',659, so even if there is 1 number, it still doesn't work... When theres multiple numbers, it gets formatted to EXEC ps_selectHireResponseSQL '47,140,138,137,139',594

Gordon Linoff Over a year ago

@omega . . . Are you sure that the original query returns rows in this case? If so, can you edit your question with a print out of the query that works?

Gordon Linoff Over a year ago

@omega . . . I have an idea. When you declare @FirmId as an argument to the stored procedure, do you use varchar or varchar(<some length>)? If you don't have a length, then it defaults to 1, and everything is truncated.

omega Over a year ago

I updated the post to show an example. And in the code I declared it like (@FirmIDString as nvarchar, @HireID as int)

omega Over a year ago

I believe you are right, I didn't know that not declaring a length makes the length 1. When I gave a length of 11, it worked.

Collectives™ on Stack Overflow

Select statement not working in sql server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related