0

I am facing problem in a simple query script for SQL server. Please help

The script is

CREATE PROCEDURE [dbo].[Search_By_Author_And_Name] 
(
    @Author_name AS [varchar](50)
)
AS
BEGIN
    Select B.*, R.R_number From Book AS B , Rack As R
    where B.Author LIKE CONCAT('%', @Author_name, '%')
    AND B.B_Id=R.B_Id
END 
GO

The problem is this returns no columns. Even if I pass a valid argument(@Author_name) to the procedure it returns nothing. There must be a problem in the where clause in the usage of LIKE.

sample data in the table are

enter image description here

the first table is Book table and the other table is Rack table

I am calling the procedure by

EXEC Search_By_Author_And_Name 'Physics'

Please help

Thanks in advance

10
  • Are there any rows in Rack? – Chad Mattox Oct 27 '16 at 18:06
  • Yes there are rows in Rack – Debesh Mohanty Oct 27 '16 at 18:09
  • What version of SQL? I am running this same scenario on 2014, and it works as expected. – Chad Mattox Oct 27 '16 at 18:13
  • Why not just have Like '%' + @Author_name + '%'. Also, use JOIN to join the 2 tables and not the implied join "," – SS_DBA Oct 27 '16 at 18:15
  • @WEI_DBA that dosent work either – Debesh Mohanty Oct 27 '16 at 18:19
2

You are only matching on Author, not B_Name. Change code to:

CREATE PROCEDURE [dbo].[Search_By_Author_And_Name] 
(
    @Author_name AS [varchar](50)
)
AS
BEGIN
    Select B.*, R.R_number From Book AS B , Rack As R
    where (B.Author LIKE CONCAT('%', @Author_name, '%') OR
    B.B_Name LIKE CONCAT('%', @Author_name, '%'))
    AND B.B_Id=R.B_Id
END 
GO
4
  • Ya i changed that..bt still it dosent work – Debesh Mohanty Oct 27 '16 at 18:34
  • You are matching on Author, not B_NAME – Chad Mattox Oct 27 '16 at 18:35
  • pass in the value 'hc' – Chad Mattox Oct 27 '16 at 18:36
  • Needs parentheses around the OR conditions. – ypercubeᵀᴹ Oct 27 '16 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.