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