CODEWITHSUNDEEP
sqlsql-serverprocedure
Temple Naylor
Temple Naylor

Reputation: 39

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.

Upvotes: 1

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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)

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93704

Remove the single quotes around the parameter. It will make the parameter as string literal. Though the parameters are string type, the parameters does not require single quotes when they are evaluated 

where AuthorName=@AuthorName and BranchName=@BranchName

Upvotes: 2

Related Questions