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)
 Funky Where Clause (not typical dynamic)

Author  Topic 

Hannibal
Starting Member

20 Posts
Posted - 2002-11-22 : 17:50:30
First, I want to say thanks to everyone on this forum who has posted comments about dynamic where/order by clauses that don't use exec(@string), the info has been very helpful.

Unfortunately, I had an article bookmarked and lost it ... now I am unable to find it. It involved using coalesce and case statements to create a dynamic where clause, and I believe there was a post that pertains to my current situation. Since I cannot find the article, I'm going to post my question and hopefully it's not duplicated. :)

This is my situation. I am retrieving a list of clients and their balances. The balances must be calculated using a stored proc, and I am unaware of how to implement this into a normal select statement. My solution involves an initial select statement with a dynamic where clause (using coalesce) that places the recordset into a table variable. Once I have this table variable, I loop through and call the client_balance stored proc and update the table variable with my various balances (deposit_balance, withdraw_balance, total_balance). Not pretty, but it works (suggestions welcome if there's a better method!).

Now that I have my FULL recordset, I need to reduce it again based on arguments related to the balance. This is where it gets tricky ... the user selects what type of balance they want to search on via a drop down (deposit, withdraw, total) and then enters a range of values. If they select "deposit_balance" with 100-1000 as the range, I want to return clients who have a deposit balance between 100 and 1000 and ignore the balance on the other fields.

I hope this all makes sense. I know it's possible some how, but I've been staring at it for too long and need some outside input. Again, any suggestions on the general mess are very welcome too!

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-11-22 : 18:00:47
I think this is the article you mentioned:

http://www.sqlteam.com/item.asp?ItemID=2077

If not, do a search on SQL Team for "dynamic WHERE" and you'll get pretty much all of them.

As far as whether or not the balance calculations can be written as a regular SELECT, the only way to know is to post the code of your stored procedure, and the structures of the tables involved in that procedure. Please post the actual CREATE TABLE and CREATE PROCEDURE statements, and also provide some sample data and the output that would be generated by the procedure. 10-20 rows is fine, just enough to give us an idea. And don't feel you need to abbreviate long code, if you leave something out it will only make it harder for us to help.
Go to Top of Page

Hannibal
Starting Member

20 Posts
Posted - 2002-11-22 : 18:09:11
That's the article I find now when I search, but the original one I referred to had different comments and it was a comment that might have the solution I'm looking for. Then again, maybe not. I just seem to remember something similar to this when I was reading it awhile back.

Anyway, I'm going to organize the stored procs and the output and see if I can get it posted within the next hour. I appreciate the help. :)
Go to Top of Page

Hannibal
Starting Member

20 Posts
Posted - 2002-11-22 : 18:41:26
I changed my post to include the complete create table statements for the tables involved (let me know if I missed one). Feel free to rip the code apart, it was done quickly and I haven't optimized, but I'm open to any suggestions. Once again, thanks for the help!

***********
CREATE TABLE [client] (
[client_id] [int] IDENTITY (10001, 1) NOT NULL ,
[client_type_cd] [tinyint] NOT NULL ,
[client_status_type_cd] [tinyint] NOT NULL ,
[client_level_type_cd] [tinyint] NOT NULL ,
[chat_enabled] [bit] NOT NULL CONSTRAINT [DF_client_chat_enabled] DEFAULT (1),
[bonus_enabled] [bit] NOT NULL CONSTRAINT [DF_client_bonus_enabled] DEFAULT (1),
[alias] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[middle_name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[birth_date] [datetime] NOT NULL ,
[language_type_cd] [tinyint] NULL ,
[gender_type_cd] [tinyint] NULL ,
[password] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[maximum_credit_card] [tinyint] NOT NULL CONSTRAINT [DF_client_maximum_credit_card] DEFAULT (3),
[created_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[created_date] [datetime] NOT NULL ,
[modified_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modified_date] [datetime] NULL ,
[update_counter] [tinyint] NOT NULL ,
CONSTRAINT [PK_client] PRIMARY KEY CLUSTERED
(
[client_id]
) ON [PRIMARY] ,
CONSTRAINT [IX_client_alias] UNIQUE NONCLUSTERED
(
[alias]
) ON [PRIMARY] ,
CONSTRAINT [FK_client_client_level_type] FOREIGN KEY
(
[client_level_type_cd]
) REFERENCES [client_level_type] (
[client_level_type_cd]
),
CONSTRAINT [FK_client_client_status_type] FOREIGN KEY
(
[client_status_type_cd]
) REFERENCES [client_status_type] (
[client_status_type_cd]
),
CONSTRAINT [FK_client_client_type] FOREIGN KEY
(
[client_type_cd]
) REFERENCES [client_type] (
[client_type_cd]
),
CONSTRAINT [FK_client_gender_type] FOREIGN KEY
(
[gender_type_cd]
) REFERENCES [gender_type] (
[gender_type_cd]
),
CONSTRAINT [FK_client_language_type] FOREIGN KEY
(
[language_type_cd]
) REFERENCES [language_type] (
[language_type_cd]
)
) ON [PRIMARY]
GO

CREATE TABLE [client_address] (
[client_address_id] [int] IDENTITY (1, 1) NOT NULL ,
[client_id] [int] NOT NULL ,
[address_type_cd] [tinyint] NOT NULL ,
[address_1] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address_2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address_3] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[state] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[country_type_cd] [tinyint] NOT NULL ,
[zip_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[created_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_client_address_created_by] DEFAULT ('System'),
[created_date] [datetime] NOT NULL CONSTRAINT [DF_client_address_created_date] DEFAULT (getdate()),
[modified_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modified_date] [datetime] NULL ,
[update_counter] [tinyint] NOT NULL CONSTRAINT [DF_client_address_update_counter] DEFAULT (0),
CONSTRAINT [PK_client_address] PRIMARY KEY CLUSTERED
(
[client_address_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_client_address_address_type] FOREIGN KEY
(
[address_type_cd]
) REFERENCES [address_type] (
[address_type_cd]
),
CONSTRAINT [FK_client_address_client] FOREIGN KEY
(
[client_id]
) REFERENCES [client] (
[client_id]
),
CONSTRAINT [FK_client_address_country_type] FOREIGN KEY
(
[country_type_cd]
) REFERENCES [country_type] (
[country_type_cd]
)
) ON [PRIMARY]
GO

CREATE TABLE [client_email] (
[client_email_id] [int] IDENTITY (1, 1) NOT NULL ,
[client_id] [int] NOT NULL ,
[enabled] [bit] NOT NULL CONSTRAINT [DF_client_email_enabled] DEFAULT (1),
[email] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[created_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_client_email_created_by] DEFAULT ('System'),
[created_date] [datetime] NOT NULL CONSTRAINT [DF_client_email_created_date] DEFAULT (getdate()),
[modified_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modified_date] [datetime] NULL ,
[update_counter] [tinyint] NOT NULL CONSTRAINT [DF_client_email_update_counter] DEFAULT (0),
CONSTRAINT [PK_client_email] PRIMARY KEY CLUSTERED
(
[client_email_id]
) ON [PRIMARY] ,
CONSTRAINT [IX_client_email] UNIQUE NONCLUSTERED
(
[email]
) ON [PRIMARY] ,
CONSTRAINT [IX_client_email_email] UNIQUE NONCLUSTERED
(
[email]
) ON [PRIMARY] ,
CONSTRAINT [FK_client_email_client] FOREIGN KEY
(
[client_id]
) REFERENCES [client] (
[client_id]
)
) ON [PRIMARY]
GO

CREATE TABLE [client_transaction] (
[client_transaction_id] [int] IDENTITY (1, 1) NOT NULL ,
[client_id] [int] NOT NULL ,
[transaction_status_type_cd] [tinyint] NOT NULL ,
[transaction_type_cd] [tinyint] NOT NULL ,
[payment_method_type_cd] [tinyint] NULL ,
[account_type_cd] [tinyint] NOT NULL ,
[promotion_id] [int] NULL ,
[confirmation] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amount] [money] NOT NULL ,
[created_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_client_transaction_created_by] DEFAULT ('System'),
[created_date] [datetime] NOT NULL CONSTRAINT [DF_client_transaction_created_date] DEFAULT (getdate()),
[modified_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modified_date] [datetime] NULL ,
[update_counter] [tinyint] NOT NULL CONSTRAINT [DF_client_transaction_update_counter] DEFAULT (0),
CONSTRAINT [PK_client_transaction] PRIMARY KEY CLUSTERED
(
[client_transaction_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_client_transaction_client] FOREIGN KEY
(
[client_id]
) REFERENCES [client] (
[client_id]
)
) ON [PRIMARY]
GO

CREATE TABLE [client_session] (
[client_session_id] [int] IDENTITY (1, 1) NOT NULL ,
[client_id] [int] NOT NULL ,
[account_type_cd] [tinyint] NOT NULL ,
[session_status_type_cd] [tinyint] NOT NULL ,
[ip_address] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[created_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_client_session_created_by] DEFAULT ('System'),
[created_date] [datetime] NOT NULL CONSTRAINT [DF_client_session_created_date] DEFAULT (getdate()),
[modified_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modified_date] [datetime] NULL ,
[update_counter] [tinyint] NOT NULL CONSTRAINT [DF_client_session_update_counter] DEFAULT (0),
CONSTRAINT [PK_client_session] PRIMARY KEY CLUSTERED
(
[client_session_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_client_session_account_type] FOREIGN KEY
(
[account_type_cd]
) REFERENCES [account_type] (
[account_type_cd]
),
CONSTRAINT [FK_client_session_client] FOREIGN KEY
(
[client_id]
) REFERENCES [client] (
[client_id]
),
CONSTRAINT [FK_client_session_session_status_type] FOREIGN KEY
(
[session_status_type_cd]
) REFERENCES [session_status_type] (
[session_status_type_cd]
)
) ON [PRIMARY]
GO

CREATE TABLE [event] (
[event_id] [int] IDENTITY (1, 1) NOT NULL ,
[event_type_cd] [tinyint] NOT NULL ,
[client_session_id] [int] NULL ,
[game_id] [int] NULL ,
[software_id] [int] NULL ,
[description] [varchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amount] [money] NULL ,
[created_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_session_event_created_by] DEFAULT ('System'),
[created_date] [datetime] NOT NULL CONSTRAINT [DF_session_event_created_date] DEFAULT (getdate()),
[modified_by] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modified_date] [datetime] NULL ,
[update_counter] [tinyint] NOT NULL CONSTRAINT [DF_session_event_update_counter] DEFAULT (0),
CONSTRAINT [PK_session_event] PRIMARY KEY CLUSTERED
(
[event_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_event_client_session] FOREIGN KEY
(
[client_session_id]
) REFERENCES [client_session] (
[client_session_id]
),
CONSTRAINT [FK_event_event_type] FOREIGN KEY
(
[event_type_cd]
) REFERENCES [event_type] (
[event_type_cd]
),
CONSTRAINT [FK_event_software] FOREIGN KEY
(
[software_id]
) REFERENCES [software] (
[software_id]
)
) ON [PRIMARY]
GO

**********

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[client_balance]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[client_balance]
GO

CREATE PROCEDURE dbo.client_balance
@client_id int,
@account_type_cd int,
@total_balance money OUTPUT,
@available_balance money OUTPUT,
@deposit_balance money OUTPUT,
@withdraw_balance money OUTPUT,
@promotion_balance money OUTPUT,
@adjust_bank_balance money OUTPUT,
@adjust_comp_balance money OUTPUT,
@wager_balance money OUTPUT,
@win_balance money OUTPUT
as set nocount on

select distinct
@total_balance = (isnull(ct1.deposit,0) - isnull(ct2.withdraw,0) + isnull(ct3.promotion,0) + isnull(ct4.adjust_bank,0) + isnull(ct5.adjust_comp,0) - isnull(se1.wager,0) - isnull(se2.result,0)),
@available_balance = (isnull(ct1.deposit,0) - isnull(ct2.withdraw,0) + isnull(ct4.adjust_bank,0) - isnull(se1.wager,0) - isnull(se2.result,0)),
@deposit_balance = isnull(ct1.deposit,0),
@withdraw_balance = isnull(ct2.withdraw,0),
@promotion_balance = isnull(ct3.promotion,0),
@adjust_bank_balance = isnull(ct4.adjust_bank,0),
@adjust_comp_balance = isnull(ct5.adjust_comp,0),
@wager_balance = isnull(se1.wager,0),
@win_balance = isnull(se2.result,0)

from client

-- find the deposits
left outer join (select sum(amount) as deposit, client_id from client_transaction where transaction_type_cd = 1 and account_type_cd = @account_type_cd group by client_id) ct1
on ct1.client_id = client.client_id

-- find the withdraws
left outer join (select sum(amount) as withdraw, client_id from client_transaction where transaction_type_cd = 2 and account_type_cd = @account_type_cd group by client_id) ct2
on ct2.client_id = client.client_id

-- find the promotions
left outer join (select sum(amount) as promotion, client_id from client_transaction where transaction_type_cd = 3 and account_type_cd = @account_type_cd group by client_id) ct3
on ct3.client_id = client.client_id

-- find the adjust bank
left outer join (select sum(amount) as adjust_bank, client_id from client_transaction where transaction_type_cd = 4 and account_type_cd = @account_type_cd group by client_id) ct4
on ct4.client_id = client.client_id

-- find the adjust comp
left outer join (select sum(amount) as adjust_comp, client_id from client_transaction where transaction_type_cd = 5 and account_type_cd = @account_type_cd group by client_id) ct5
on ct4.client_id = client.client_id

-- find the events that cost money
left outer join (select sum(amount) as wager, software_id, client.client_id
from event, client_session, client
where event.client_session_id = client_session.client_session_id
and client_session.client_id = client.client_id
and client_session.account_type_cd = @account_type_cd
and event_type_cd in (3,4,5,7,8,9,10,15)
group by software_id, client.client_id) se1
on se1.client_id = client.client_id

-- find the win events
left outer join (select sum(amount) as result, software_id, client.client_id
from event, client_session, client
where event.client_session_id = client_session.client_session_id
and client_session.client_id = client.client_id
and client_session.account_type_cd = @account_type_cd
and event_type_cd = 13
group by software_id, client.client_id) se2
on se2.client_id = client.client_id
where client.client_id = @client_id

**********

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gms_select_client]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[gms_select_client]
GO

CREATE PROCEDURE dbo.gms_select_client
@client_id int,
@alias varchar(32),
@first_name varchar(32),
@last_name varchar(32),
@email varchar(32),
@order_by tinyint,
@order_direction tinyint
as set nocount on

declare @client_id_temp int,
@total_balance money,
@available_balance money,
@deposit_balance money,
@withdraw_balance money,
@promotion_balance money,
@adjust_bank_balance money,
@adjust_comp_balance money,
@wager_balance money,
@win_balance money

declare @table table
(client_id int primary key,
client_type_cd tinyint,
client_status_type_cd tinyint,
client_level_type_cd tinyint,
alias varchar(32),
first_name varchar(32),
last_name varchar(32),
middle_name varchar(32),
birth_date datetime,
language_type_cd tinyint,
gender_type_cd tinyint,
password varchar(16),
created_by varchar(32),
created_date datetime,
modified_by varchar(32),
modified_date datetime,
client_update_counter tinyint,
address_1 varchar(32),
address_2 varchar(32),
address_3 varchar(32),
city varchar(32),
state varchar(32),
country_type_cd tinyint,
zip_code varchar(16),
address_update_counter tinyint,
email varchar(32),
email_enabled bit,
email_update_counter tinyint,
last_login datetime,
last_transaction_amount money,
total_balance money,
available_balance money,
deposit_balance money,
withdraw_balance money,
promotion_balance money,
adjust_bank_balance money,
adjust_comp_balance money,
wager_balance money,
win_balance money)

-- this will set arguments to null so coalesce will work.
if @client_id = 0 set @client_id = null
if @alias = '' set @alias = null
if @first_name = '' set @first_name = null
if @last_name = '' set @last_name = null
if @email = '' set @email = null

insert into @table
select
client.client_id,
client.client_type_cd,
client.client_status_type_cd,
client.client_level_type_cd,
client.alias,
client.first_name,
client.last_name,
client.middle_name,
client.birth_date,
client.language_type_cd,
client.gender_type_cd,
client.password,
client.created_by,
client.created_date,
client.modified_by,
client.modified_date,
client.update_counter as "client_update_counter",
client_address.address_1,
client_address.address_2,
client_address.address_3,
client_address.city,
client_address.state,
client_address.country_type_cd,
client_address.zip_code,
client_address.update_counter as "address_update_counter",
client_email.email,
client_email.enabled as "email_enabled",
client_email.update_counter as "email_update_counter",
client_session.created_date as "last_login",
client_transaction.amount as "last_transaction_amount",
null as "total_balance",
null as "available_balance",
null as "deposit_balance",
null as "withdraw_balance",
null as "promotion_balance",
null as "adjust_bank_balance",
null as "adjust_comp_balance",
null as "wager_balance",
null as "win_balance"
from client
join client_address
on client_address.client_id = client.client_id
join client_email
on client_email.client_id = client.client_id
and client_email.email like '%' + coalesce(@email, client_email.email) + '%'
left outer join client_session
on client_session.client_id = client.client_id
and client_session.created_date = (select max(created_date) from client_session where client_id = client.client_id)
left outer join client_transaction
on client_transaction.client_id = client.client_id
and client_transaction.created_date = (select max(created_date) from client_transaction where client_id = client.client_id)
where client.client_id = coalesce(@client_id, client.client_id)
and client.alias like '%' + coalesce(@alias, client.alias) + '%'
and client.first_name like '%' + coalesce(@first_name, client.first_name) + '%'
and client.last_name like '%' + coalesce(@last_name, client.last_name) + '%'

declare client_cursor cursor for
select client_id from @table
open client_cursor
fetch next from client_cursor into @client_id_temp
while @@fetch_status = 0 begin
exec dbo.client_balance @client_id_temp, 2, @total_balance OUTPUT, @available_balance OUTPUT, @deposit_balance OUTPUT, @withdraw_balance OUTPUT, @promotion_balance OUTPUT, @adjust_bank_balance OUTPUT, @adjust_comp_balance OUTPUT, @wager_balance OUTPUT, @win_balance OUTPUT

update @table set
total_balance = @total_balance,
available_balance = @available_balance,
deposit_balance = @deposit_balance,
withdraw_balance = @withdraw_balance,
promotion_balance = @promotion_balance,
adjust_bank_balance = @adjust_bank_balance,
adjust_comp_balance = @adjust_comp_balance,
wager_balance = @wager_balance,
win_balance = @win_balance
where client_id = @client_id_temp

fetch next from client_cursor into @client_id_temp
end

select * from @table
order by
case
when @order_by = 1 and @order_direction = 1 then cast(client_id as varchar)
when @order_by = 2 and @order_direction = 1 then alias
when @order_by = 3 and @order_direction = 1 then cast(last_name + ', ' + first_name as varchar)
when @order_by = 4 and @order_direction = 1 then email
when @order_by = 5 and @order_direction = 1 then cast(client_level_type_cd as varchar)
end asc,
case
when @order_by = 6 and @order_direction = 1 then total_balance
end asc,
case
when @order_by = 1 and @order_direction = 2 then cast(client_id as varchar)
when @order_by = 2 and @order_direction = 2 then alias
when @order_by = 3 and @order_direction = 2 then cast(last_name + ', ' + first_name as varchar)
when @order_by = 4 and @order_direction = 2 then email
when @order_by = 5 and @order_direction = 2 then cast(client_level_type_cd as varchar)
end desc,
case
when @order_by = 6 and @order_direction = 2 then total_balance
end desc

***************
desired result set from gms_select_client:

client_id, first_name, last_name, deposit_balance, withdraw_balance, total_balance

I'd like to avoid putting the code from client_balance into gms_select_client ... and I also need to apply a where clause for the balance stuff (as described in the first post).

I hope this is enough information to make suggestions ... if not, let me know what I'm missing. :)

Edited by - hannibal on 11/22/2002 19:20:55

Edited by - hannibal on 11/22/2002 19:43:17
Go to Top of Page

Hannibal
Starting Member

20 Posts
Posted - 2002-11-23 : 12:04:38
robvolk, this is the article I was referring to:

http://www.sqlteam.com/forums/topic.asp?whichpage=2&ARCHIVEVIEW=&TOPIC_ID=5942

... and it's your post that I remembered (it starts with "If you don't mind the extra column"). Unfortunately, when I tried it, I get the "invalid column" column error. Here is the code, the error happens on the where clause. I'm pretty sure this would work, though. :)

select *,
case
when @balance_type = 1 then total_balance
end as search_balance
from @table
where search_balance between 100 and 1000

BTW - if I comment the where clause and add "order by search_balance" it works fine ... am I unable to reference the alias in a where clause?

Edited by - hannibal on 11/23/2002 12:05:51
Go to Top of Page

Hannibal
Starting Member

20 Posts
Posted - 2002-11-23 : 12:32:43
Sorry for the multi posting ... here is a modified version of gms_select_client that produces the proper result set. This stored proc is used for 4 very similar, but different reports. I didn't want to create 4 stored procs ... so it's a bit messy. Suggestions on how to clean this up would be GREATLY appreciated.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gms_select_client]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[gms_select_client]
GO

CREATE PROCEDURE dbo.gms_select_client
@client_id int,
@alias varchar(32),
@first_name varchar(32),
@last_name varchar(32),
@email varchar(32),
@balance_type tinyint,
@balance_from money,
@balance_to money,
@order_by tinyint,
@order_direction tinyint
as set nocount on

declare @client_id_temp int,
@total_balance money,
@available_balance money,
@deposit_balance money,
@withdraw_balance money,
@promotion_balance money,
@adjust_bank_balance money,
@adjust_comp_balance money,
@wager_balance money,
@win_balance money

declare @table table
(client_id int primary key,
client_type_cd tinyint,
client_status_type_cd tinyint,
client_level_type_cd tinyint,
alias varchar(32),
first_name varchar(32),
last_name varchar(32),
middle_name varchar(32),
birth_date datetime,
language_type_cd tinyint,
gender_type_cd tinyint,
password varchar(16),
created_by varchar(32),
created_date datetime,
modified_by varchar(32),
modified_date datetime,
client_update_counter tinyint,
address_1 varchar(32),
address_2 varchar(32),
address_3 varchar(32),
city varchar(32),
state varchar(32),
country_type_cd tinyint,
zip_code varchar(16),
address_update_counter tinyint,
email varchar(32),
email_enabled bit,
email_update_counter tinyint,
last_login datetime,
last_transaction_amount money,
total_balance money,
available_balance money,
deposit_balance money,
withdraw_balance money,
promotion_balance money,
adjust_bank_balance money,
adjust_comp_balance money,
wager_balance money,
win_balance money,
search_balance money)

-- this will set arguments to null so coalesce will work.
if @client_id = 0 set @client_id = null
if @alias = '' set @alias = null
if @first_name = '' set @first_name = null
if @last_name = '' set @last_name = null
if @email = '' set @email = null

insert into @table
select
client.client_id,
client.client_type_cd,
client.client_status_type_cd,
client.client_level_type_cd,
client.alias,
client.first_name,
client.last_name,
client.middle_name,
client.birth_date,
client.language_type_cd,
client.gender_type_cd,
client.password,
client.created_by,
client.created_date,
client.modified_by,
client.modified_date,
client.update_counter as "client_update_counter",
client_address.address_1,
client_address.address_2,
client_address.address_3,
client_address.city,
client_address.state,
client_address.country_type_cd,
client_address.zip_code,
client_address.update_counter as "address_update_counter",
client_email.email,
client_email.enabled as "email_enabled",
client_email.update_counter as "email_update_counter",
client_session.created_date as "last_login",
client_transaction.amount as "last_transaction_amount",
null as "total_balance",
null as "available_balance",
null as "deposit_balance",
null as "withdraw_balance",
null as "promotion_balance",
null as "adjust_bank_balance",
null as "adjust_comp_balance",
null as "wager_balance",
null as "win_balance",
null as "search_balance"
from client
join client_address
on client_address.client_id = client.client_id
join client_email
on client_email.client_id = client.client_id
and client_email.email like '%' + coalesce(@email, client_email.email) + '%'
left outer join client_session
on client_session.client_id = client.client_id
and client_session.created_date = (select max(created_date) from client_session where client_id = client.client_id)
left outer join client_transaction
on client_transaction.client_id = client.client_id
and client_transaction.created_date = (select max(created_date) from client_transaction where client_id = client.client_id)
where client.client_id = coalesce(@client_id, client.client_id)
and client.alias like '%' + coalesce(@alias, client.alias) + '%'
and client.first_name like '%' + coalesce(@first_name, client.first_name) + '%'
and client.last_name like '%' + coalesce(@last_name, client.last_name) + '%'

declare client_cursor cursor for
select client_id from @table
open client_cursor
fetch next from client_cursor into @client_id_temp
while @@fetch_status = 0 begin
exec dbo.client_balance @client_id_temp, 2, @total_balance OUTPUT, @available_balance OUTPUT, @deposit_balance OUTPUT, @withdraw_balance OUTPUT, @promotion_balance OUTPUT, @adjust_bank_balance OUTPUT, @adjust_comp_balance OUTPUT, @wager_balance OUTPUT, @win_balance OUTPUT

update @table set
total_balance = @total_balance,
available_balance = @available_balance,
deposit_balance = @deposit_balance,
withdraw_balance = @withdraw_balance,
promotion_balance = @promotion_balance,
adjust_bank_balance = @adjust_bank_balance,
adjust_comp_balance = @adjust_comp_balance,
wager_balance = @wager_balance,
win_balance = @win_balance,
search_balance =
case
when @balance_type = 0 then 0
when @balance_type = 1 then @total_balance
when @balance_type = 2 then @available_balance
when @balance_type = 3 then @deposit_balance
when @balance_type = 4 then @withdraw_balance
when @balance_type = 5 then @promotion_balance
when @balance_type = 6 then @adjust_bank_balance
when @balance_type = 7 then @adjust_comp_balance
when @balance_type = 8 then @wager_balance
when @balance_type = 9 then @win_balance
end
where client_id = @client_id_temp

fetch next from client_cursor into @client_id_temp
end

select *
from @table
where search_balance between @balance_from and @balance_to
order by
case
when @order_by = 1 and @order_direction = 1 then cast(client_id as varchar)
when @order_by = 2 and @order_direction = 1 then alias
when @order_by = 3 and @order_direction = 1 then cast(last_name + ', ' + first_name as varchar)
when @order_by = 4 and @order_direction = 1 then email
when @order_by = 5 and @order_direction = 1 then cast(client_level_type_cd as varchar)
end asc,
case
when @order_by = 6 and @order_direction = 1 then total_balance
end asc,
case
when @order_by = 1 and @order_direction = 2 then cast(client_id as varchar)
when @order_by = 2 and @order_direction = 2 then alias
when @order_by = 3 and @order_direction = 2 then cast(last_name + ', ' + first_name as varchar)
when @order_by = 4 and @order_direction = 2 then email
when @order_by = 5 and @order_direction = 2 then cast(client_level_type_cd as varchar)
end desc,
case
when @order_by = 6 and @order_direction = 2 then total_balance
end desc

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site