CODEWITHSUNDEEP
sqlsql-servert-sqlcase
vbneil54
vbneil54

Reputation: 103

Assigne a string to a variable in a case statement in the THEN clause

Here's what I have. I have tried quotes instead of =, I have removed the variable and tried just the select statement wrapped in quotes. Loosing my mind trying to figure this out. Yes, it is homework, I'm not asking for you to write it for me, just let me know why I can't seem to get it to work and what I'm doing wrong. The Case is about 7 Whens long, But I can't get passed the first one. Please HELP. I know people don't like doing homework, so think of this as teaching me instead. Thank you, 

CREATE PROC usr_spChooseReport(@ReportNum int)

AS
declare @sqlString nvarchar(500)
SELECT CASE(@ReportNum) 

WHEN 1 THEN @sqlString =''select book.Title, Book_Copies.No_of_Copies,  Library_Branch.BranchName from Book inner join Book_Copies on book.BookID=Book_Copies.BookID inner join Library_Branch on Book_Copies.BranchID=Library_Branch.BranchID where ONVERT(NVARCHAR(MAX),Library_Branch.BranchName) = 'Sharptown' 
and CONVERT(NVARCHAR(MAX), Book.Title) ='The Lost Tribe'''

                  ELSE 'Unknown'  

END

But if I try this,

CREATE PROC usr_spChooseReport(@ReportNum int)
AS

SELECT CASE(@ReportNum) 

WHEN 1 THEN (select book.Title, Book_Copies.No_of_Copies, Library_Branch.BranchName from Book inner join Book_Copies on book.BookID=Book_Copies.BookID inner join Library_Branch on Book_Copies.BranchID=Library_Branch.BranchID where CONVERT(NVARCHAR(MAX),Library_Branch.BranchName) = 'Sharptown' 
and CONVERT(NVARCHAR(MAX), Book.Title) ='The Lost Tribe')

                  ELSE 'Unknown'  

END

I get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 2

Views: 1451

Answers (2)

Stephan
Stephan

Reputation: 6018

First off, this is a funky problem. If you follow this pattern, will you have a completely different query for each of the 7 numbers?

I would hope it's all the same query with simply different parameters passed. Then I would just create a lookup table like this:

DECLARE @LookUpTable TABLE (ID INT PRIMARY KEY, LibraryBranch VARCHAR(25), Book_Name VARCHAR(25))
INSERT INTO @LookUpTable
VALUES  (1,'Sharptown','The Lost Tribe'),
        (2,'Other Branch','Other book');

DECLARE @ReportNum INT = 1;

select book.Title, Book_Copies.No_of_Copies,  Library_Branch.BranchName 
FROM Book 
inner join Book_Copies 
    on book.BookID=Book_Copies.BookID 
inner join Library_Branch 
    on Book_Copies.BranchID=Library_Branch.BranchID 
inner join @LookUpTable LT
    on Library_Branch.BranchName = LT.LibraryBranch
        AND Book.Title = LT.Book_Name
WHERE LT.ID = @ReportNum

BUT if you actually do have different queries for each case, then either try Jchao's method and EXEC(@sqlString) at the end OR you could use if statements. Note: ELSE IF means it will run only one of the queries so once it finds a match it will run that one query and then be done, but if NO match is found then the ELSE at the end will run

DECLARE @ReportNum INT = 1;

IF (@ReportNum = 1)
BEGIN
    SELECT 1 --query 1
END
ELSE IF (@ReportNum = 2)
BEGIN
    SELECT 2 --query 2
END
--etc to 7
ELSE
BEGIN
    SELECT 'Unknown' --query 8 for unknown
END

Upvotes: 1

SQLChao
SQLChao

Reputation: 7837

After reading your question a second time I think I understand. They want you to use a case statement to set a variable @sqlString to a string which is the select statement. Then at some point they will EXEC (@sqlString)

You need to follow your first example for the remaining cases. 

WHEN 2 THEN @sqlString = 'SELECT * FROM Somewhere'
WHEN 3 THEN @sqlString = 'SELECT * FROM SomewhereElse'

The difference between your first example and the second is that in the first they are assigning a string value to a variable @sqlString. In your second example you are running a query that returns multiple expressions. Two totally different things.

Upvotes: 2

Related Questions