A better way? - 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 2008 Forums

Transact-SQL (2008)

A better way?

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 11:12:00

I have to allow 4 input parameters, but only 1 can be valued at any 1 time. If they try and pass more than 1, I will return an error, I have a solution, but is there a better way?



IF  ((CASE WHEN @Bu_Unit IS NOT NULL 
		    AND @Bu_Unit		<> ' ' 
		    AND @Bu_Unit		<> ''		THEN 1 ELSE 0 END)
	+
	 (CASE WHEN @BC_Plan IS NOT NULL 
		    AND @BC_Plan		<> ' ' 
		    AND @BC_Plan		<> ''		THEN 1 ELSE 0 END)
	+
	 (CASE WHEN @Bus_Function IS NOT NULL 
		    AND @Bus_Function	<> ' ' 
		    AND @Bus_Function	<> ''		THEN 1 ELSE 0 END)
	+
	 (CASE WHEN @Appl_Name IS NOT NULL 
		    AND @Appl_Name		<> ' ' 
		    AND @Appl_Name		<> ''		THEN 1 ELSE 0 END)
	) > 1
	BEGIN 
              SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1 
               set @Error_Message = 'Only 1 Parameter is allowed to be passed'
               GOTO usp_SEL_report5_infra_appl_Err  
	END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 11:32:22

declare @Bu_Unit varchar(10) = 'asd' ,
@BC_Plan varchar(10) = '' ,
@Bus_Function varchar(10) = '' ,
@Appl_Name varchar(10) = 'x'

select 1 where
'|'+RTRIM(coalesce(@Bu_Unit,''))+'|'+RTRIM(coalesce(@BC_Plan,''))+'|'+RTRIM(coalesce(@Bus_Function,''))+'|'+RTRIM(coalesce(@Appl_Name,''))+'|' like '%|[^|]%|[^|]%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-26 : 11:35:49

Using the same idea, and probably a little bit slower than yours, but slightly more compact

IF
(
   CASE WHEN NULLIF(LTRIM(@Bu_Unit),'')      IS NOT NULL THEN 1 ELSE 0 END +
   CASE WHEN NULLIF(LTRIM(@BC_Plan),'')      IS NOT NULL THEN 1 ELSE 0 END +
   CASE WHEN NULLIF(LTRIM(@Bus_Function),'') IS NOT NULL THEN 1 ELSE 0 END +
   CASE WHEN NULLIF(LTRIM(@Appl_Name),'')    IS NOT NULL THEN 1 ELSE 0 END > 1
)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-26 : 11:42:05

not sure about the '' or ' '



IF COALESCE(@bu_unit,@bc_plan,@bus_Function,@appl_name)<>COALESCE(@bc_plan,@bus_Function,@bu_unit,@appl_name)
SELECT 1 ELSE SELECT  0

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-26 : 11:43:05

A slight variation on sunita's idea:

IF (SELECT COUNT(*) FROM (
SELECT NULLIF(@Bu_Unit),'') a UNION ALL
SELECT NULLIF(@BC_Plan,'') UNION ALL
SELECT NULLIF(@Bus_Function,'') UNION ALL
SELECT NULLIF(@Appl_Name,'')) a) > 1
PRINT 'too many'

You don't have to trim a string that only contains spaces.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-26 : 12:06:35

quote:
Originally posted by robvolk
You don't have to trim a string that only contains spaces.

I always thought the following code will return 0, but now I know better.

DECLARE @x VARCHAR(32);
SET @x = SPACE(10);
SELECT CASE WHEN NULLIF(@x,'') IS NULL THEN 1 ELSE 0 END

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 12:26:26

my bad sql2k5

Thanks everyone...

I always have to stop and think about Nigel's code


declare @Bu_Unit varchar(10),@BC_Plan varchar(10),@Bus_Function varchar(10),@Appl_Name varchar(10)
SELECT @Bu_Unit= 'x' ,@BC_Plan= '' ,@Bus_Function= '' ,@Appl_Name= 'x'

select 1,  '|'+RTRIM(coalesce(@Bu_Unit,''))
+'|'+RTRIM(coalesce(@BC_Plan,''))
+'|'+RTRIM(coalesce(@Bus_Function,''))
+'|'+RTRIM(coalesce(@Appl_Name,''))
+'|' 
where 
 '|'+RTRIM(coalesce(@Bu_Unit,''))
+'|'+RTRIM(coalesce(@BC_Plan,''))
+'|'+RTRIM(coalesce(@Bus_Function,''))
+'|'+RTRIM(coalesce(@Appl_Name,''))
+'|' 
like '%|[^|]%|[^|]%'

Like, what is happening here


like '%|[^|]%|[^|]%'

EDIT: I mean it works like a champ, and I know it must be looking for something like this ||||x| with only 1 non empty string between |'s, but it's not positional..I'm also guess that ^ is an escape char?

In any case...I like it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 12:41:16

^ means not
so it's looking for two occurances of not ||.

You didn't say what you meant by "better" so ....

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-26 : 12:46:15