asked
SQL parameterized query not returning results
I am using MS SQl Server 2008 to test sql select queries. I am currently trying to use a paramaterized query to return records based on a user defined parameter. SELECT Company, Date_issued, Num_books, Officer_fname, Officer_lname, Officer_fname + ' ' + Officer_lname AS Officer_name, Officer_sig, Receipt_id, Recept_sig, Recofficer_fname, Recofficer_lname, Recofficer_fname + ' ' + Recofficer_lname AS Recofficer_name, Ref_num, Super_fname, Super_lname, Super_fname + ' ' + Super_lname AS Super_name, Super_sig FROM register WHERE (Ref_num = @value) OR (Company = @value) OR (Officer_fname = @value) OR (Officer_lname = @value) OR (Super_fname = @value) OR (Super_lname = @value) OR (Recofficer_fname = @value) OR (Recofficer_lname = @value) OR (Receipt_id = @value) OR (Officer_fname + ' ' + Officer_lname = @value) OR (Super_fname + ' ' + Super_lname = @value) OR (Recofficer_fname + ' ' + Recofficer_lname = @value) ORDER BY Date_issued DESC When @value is used, no results are returned. When it is replaced by a string from the database it returns the correct result. Can anyone please help with this?
That code is an example of what you are trying to do. It creates a stored procedure to select database [name] from system tables. It also appears you have answered your own question. > When values from Company or Recofficer_fname+' '+Recofficer_lname are used as @value no values are returned. The rest return the correct results. What are the values of that parameter if you print it?
I would suggest going through your code and make sure the select works are you are expecting then port to an SP. The code below should work. CREATE PROCEDURE [dbo].[testsp] @dbname NVARCHAR(50) AS SELECT * FROM sys.databases WHERE [name] = @dbname EXEC [dbo].[testsp] @dbname = N'master' GO
What does that code do exactly? Sorry I'm not very familiar with SQL. It seems only certain results are being returned based on the value given. When values from Company or Recofficer_fname+' '+Recofficer_lname are used as @value no values are returned. The rest return the correct results.
14 People are following this question.
