October 8, 2009 at 10:31 am
We have 2 types of customers. Automotive and Industrial. Automotive is compromised of Customer Classes MD, RD and RS. Industrial is compromised of Customer Classes ID, IW and JP. I am trying to set up a report where the user will pick Auto, Industrial or All. My problem is that when I set up the variable declarations, I cannot remember how to set up multiple values in a SET statement. Depending on the selection of the user, these variables will be pass down to a WHERE IN statement. I've tried different syntaxes, but I just cannot remember how this is done. Here is the gist of the declaration.
DECLARE @auto varchar(20)
DECLARE @industrial varchar(20)
SET @auto='MD','RD','RS'
SET @industrial='ID','IW','JP'
When the above is executed, I'll get a syntax error with the first comma. I've also tried SET @auto='MD'+','+'RD'+','RS' , but I'll get 0 results when used in the WHERE CustomerClass IN (@auto)
Any ideas?
October 8, 2009 at 10:48 am
How about this: SET @auto='MD,RD,RS'
October 8, 2009 at 11:09 am
You will have to use Dynamic SQL.
October 8, 2009 at 11:28 am
dsdeming (10/8/2009)
How about this: SET @auto='MD,RD,RS'
I've tried that, but what it does is treat everything inside the ' ' as 1 value of MD,RD,RS instead of the 3 separate ones. In the IN statement, I have to have each value inside the single quote, i.e. 'MD','RD','RS'
October 8, 2009 at 12:58 pm
I wound up using a couple of IF Else statements.
IF @type='Auto'
BEGIN
Select *
From Table
Where Class in ('RD','MD','RS')
END
IF @type='Industrial'
BEGIN
Select *
From Table
Where Class in ('ID','IW','JP')
END
ELSE
BEGIN
Select *
From Table
END
October 8, 2009 at 2:48 pm
adams.squared (10/8/2009)
I wound up using a couple of IF Else statements.IF @type='Auto'
BEGIN
Select *
From Table
Where Class in ('RD','MD','RS')
END
IF @type='Industrial'
BEGIN
Select *
From Table
Where Class in ('ID','IW','JP')
END
ELSE
BEGIN
Select *
From Table
END
Well there's always something like this.
CREATE TABLE @classes (classcode char(2),classtype varchar(20))
INSERT INTO @classes
SELECT
'RD','Auto'
UNION ALL
SELECT
'MD','Auto'
UNION ALL
SELECT
'RS','Auto'
UNION ALL
SELECT
'ID','Industrial'
UNION ALL
SELECT
'IW','Industrial'
UNION ALL
SELECT
'JP','Industrial'
SELECT
a.*
FROM
table a JOIN @classes b ON
a.Class = b.classcode
WHERE
b.classtype = @type
October 9, 2009 at 1:04 am
One more solution in Single query.
Select * from table_1 where [Class] in
(case when @type='Auto' then 'MD' else 'ID' end,
case when @type='Auto' then 'RD' else 'IW' end,
case when @type='Auto' then 'RS' else 'JP' end
)
but for performace your solution of 'IF ELSE' itself would be better it seems as it is less complex
October 9, 2009 at 2:26 am
Regarding Mark's solution, what would (IMHO) be better is if your application already had this table defined. Those codes are coming from someplace, just connect the ClassCode to the ClassType in this table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 9, 2009 at 3:51 am
Use Dynamic query...
DECLARE @auto varchar(20)
DECLARE @industrial varchar(20),@SQL varchar(500)
SET @auto='MD'','+'''RD'
SET @industrial='ID'','+'''IW'
Set @SQL='select * from Customer where CustomerClass in ('''+@ind+''')'
Exec (@SQL)
October 9, 2009 at 3:52 am
Listen to Wayne.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
October 9, 2009 at 7:06 am
WayneS (10/9/2009)
Regarding Mark's solution, what would (IMHO) be better is if your application already had this table defined. Those codes are coming from someplace, just connect the ClassCode to the ClassType in this table.
I completely agree... I was surprised that such a table wasn't mentioned...
But I'm also getting used to the stuff I have around here... and there is the twisted possibility that the table with those codes related to types does not exist. I know here there are several code type fields that have no relating table that categorizes the codes (or the table was dropped, and nobody ever declared a foreign key).
October 9, 2009 at 7:36 am
The MD, RD, ID, etc are coming from the Customer Table. They are the Class of Trade codes that we use. There isn't a table that says that MD is Automotive, so I am left with defining what belongs where within the script. I could always create another table, use up one of the User Defined fields in the application table, or use the temp table method as suggested. There are lots of ways to skin this cat. If the request for this becomes more common in the future, then I will probably use one of the user defined fields. Otherwise, I'll just leave it as a script.
Thanks for all of the responses.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply