Temple Naylor Temple Naylor - 1 month ago 8
SQL Question

Parameters in Procedure not working

I created the following query:

SELECT Title, sum(No_Of_Copies) as 'Total Copies'
FROM BOOK_COPIES bc
join BOOK_AUTHORS ba
on bc.BookId=ba.BookId
join BOOK b
on b.BookId=bc.BookId
join LIBRARY_BRANCH lb
on bc.BranchId=lb.BranchId where AuthorName='George Orwell' and BranchName='Central'
group by b.Title


From this query I wanted to create a procedure that could search for the amount of book copies of any Author or Branch name, so I created this:

CREATE PROC GetTotalCopies @AuthorName varchar(100), @BranchName varchar(100)
AS

SELECT Title, sum(No_Of_Copies) as 'Total Copies'
FROM BOOK_COPIES bc
join BOOK_AUTHORS ba
on bc.BookId=ba.BookId
join BOOK b
on b.BookId=bc.BookId
join LIBRARY_BRANCH lb
on bc.BranchId=lb.BranchId where AuthorName='@AuthorName' and BranchName='@BranchName'
group by b.Title


But when I run it with a Author Name and Branch Name I get empty tables. Anyone know why?
I created this database from the following flowchart:
https://www.learncodinganywhere.com/learningmanagementsystem/links/07_DB/SQL_Drill.pdf
using SQL Server 2008.

Answer

No need for single quotes. The arguments are already strings.

So, write the query like this:

where AuthorName = @AuthorName and BranchName = @BranchName

Note: You might want to consider arguments that function as "all values":

where (AuthorName = @AuthorName or @AuthorName is null) and
      (BranchName = @BranchName or @BranchName is null)