Dynamic SP with CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SP with CASE

Author

Topic

TGarmon
Starting Member

18 Posts

Posted - 2002-06-12 : 14:18:00

I am having trouble with this SP. The problem I have is where the case statement beings. I keep getting an error about the End statement. Thanks for your help.

CREATE PROCEDURE [qpd_searchShopperProfile]
/*
Created by: John Garmon / Megan Umphress
Purpose: To Search ShopperProfile Table
For: SS Reports
*/

@race int =null,
@gender int =null,
@age char(2)=null,
@shopperid varchar(6)=null,
@criteria char(1)=null,
@orderby nvarchar(14)='b.sp_shopperID'
AS
DECLARE @SQLString nvarchar(500)

/* OR Criteria */
if (@criteria = 1)
BEGIN

set @SQLString = 'Select b.sp_shopperID as ShopperID,a.Race as ShopperRace,c.Gender as ShopperGender, b.sp_Age as ShopperAge
From ShopperProfiles b
LEFT OUTER JOIN Race a ON a.Raceid = b.SP_Race
LEFT OUTER JOIN Gender c ON c.Genderid = b.SP_Gender
WHERE c.genderid= '+case when @gender=1 then+' c.genderid '+ else @gender end

exec sp_executeSQL @SQLString
END

/*
WHERE c.genderid= '+case when @gender='+''+'then +' c.genderid '+ else @gender end +'
or a.raceid = case when '+ @race+' = '+''+ 'then a.raceid else '+@race+ ' end
*/

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-06-12 : 15:08:42

Select b.sp_shopperID as ShopperID,a.Race as ShopperRace,c.Gender as ShopperGender, b.sp_Age as ShopperAge
From ShopperProfiles b
LEFT OUTER JOIN Race a ON a.Raceid = b.SP_Race
LEFT OUTER JOIN Gender c ON c.Genderid = b.SP_Gender
WHERE c.genderid =
case when @gender = 1 then c.genderid
else @gender end

As I see it, there is no need for dynamic sql. Also, it is a case expression not a case statement.

TGarmon
Starting Member

18 Posts

Posted - 2002-06-12 : 16:00:49

You are correct, but I forgot to include the code for a order by statement. That is what will be dynamic and I was having trouble with the case clause. Any ideas? Thanks so much!

set @SQLString = 'Select b.sp_shopperID as ShopperID,a.Race as ShopperRace,c.Gender as ShopperGender, b.sp_Age as ShopperAge
From ShopperProfiles b
LEFT OUTER JOIN Race a ON a.Raceid = b.SP_Race
LEFT OUTER JOIN Gender c ON c.Genderid = b.SP_Gender
WHERE c.genderid= '+case when @gender=1 then+' c.genderid '+ else @gender end'
Order By'+@orderby

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-06-12 : 17:23:01

I see

set @SQLString = 'Select b.sp_shopperID as ShopperID,a.Race as ShopperRace,c.Gender as ShopperGender, b.sp_Age as ShopperAge
From ShopperProfiles b
LEFT OUTER JOIN Race a ON a.Raceid = b.SP_Race
LEFT OUTER JOIN Gender c ON c.Genderid = b.SP_Gender
WHERE c.genderid= ' + case when @gender=1 then ' c.genderid ' else cast(@gender as varchar(11)) end +
'Order By '+@orderby

TGarmon
Starting Member

18 Posts

Posted - 2002-06-13 : 09:07:33

Thank you so much. That worked! One more question... Why did you have to use the cast statement with @gender?

,
John

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-06-13 : 12:06:56

The cast expression is just to make sure that all expressions in the when clauses are of the same datatype.

TGarmon
Starting Member

18 Posts

Posted - 2002-06-14 : 14:59:47

One more question for you.

I have tried several ways to make the predicate statement below work with a wildcard(%), but I cannot seem to get it right. Any ideas?

declare @SQLSTRING nvarchar(500)
declare @shopperID varchar(15)
declare @orderby varchar(25)
set @orderby= 'b.sp_race'
set @shopperID = '499075'

set @SQLString = 'Select b.sp_shopperID as ShopperID,a.Race as ShopperRace,c.Gender as ShopperGender, b.sp_Age as ShopperAge
From ShopperProfiles b
LEFT OUTER JOIN Race a ON a.Raceid = b.SP_Race
LEFT OUTER JOIN Gender c ON c.Genderid = b.SP_Gender
WHERE b.SP_shopperID like ('+cast(@shopperid as varchar(6))+'%'+')
Order By '+@orderby
exec sp_executeSQL @SQLSTRING

Edited by - TGarmon on 06/14/2002 15:00:47

Edited by - TGarmon on 06/14/2002 15:01:13

dsdeming

479 Posts

Posted - 2002-06-15 : 09:55:17

I think you need a couple more quotation marks so that your LIKE statement looks like 'idno%' when it's executed.

set @SQLString = 'Select b.sp_shopperID as ShopperID,a.Race as ShopperRace,c.Gender as ShopperGender, b.sp_Age as ShopperAge
From ShopperProfiles b
LEFT OUTER JOIN Race a ON a.Raceid = b.SP_Race
LEFT OUTER JOIN Gender c ON c.Genderid = b.SP_Gender
WHERE b.SP_shopperID like ('''+cast(@shopperid as varchar(6))+'%'+''')
Order By '+@orderby
exec sp_executeSQL @SQLSTRING

TGarmon
Starting Member

18 Posts

Posted - 2002-06-17 : 10:04:10

Thank you for your help! The following worked for me.

WHERE b.SP_shopperID like ('''+cast(@shopperID as varchar(6))+'%'')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources