need help on dynamic SQL - 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)

need help on dynamic SQL

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-06-04 : 23:45:47

I've dynamic SQL as following,


declare @icNo varchar(100)
declare @payerNme varchar(300)
declare @kodAmil varchar(100)

set @payerNme='sharul'
set @kodAmil='000100'

declare @SQL varchar(max)

SELECT @SQL = 'select t1.sysReceiptNo, t3.[icNo (Baru)] as icNo, upper(t3.nme) as payerNme,
t5.descrp as daerahKutipan, upper(t4.nme) as amilNme, t6.descrp as paymentMethod,
t1.paymentDte
 from paymentH t1 inner join paymentItem t2
on t1.idx=t2.paymentH
inner join payer t3 on t1.payer=t3.idx
inner join amil t4 on t1.amil=t4.idx
inner join daerahKutipan t5 on t1.daerahKutipan=t5.idx
inner join paymentMethod t6 on t1.paymentMethod=t6.idx' 

if @icNo is null and @payerNme is null and @kodAmil is null
Begin
	SELECT @SQL = @SQL
End
else
Begin
	SELECT @SQL = @SQL + ' where '
End

if @icNo is not null
Begin
	SELECT @SQL = @SQL + ' t3.[icNo (Baru)]=''' + @icNo + ''''
End

if @payerNme is not null
Begin
	SELECT @SQL = @SQL + ' t3.nme like ''%' + @payerNme + '%'''
End

if @kodAmil is not null
Begin
	SELECT @SQL = @SQL + ' t4.code=''' + @kodAmil + ''''
End


--Exec ( @SQL)
print @SQL

So, my result as following,

select t1.sysReceiptNo, t3.[icNo (Baru)] as icNo, upper(t3.nme) as payerNme,
t5.descrp as daerahKutipan, upper(t4.nme) as amilNme, t6.descrp as paymentMethod,
t1.paymentDte
 from paymentH t1 inner join paymentItem t2
on t1.idx=t2.paymentH
inner join payer t3 on t1.payer=t3.idx
inner join amil t4 on t1.amil=t4.idx
inner join daerahKutipan t5 on t1.daerahKutipan=t5.idx
inner join paymentMethod t6 on t1.paymentMethod=t6.idx where  t3.nme like '%sharul%' t4.code='000100'

if I change


if @kodAmil is not null
Begin
	SELECT @SQL = @SQL + ' t4.code=''' + @kodAmil + ''''
End


if @kodAmil is not null
Begin
	SELECT @SQL = @SQL + ' and t4.code=''' + @kodAmil + ''''
End

So, the result as following,


select t1.sysReceiptNo, t3.[icNo (Baru)] as icNo, upper(t3.nme) as payerNme,
t5.descrp as daerahKutipan, upper(t4.nme) as amilNme, t6.descrp as paymentMethod,
t1.paymentDte
 from paymentH t1 inner join paymentItem t2
on t1.idx=t2.paymentH
inner join payer t3 on t1.payer=t3.idx
inner join amil t4 on t1.amil=t4.idx
inner join daerahKutipan t5 on t1.daerahKutipan=t5.idx
inner join paymentMethod t6 on t1.paymentMethod=t6.idx where  t3.nme like '%sharul%' and t4.code='000100'

and it's ok

BUT

IT'S NOT OK, if my dynamic SQL as following,


declare @icNo varchar(100)
declare @payerNme varchar(300)
declare @kodAmil varchar(100)


set @kodAmil='000100' /*only @kodAmil will pass*/

declare @SQL varchar(max)

SELECT @SQL = 'select t1.sysReceiptNo, t3.[icNo (Baru)] as icNo, upper(t3.nme) as payerNme,
t5.descrp as daerahKutipan, upper(t4.nme) as amilNme, t6.descrp as paymentMethod,
t1.paymentDte
 from paymentH t1 inner join paymentItem t2
on t1.idx=t2.paymentH
inner join payer t3 on t1.payer=t3.idx
inner join amil t4 on t1.amil=t4.idx
inner join daerahKutipan t5 on t1.daerahKutipan=t5.idx
inner join paymentMethod t6 on t1.paymentMethod=t6.idx' 

if @icNo is null and @payerNme is null and @kodAmil is null
Begin
	SELECT @SQL = @SQL
End
else
Begin
	SELECT @SQL = @SQL + ' where '
End

if @icNo is not null
Begin
	SELECT @SQL = @SQL + ' t3.[icNo (Baru)]=''' + @icNo + ''''
End

if @payerNme is not null
Begin
	SELECT @SQL = @SQL + ' t3.nme like ''%' + @payerNme + '%'''
End

if @kodAmil is not null
Begin
	SELECT @SQL = @SQL + ' and t4.code=''' + @kodAmil + ''''
End


--Exec ( @SQL)
print @SQL

Please help me to adjust my dynamic sql. As a result, I can pass
1. no parameter
2. @icNo only
3. @payerNme only
4. @kodAmil only
5. @icNo and @payerNme only
6. @icNo and @kodAmil only
7. @payerNme and @kodAmil only
8. and so on ....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 00:17:34

just add a default condition with where and it should work fine

see


declare @icNo varchar(100)
declare @payerNme varchar(300)
declare @kodAmil varchar(100)


set @kodAmil='000100' /*only @kodAmil will pass*/

declare @SQL varchar(max)

SELECT @SQL = 'select t1.sysReceiptNo, t3.[icNo (Baru)] as icNo, upper(t3.nme) as payerNme,
t5.descrp as daerahKutipan, upper(t4.nme) as amilNme, t6.descrp as paymentMethod,
t1.paymentDte
 from paymentH t1 inner join paymentItem t2
on t1.idx=t2.paymentH
inner join payer t3 on t1.payer=t3.idx
inner join amil t4 on t1.amil=t4.idx
inner join daerahKutipan t5 on t1.daerahKutipan=t5.idx
inner join paymentMethod t6 on t1.paymentMethod=t6.idx' 

if @icNo is null and @payerNme is null and @kodAmil is null
Begin
	SELECT @SQL = @SQL
End
else
Begin
	SELECT @SQL = @SQL + ' where 1=1'
End

if @icNo is not null
Begin
	SELECT @SQL = @SQL + 'and  t3.[icNo (Baru)]=''' + @icNo + ''''
End

if @payerNme is not null
Begin
	SELECT @SQL = @SQL + 'and t3.nme like ''%' + @payerNme + '%'''
End

if @kodAmil is not null
Begin
	SELECT @SQL = @SQL + 'and  t4.code=''' + @kodAmil + ''''
End


--Exec ( @SQL)
print @SQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-06-05 : 04:29:19

tq sir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 10:28:20

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources