June 14, 2012 at 1:49 am
I am writing a stored procedure with a parameter @status.(which will be a drop down list. All = -1, complete = 1, incomplete = 0)
There is a field in the table called userAccount.
Status can be All, Complete or Incomplete. Depending on the userAccount field.
If the userAccount field is null then the status is incomplete
If the userAcccout field has some data then it is complete.
I dont know how to write a where clause. Can anyone help. I think we can use case but i dont know how. Thanks in advance.
June 14, 2012 at 2:48 am
Something like this?
SELECT
myID -- something that uniquely identifies your row
,userAccount
,Status = CASE WHEN userAccount IS NULL
THEN 0
ELSE 1
END
FROM myAccountTable
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 2:55 am
SELECT * FROM
(SELECT
myID -- something that uniquely identifies your row
,userAccount
,Status = CASE WHEN userAccount IS NULL
THEN 0
ELSE 1
END
FROM myAccountTable) sq
WHERE Status = @status
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 3:09 am
Maybe you can share some more information?
What is the stored procedure supposed to do?
What is the structure of the table?
Read the first link in my signature about posting questions.
It will explain how to post DDL for the tables, sample data and desired output.
Also post the statements you have so far.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 3:16 am
sorry for being a mess.
ok someting like this
Alter procedure [dbo].[Application]
@forename varchar(50),
@surname varchar(50),
@status int,
@origin int,
@pagenumber int,
@pagesize int,
@sortexpression varchar(32),
@sortorder varchar(4)
as
select OriginName,[Status],Forename,Surname,EmailAddress,CreateDate,LastUpdate,UseAccountNumber
from
(
select
cp.ClientID,cp.ClientID as uniquevalue,cpo.OriginName,
[Status] = CASE WHEN cpa.UseAccountNumber IS null THEN 0 ELSE 1 END,
cp.Forename,
cp.Surname,
cp.EmailAddress,
cpa.CreateDate,
cpa.LastUpdate,
cpa.UseAccountNumber
from
ClientPotential cp with (nolock)
inner join ClientPotentialAccount cpa with (nolock) on cpa.Code = cp.Code
inner join ClientPotentialOrigin cpo with (nolock) on cpo.OriginId = cpa.Origin
WHERE
(cp.Surname LIKE '%' + @surname + '%' OR cp.Surname is NULL) AND
(cp.Forename LIKE '%' + @forename + '%' OR cp.Forename is NULL) AND
(cpo.OriginId = @origin or @origin = -1)
) as derived
GO
so I want to add status in where clause.
June 14, 2012 at 3:25 am
Normally this should work:
Alter procedure [dbo].[Application]
@forename varchar(50),
@surname varchar(50),
@status int,
@origin int,
@pagenumber int,
@pagesize int,
@sortexpression varchar(32),
@sortorder varchar(4)
as
select OriginName,[Status],Forename,Surname,EmailAddress,CreateDate,LastUpdate,UseAccountNumber
from
(
select
cp.ClientID,cp.ClientID as uniquevalue,cpo.OriginName,
[Status] = CASE WHEN cpa.UseAccountNumber IS null THEN 0 ELSE 1 END,
cp.Forename,
cp.Surname,
cp.EmailAddress,
cpa.CreateDate,
cpa.LastUpdate,
cpa.UseAccountNumber
from
ClientPotential cp with (nolock)
inner join ClientPotentialAccount cpa with (nolock) on cpa.Code = cp.Code
inner join ClientPotentialOrigin cpo with (nolock) on cpo.OriginId = cpa.Origin
WHERE
(cp.Surname LIKE '%' + @surname + '%' OR cp.Surname is NULL) AND
(cp.Forename LIKE '%' + @forename + '%' OR cp.Forename is NULL) AND
(cpo.OriginId = @origin or @origin = -1)
) as derived
WHERE derived.Status = @status -- added code here
GO
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 3:36 am
Alter procedure [dbo].[Application]
@forename varchar(50),
@surname varchar(50),
@status int,
@origin int,
@pagenumber int,
@pagesize int,
@sortexpression varchar(32),
@sortorder varchar(4)
as
if @pagenumber < 1
begin
set @pageNumber = 1
end
declare @lastkeyvalue varchar(7)
declare @lastascendingsortvalue sql_variant
declare @lastdescendingsortvalue sql_variant
declare @numbertoignore int
set @numbertoignore = (@pagenumber-1) * @pagesize
if @numberToIgnore > 0
begin
-- Get the last available sort data and unique key value from the last page.
set rowcount @numbertoignore
select
@lastkeyvalue = uniquevalue,
@lastascendingsortvalue = ascendingsort,
@lastdescendingsortvalue = descendingsort
from
(
select
cp.ClientID as uniquevalue,
case
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'ForeName'
THEN
convert(sql_variant, cp.Forename)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'Surname'
THEN
convert(sql_variant, cp.Surname)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'EmailAddress'
THEN
convert(sql_variant, cp.EmailAddress)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'CreateDate'
THEN
convert(sql_variant, cpa.CreateDate)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'LastUpdate'
THEN
convert(sql_variant, cpa.LastUpdate)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'UserAccountNumber'
THEN
convert(sql_variant, cpa.UseAccountNumber)
else
null
end as descendingsort,
case
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'ForeName'
THEN
convert(sql_variant, cp.Forename)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'Surname'
THEN
convert(sql_variant, cp.Surname)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'EmailAddress'
THEN
convert(sql_variant, cp.EmailAddress)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'CreateDate'
THEN
convert(sql_variant, cpa.CreateDate)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'LastUpdate'
THEN
convert(sql_variant, cpa.LastUpdate)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'UserAccountNumber'
THEN
convert(sql_variant, cpa.UseAccountNumber)
else
null
end as ascendingsort
from
ClientPotential cp with (nolock)
inner join ClientPotentialAccount cpa with (nolock) on cpa.Code = cp.Code
inner join ClientPotentialOrigin cpo with (nolock) on cpo.OriginId = cpa.Origin
WHERE
(cp.Surname LIKE '%' + @surname + '%' OR cp.Surname is NULL) AND
(cp.Forename LIKE '%' + @forename + '%' OR cp.Forename is NULL) AND
(cpo.OriginId = @origin or @origin = -1)
) as derived
order by
ascendingsort asc,
descendingsort desc,
uniquevalue asc
end
-- select the first @pageSize records that come after the last sort data/unique value from the last page.
-- if this is the first page, just get the first @pageSize records.
set rowcount @pageSize
select OriginName,[Status],Forename,Surname,EmailAddress,CreateDate,LastUpdate,UseAccountNumber
from
(
select
cp.ClientID,cp.ClientID as uniquevalue,cpo.OriginName,
[Status] = CASE WHEN cpa.UseAccountNumber IS null THEN 0 ELSE 1 END,
cp.Forename,
cp.Surname,
cp.EmailAddress,
cpa.CreateDate,
cpa.LastUpdate,
cpa.UseAccountNumber,
case
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'ForeName'
THEN
convert(sql_variant, cp.Forename)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'Surname'
THEN
convert(sql_variant, cp.Surname)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'EmailAddress'
THEN
convert(sql_variant, cp.EmailAddress)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'CreateDate'
THEN
convert(sql_variant, cpa.CreateDate)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'LastUpdate'
THEN
convert(sql_variant, cpa.LastUpdate)
WHEN
upper(@sortOrder) = 'DESC'
and upper(@sortExpression) = 'UserAccountNumber'
THEN
convert(sql_variant, cpa.UseAccountNumber)
else
null
end as descendingsort,
case
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'ForeName'
THEN
convert(sql_variant, cp.Forename)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'Surname'
THEN
convert(sql_variant, cp.Surname)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'EmailAddress'
THEN
convert(sql_variant, cp.EmailAddress)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'CreateDate'
THEN
convert(sql_variant, cpa.CreateDate)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'LastUpdate'
THEN
convert(sql_variant, cpa.LastUpdate)
WHEN
upper(@sortOrder) = 'ASC'
and upper(@sortExpression) = 'UserAccountNumber'
THEN
convert(sql_variant, cpa.UseAccountNumber)
else
null
end as ascendingsort
from
ClientPotential cp with (nolock)
inner join ClientPotentialAccount cpa with (nolock) on cpa.Code = cp.Code
inner join ClientPotentialOrigin cpo with (nolock) on cpo.OriginId = cpa.Origin
WHERE
(cp.Surname LIKE '%' + @surname + '%' OR cp.Surname is NULL) AND
(cp.Forename LIKE '%' + @forename + '%' OR cp.Forename is NULL) AND
(cpo.OriginId = @origin or @origin = -1)
) as derived
WHERE
(
@numbertoignore = 0
)
or
(
@sortorder='ASC'
and
(
(
@lastascendingsortvalue is null
and
(
(uniquevalue > @lastkeyvalue and ascendingsort is null)
or
ascendingsort is not null
)
)
or
(
@lastascendingsortvalue is not null
and
(
ascendingsort > @lastascendingsortvalue
or
(ascendingsort = @lastascendingsortvalue and uniquevalue > @lastkeyvalue)
)
)
)
)
or
(
@sortorder='DESC'
and
(
(
@lastdescendingsortvalue is not null
and (
(descendingsort is not null and
(descendingsort = @lastdescendingsortvalue and uniquevalue > @lastkeyvalue)
or
(descendingsort < @lastdescendingsortvalue)
)
or
(descendingsort is null)
)
)
or
(@lastdescendingsortvalue is null and descendingsort is null and uniquevalue > @lastkeyvalue)
)
)
order by ascendingsort asc, descendingsort desc, uniquevalue asc
set rowcount 0
GO
I included sorting in it. So how do I do it now?
June 14, 2012 at 3:41 am
Include AND derived.Status = @status at the end.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 3:50 am
it doesnt work. But its ok I will manage this in code. 🙂
June 14, 2012 at 4:05 am
just for your help mate..
this is the solution 🙂
where
(ISNULL(@status, 0) = 0 AND cpa.UseAccountNumber IS NULL)OR
(@status = 1 AND cpa.UseAccountNumber IS NOT NULL)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply