check for many combinations in sp

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)

check for many combinations in sp

Author

Topic

jn-at-uk
Starting Member

20 Posts

Posted - 2005-04-28 : 07:31:19

hi,

I need your advice on the best way to do this.
I am passing a whole lot of values to my stored proc. & based on what's passed i(0/1) i need to display the proper table.
the problem is i have 2 test for every combination.
There's too many combinations, is there a better way of testing for every combination excet the if else im using.

Eg.
@field1 = 1, @field2 = 1, @field3 = 0, @field4 = 1, @field5 = 1, @field6 = 0,@field7 = 1, @field8 = 1,
So @field3 = 0, @field6 = 0
That means i need to print the other fields

if @field1 = 1 and @field2 = 1 and @field3 = 0 and @field4 = 1 and @field5 = 1 and @field6 = 0 and @field7 = 1 and @field8 = 1
BEGIN
create Table #Final1(Param1 nvarchar(500), Param2 nvarchar(500), Param4 nvarchar(20),Param5 nvarchar(500), Param7 nvarchar(500), Param8 nvarchar(20) )
insert #Final19(Param1, Param2, Param4,Param5, Param6, Param8)
select pageref2,TrckTime,UserIP From #Temp group by pageref2,TrckTime,userip
select Param1 [Exit Page], Param2 [Time Spent], Param3 [User IP Addr],Param5,Param7,Param8 from #Final19
drop table #Final19
END

if @field1 = 1 and @field2 = 0 and @field3 = 1 and @field4 = 1 and @field5 = 1 and @field6 = 0 and @field7 = 1 and @field8 = 1
BEGIN
create Table #Final1(Param1 nvarchar(500), Param3 nvarchar(500), Param4 nvarchar(20),Param5 nvarchar(500), Param7 nvarchar(500), Param8 nvarchar(20) )
--insert
--select
drop table #Final19
END

if @field1 = 0 and @field2 = 1 and @field3 = 0 and @field4 = 1 and @field5 = 1 and @field6 = 0 and @field7 = 1 and @field8 = 1
BEGIN
create Table #Final1(Param2 nvarchar(500), Param2 nvarchar(500), Param4 nvarchar(20),Param5 nvarchar(500), Param7 nvarchar(500), Param8 nvarchar(20) )
--insert
--select
drop table #Final1
END

if @field1 = 1 and @field2 = 1 and @field3 = 0 and @field4 = 1 and @field5 = 1 and @field6 = 0 and @field7 = 1 and @field8 = 1
BEGIN
--insert
--select
END

if @field1 = 1 and @field2 = 1 and @field3 = 0 and @field4 = 1 and @field5 = 1 and @field6 = 0 and @field7 = 1 and @field8 = 1
BEGIN
--insert
--select
END

thanku

amachanic
SQL Server MVP

169 Posts

Posted - 2005-04-28 : 10:06:29

Sounds like a good case for dynamic SQL to me.

Check out these two articles:

http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/dyn-search.html

Also, why are you using temp tables? Why not just select the data?

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

jn-at-uk
Starting Member

20 Posts

Posted - 2005-04-28 : 12:07:09

thanku..dynamic sql is great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources