How to add if ~else to a exist Stored Procedure

here is my original Sql Code

I add a parameters @.CustId in line 13

when CustID not Null, I want to add this to the Where clause line 146 and 156

if CustId not null then where clause will add a reference like CustId = @.CustID

if CustId is Null then not reference @.custID

how to Add a if ~else clause to my code? i tried all day.. but it doesn't work..

1SET QUOTED_IDENTIFIEROFF2GO3SET ANSI_NULLSOFF4GO5678ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]9@.DTBEGDATETIME,10@.DTENDDATETIME,11@.RemarkINT,12@.BankIdVARCHAR (128)13@.CustIDCHAR141516as17181920SELECT21*22FROM23(24SELECT25ZT_Master.PriKey,26ZT_Master.BankId,27ZT_Master.TDateTime,28ZT_Master.PNo,29ZT_Master.Remark,30ZT_Master.CustId,31ZT_Master.ProcStatus,32ZT_Customer.[Name],33--ZT_Customer.AccountBAK AS Account,34--ZT_Customer.SCAccountBAK AS SCAccount35(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SD')AS Account,36(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SC')AS SCAccount37FROM38ZT_MasterLEFTJOIN ZT_CustomerON ZT_Master.CustId=ZT_Customer.Id39) a40,---------------------------------------------------41(SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),42CtcbBanSDTotal=ISNULL(本行代收筆數,0),43CtcbBanSDTotalAMT=ISNULL(本行代收金額,0),44OtherBanSDTotal=ISNULL(他行代收筆數,0),45OtherBanSDTotalAMT=ISNULL(他行代收金額,0),46CtcbBanSCTotal=ISNULL(本行代付筆數,0),47CtcbBanSCTotalAMT=ISNULL(本行代付金額,0),48OtherBanSCTotal=ISNULL(他行代付筆數,0),49OtherBanSCTotalAMT=ISNULL(他行代付金額,0),50GoodSDTotal=ISNULL(代收成功筆數,0),51GoodSDTotalAMT=ISNULL(代收成功金額,0),52GoodSCTotal=ISNULL(代付成功筆數,0),53GoodSCTotalAMT=ISNULL(代付成功金額,0),54BadSDTotal=ISNULL(代收失敗筆數,0),55BadSDTotalAMT=ISNULL(代收失敗金額,0),56BadSCTotal=ISNULL(代付失敗筆數,0),57BadSCTotalAMT=ISNULL(代付失敗金額,0)58FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),59本行代收筆數,60本行代收金額,61他行代收筆數,62他行代收金額,63本行代付筆數,64本行代付金額,65他行代付筆數,66他行代付金額,67代收成功筆數,68代收成功金額,69代付成功筆數,70代付成功金額71FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),72本行代收筆數,73本行代收金額,74他行代收筆數,75他行代收金額,76本行代付筆數,77本行代付金額,78他行代付筆數,79他行代付金額80--------------------代收成功筆數與代收成功金額-------------------81FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),82本行代收筆數,83本行代收金額,84他行代收筆數,85他行代收金額86FROM (SELECT MasterKey,87COUNT(PriKey)AS 本行代收筆數,88SUM(AMT)AS 本行代收金額89FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a90FULLJOIN (SELECT MasterKey,91COUNT(PriKey)AS 他行代收筆數,92SUM(AMT)AS 他行代收金額93FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b94ON a.MasterKey=b.MasterKey) a95-------------------代付成功筆數與代付成功金額-------------------96FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),97本行代付筆數,98本行代付金額,99他行代付筆數,100他行代付金額101FROM (SELECT MasterKey,102COUNT(PriKey)AS 本行代付筆數,103SUM(AMT)AS 本行代付金額104FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a105FULLJOIN (SELECT MasterKey,106COUNT(PriKey)AS 他行代付筆數,107SUM(AMT)AS 他行代付金額108FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b109ON a.MasterKey=b.MasterKey) b110ON a.MasterKey=b.MasterKey) a111--------------------成功筆數與成功金額-----------------------112FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),113代收成功筆數,114代收成功金額,115代付成功筆數,116代付成功金額117FROM (SELECT MasterKey,118COUNT(PriKey)AS 代收成功筆數,119SUM(AMT)AS 代收成功金額120FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) a121FULLJOIN (SELECT MasterKey,122COUNT(PriKey)AS 代付成功筆數,123SUM(AMT)AS 代付成功金額124FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) b125ON a.MasterKey=b.MasterKey) b126ON a.MasterKey=b.MasterKey) a127---------------------失敗筆數與失敗金額------------------------128FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),129代收失敗筆數,130代收失敗金額,131代付失敗筆數,132代付失敗金額133FROM (SELECT MasterKey,134COUNT(PriKey)AS 代收失敗筆數,135SUM(AMT)AS 代收失敗金額136FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) a137FULLJOIN (SELECT MasterKey,138COUNT(PriKey)AS 代付失敗筆數,139SUM(AMT)AS 代付失敗金額140FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) b141ON a.MasterKey=b.MasterKey) b142ON a.MasterKey=b.MasterKey) b143144145146WHERE a.PriKey=b.MasterKeyAND147--TDateTime BETWEEN @.DTBEG AND @.DTEND AND148TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND149150(a.Remark=@.RemarkOR @.Remark=2)AND151(a.BankId=@.BankIdOR @.BankId='')152ORDER BY TDateTime,CustId,PNo153154155156WHERE a.PriKey=b.MasterKeyAND157--TDateTime BETWEEN @.DTBEG AND @.DTEND AND158TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND159160(a.Remark=@.RemarkOR @.Remark=2)AND161(a.BankId=@.BankIdOR @.BankId='')162ORDER BY TDateTime,CustId,PNo163164165GO166SET QUOTED_IDENTIFIEROFF167GO168SET ANSI_NULLSON169GO170171

CustID=COALESCE(@.CustID,CustId)

COALESCE function returns the first non-null expression in its expression list.

Refer the below link for more information

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

How to add condition to where clause?

I have a long query and therefore want to avoid using it twice in an if else
structure, while still be able to achieve excluding an id when
@.id=null(meaning all included) in the where clause or like.
i.e.
if @.id=null
theId<>555
Thanks,
--
bicbic wrote:
> I have a long query and therefore want to avoid using it twice in an if el
se
> structure, while still be able to achieve excluding an id when
> @.id=null(meaning all included) in the where clause or like.
> i.e.
> if @.id=null
> theId<>555
> Thanks,
One way is to add:
AND (id = @.id OR @.id IS NULL)|||WHERE (@.id IS NULL OR theId<>555)
On Thu, 22 Jun 2006 11:10:02 -0700, bic
<bic@.discussions.microsoft.com> wrote:

>I have a long query and therefore want to avoid using it twice in an if els
e
>structure, while still be able to achieve excluding an id when
>@.id=null(meaning all included) in the where clause or like.
>i.e.
>if @.id=null
>theId<>555
>Thanks,|||If I understood it right.
one way
(id = @.id or @.id is null)
another way
id = coalesce(@.id,id)
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If you posted the entire query, it would be easier to understand what you ar
e trying to accomplish.
Pehaps this idea could work for you. (It would require @.ID = 0 rather than n
ull)
USING NORTHWIND
GO
DECLARE @.Test int
SET @.Test = 0
SELECT
EmployeeID
, LastName
FROM Employees
WHERE ( @.Test = CASE @.Test WHEN 0 THEN @.Test ELSE -1 END
OR EmployeeID = @.Test
)
SET @.Test = 5
SELECT
EmployeeID
, LastName
FROM Employees
WHERE ( @.Test = CASE @.Test WHEN 0 THEN @.Test ELSE -1 END
OR EmployeeID = @.Test
)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"bic" <bic@.discussions.microsoft.com> wrote in message news:12381DCD-549E-40D0-B85B-EC9A87A
A005C@.microsoft.com...
>I have a long query and therefore want to avoid using it twice in an if els
e
> structure, while still be able to achieve excluding an id when
> @.id=null(meaning all included) in the where clause or like.
> i.e.
> if @.id=null
> theId<>555
>
> Thanks,
> --
> bic|||Perhaps I did not make myself clear; I am trying to implement the following
,
AND theid= CASE WHEN @.id=NULL THEN <>55 ELSE @.id END
can someone offer help to fix my syntax. Thanks.
bic
"bic" wrote:

> I have a long query and therefore want to avoid using it twice in an if el
se
> structure, while still be able to achieve excluding an id when
> @.id=null(meaning all included) in the where clause or like.
> i.e.
> if @.id=null
> theId<>555
> Thanks,
> --
> bic|||bic wrote:
> Perhaps I did not make myself clear; I am trying to implement the followi
ng,
> AND theid= CASE WHEN @.id=NULL THEN <>55 ELSE @.id END
> can someone offer help to fix my syntax. Thanks.
>
AND ((@.id IS NULL AND theid <> 55) OR (theid = @.id))|||For your particular situation:
(If you can pass in a 0 instead of a NULL for @.ID to get all records -except
555)
WHERE ( ( @.ID = CASE @.ID WHEN 0 THEN @.ID ELSE -1 END
AND theID <> 555
)
OR EmployeeID = @.ID
)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23N1ZptilGHA.1276@.TK
2MSFTNGP03.phx.gbl...
If you posted the entire query, it would be easier to understand what you ar
e trying to accomplish.
Pehaps this idea could work for you. (It would require @.ID = 0 rather than n
ull)
USING NORTHWIND
GO
DECLARE @.Test int
SET @.Test = 0
SELECT
EmployeeID
, LastName
FROM Employees
WHERE ( @.Test = CASE @.Test WHEN 0 THEN @.Test ELSE -1 END
OR EmployeeID = @.Test
)
SET @.Test = 5
SELECT
EmployeeID
, LastName
FROM Employees
WHERE ( @.Test = CASE @.Test WHEN 0 THEN @.Test ELSE -1 END
OR EmployeeID = @.Test
)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"bic" <bic@.discussions.microsoft.com> wrote in message news:12381DCD-549E-40D0-B85B-EC9A87A
A005C@.microsoft.com...
>I have a long query and therefore want to avoid using it twice in an if els
e
> structure, while still be able to achieve excluding an id when
> @.id=null(meaning all included) in the where clause or like.
> i.e.
> if @.id=null
> theId<>555
>
> Thanks,
> --
> bic

how to add a where clause by parameter in a stored procedure

What i want is to add by parameter a Where clause and i can not find how to do it!
CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 Where fieldId = @.id /*and @.WhereClause */
GO

thx
What error did you get? That sproc defintion looks fine to me apart from the fact that you've missed the FROM clause from the parameterised SQL statement.

-Jamie|||thx for the reply.

You are right. I forgot the from clause.

CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 FROM Table1 Where fieldId = @.id /*and @.WhereClause */
GO
I get only a syntax error when checking the procedure syntax|||

SQL Server does not support "parameterizing" the WHERE clause or any syntactic construct.

You have to construct the SQL String using string concatenation operations and then execute the constructed string using the dynamic EXEC statement.

CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
BEGIN
DECLARE @.mdstring nvarchar(300);
set @.cmdstring = "SELECT field1, field2 FROM T WHERE fieldID = " + @.id +
" AND " + @.whereClause;
EXEC(@.cmdstring);
END
GO

|||Personally, I dislike the above mentioned method. Besides the fact that it opens you to all sorts of bad stuff.

What you can do is make the parameters nullable, and do a case statement on the where part of the clause:

create procedure pSomethingOrAnother
(
@.p_lID integer,
@.p_lWhereItem1 integer,
@.p_sWhereItem2 varchar(50)
)
as

set nocount on

select
t.Field1,
t.Field2
from
TableName t
where
t.FieldID = @.p_lID
and (case when ISNULL(@.p_lWhereItem1, 0) = 0 then 0 else t.FieldToCheck1 end) = ISNULL(@.p_lWhereItem1, 0)
and (case when ISNULL(@.p_lWhereItem2, '') = '' then '' else t.FieldToCheck2 end) = ISNULL(@.p_sWhereItem2, '')

set nocount off

No magic values, no string concatenation, no million if statements. The only time this won't work so well is when you are looking for the value NULL in a field, but you can separate those instances out with an IF statement for that field.

how to add a where clause by parameter in a stored procedure

What i want is to add by parameter a Where clause and i can not find how to do it!
CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 from table1 Where fieldId = @.id /*and @.WhereClause */
GO
any suggestion?What you're trying to do can only be done using dynamic SQL. Dynamic SQL is a pretty large topic, so here are a few links to get youstarted:
http://www.databasejournal.com/features/mssql/article.php/1438931
http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sommarskog.se/dynamic_sql.html