September 8, 2011 at 7:45 am
Hi Folks,
I have some issues with stored procedure on SQL Server 2005.
My Conditions:
1-by date range,
2-according to cari_kod,
3-according to stok_kod,
I have to write inside same stored procedure the above conditions.How do I change my query?
Code:
ALTER PROC SP_ONAYLARI_PARAMETRELI_LISTELE
@ilktarih datetime,
@sontarih datetime,
@cari_kod nvarchar(25),
@stok_kod nvarchar(25)
AS
IF(@cari_kod=NULL AND @stok_kod=NULL AND @ilktarih!=NULL AND @sontarih!=NULL)
BEGIN
SELECT O.Onay_ID,O.Supervisor,O.[User 1],O.[User 2],O.[User 3],ST.sto_isim AS ISMI,ST.sto_kod AS KODU,ST.sto_birim1_ad AS BIRIMI,F.fir_unvan AS FIRMAUNVANI,S.sip_teslim_tarih AS TESLIMTARIHI,S.sip_evrakno_seri AS SERINO,S.sip_evrakno_sira AS SIRANO,C.cari_kod AS CARIKOD,C.cari_unvan1 AS CARIISMI,D.dep_adi AS DEPO,S.sip_miktar AS SIPARISMIKTARI,S.sip_b_fiyat AS BIRIMFIYATI,S.sip_tutar AS TUTAR,S.sip_fiyat_liste_no AS FIYATLÝSTENO,S.sip_teslimturu AS SORUMLULUKMERKEZIKOD
FROM CARI_HESAPLAR C,DEPOLAR D,FIRMALAR F,SIPARISLER S,STOKLAR ST,ONAYLAR O
WHERE O.sip_RECno=S.sip_RECno AND
C.cari_kod=S.sip_musteri_kod AND
S.sip_stok_kod=ST.sto_kod AND
S.sip_depono=D.dep_RECid_RECno AND S.sip_OnaylayanKulNo=0 AND
S.sip_teslim_tarih BETWEEN @ilktarih AND @sontarih
END
IF(@cari_kod!=NULL AND @stok_kod=NULL AND @ilktarih=NULL AND @sontarih=NULL)
BEGIN
SELECT O.Onay_ID,O.Supervisor,O.[User 1],O.[User 2],O.[User 3],ST.sto_isim AS ISMI,ST.sto_kod AS KODU,ST.sto_birim1_ad AS BIRIMI,F.fir_unvan AS FIRMAUNVANI,S.sip_teslim_tarih AS TESLIMTARIHI,S.sip_evrakno_seri AS SERINO,S.sip_evrakno_sira AS SIRANO,C.cari_kod AS CARIKOD,C.cari_unvan1 AS CARIISMI,D.dep_adi AS DEPO,S.sip_miktar AS SIPARISMIKTARI,S.sip_b_fiyat AS BIRIMFIYATI,S.sip_tutar AS TUTAR,S.sip_fiyat_liste_no AS FIYATLÝSTENO,S.sip_teslimturu AS SORUMLULUKMERKEZIKOD
FROM CARI_HESAPLAR C,DEPOLAR D,FIRMALAR F,SIPARISLER S,STOKLAR ST,ONAYLAR O
WHERE O.sip_RECno=S.sip_RECno AND
C.cari_kod=S.sip_musteri_kod AND
S.sip_stok_kod=ST.sto_kod AND
S.sip_depono=D.dep_RECid_RECno AND S.sip_OnaylayanKulNo=0 AND
C.cari_kod=@cari_kod
END
IF(@cari_kod=NULL AND @stok_kod!=NULL AND @ilktarih=NULL AND @sontarih=NULL)
BEGIN
SELECT O.Onay_ID,O.Supervisor,O.[User 1],O.[User 2],O.[User 3],ST.sto_isim AS ISMI,ST.sto_kod AS KODU,ST.sto_birim1_ad AS BIRIMI,F.fir_unvan AS FIRMAUNVANI,S.sip_teslim_tarih AS TESLIMTARIHI,S.sip_evrakno_seri AS SERINO,S.sip_evrakno_sira AS SIRANO,C.cari_kod AS CARIKOD,C.cari_unvan1 AS CARIISMI,D.dep_adi AS DEPO,S.sip_miktar AS SIPARISMIKTARI,S.sip_b_fiyat AS BIRIMFIYATI,S.sip_tutar AS TUTAR,S.sip_fiyat_liste_no AS FIYATLÝSTENO,S.sip_teslimturu AS SORUMLULUKMERKEZIKOD
FROM CARI_HESAPLAR C,DEPOLAR D,FIRMALAR F,SIPARISLER S,STOKLAR ST,ONAYLAR O
WHERE O.sip_RECno=S.sip_RECno AND
C.cari_kod=S.sip_musteri_kod AND
S.sip_stok_kod=ST.sto_kod AND
S.sip_depono=D.dep_RECid_RECno AND S.sip_OnaylayanKulNo=0 AND
ST.sto_kod=@stok_kod
END
Thanks in advance.
September 8, 2011 at 8:18 am
You haven't explained what you issue is, nor what the conditions mean for the logic. You need to stop and go over the functionality you need, build an algorithm in psuedocode that explains what you need to happen and then write the code or ask the question.
September 8, 2011 at 9:45 am
Selahattin SADOGLU (9/8/2011)
Hi Folks,I have some issues with stored procedure on SQL Server 2005.
My Conditions:
1-by date range,
2-according to cari_kod,
3-according to stok_kod,
I have to write inside same stored procedure the above conditions.How do I change my query?
Code:
ALTER PROC SP_ONAYLARI_PARAMETRELI_LISTELE@ilktarih datetime,
@sontarih datetime,
@cari_kod nvarchar(25),
@stok_kod nvarchar(25)
AS
IF(@cari_kod=NULL AND @stok_kod=NULL AND @ilktarih!=NULL AND @sontarih!=NULL)
BEGIN
SELECT O.Onay_ID,O.Supervisor,O.[User 1],O.[User 2],O.[User 3],ST.sto_isim AS ISMI,ST.sto_kod AS KODU,ST.sto_birim1_ad AS BIRIMI,F.fir_unvan AS FIRMAUNVANI,S.sip_teslim_tarih AS TESLIMTARIHI,S.sip_evrakno_seri AS SERINO,S.sip_evrakno_sira AS SIRANO,C.cari_kod AS CARIKOD,C.cari_unvan1 AS CARIISMI,D.dep_adi AS DEPO,S.sip_miktar AS SIPARISMIKTARI,S.sip_b_fiyat AS BIRIMFIYATI,S.sip_tutar AS TUTAR,S.sip_fiyat_liste_no AS FIYATLÝSTENO,S.sip_teslimturu AS SORUMLULUKMERKEZIKOD
FROM CARI_HESAPLAR C,DEPOLAR D,FIRMALAR F,SIPARISLER S,STOKLAR ST,ONAYLAR O
WHERE O.sip_RECno=S.sip_RECno AND
C.cari_kod=S.sip_musteri_kod AND
S.sip_stok_kod=ST.sto_kod AND
S.sip_depono=D.dep_RECid_RECno AND S.sip_OnaylayanKulNo=0 AND
S.sip_teslim_tarih BETWEEN @ilktarih AND @sontarih
END
IF(@cari_kod!=NULL AND @stok_kod=NULL AND @ilktarih=NULL AND @sontarih=NULL)
BEGIN
SELECT O.Onay_ID,O.Supervisor,O.[User 1],O.[User 2],O.[User 3],ST.sto_isim AS ISMI,ST.sto_kod AS KODU,ST.sto_birim1_ad AS BIRIMI,F.fir_unvan AS FIRMAUNVANI,S.sip_teslim_tarih AS TESLIMTARIHI,S.sip_evrakno_seri AS SERINO,S.sip_evrakno_sira AS SIRANO,C.cari_kod AS CARIKOD,C.cari_unvan1 AS CARIISMI,D.dep_adi AS DEPO,S.sip_miktar AS SIPARISMIKTARI,S.sip_b_fiyat AS BIRIMFIYATI,S.sip_tutar AS TUTAR,S.sip_fiyat_liste_no AS FIYATLÝSTENO,S.sip_teslimturu AS SORUMLULUKMERKEZIKOD
FROM CARI_HESAPLAR C,DEPOLAR D,FIRMALAR F,SIPARISLER S,STOKLAR ST,ONAYLAR O
WHERE O.sip_RECno=S.sip_RECno AND
C.cari_kod=S.sip_musteri_kod AND
S.sip_stok_kod=ST.sto_kod AND
S.sip_depono=D.dep_RECid_RECno AND S.sip_OnaylayanKulNo=0 AND
C.cari_kod=@cari_kod
END
IF(@cari_kod=NULL AND @stok_kod!=NULL AND @ilktarih=NULL AND @sontarih=NULL)
BEGIN
SELECT O.Onay_ID,O.Supervisor,O.[User 1],O.[User 2],O.[User 3],ST.sto_isim AS ISMI,ST.sto_kod AS KODU,ST.sto_birim1_ad AS BIRIMI,F.fir_unvan AS FIRMAUNVANI,S.sip_teslim_tarih AS TESLIMTARIHI,S.sip_evrakno_seri AS SERINO,S.sip_evrakno_sira AS SIRANO,C.cari_kod AS CARIKOD,C.cari_unvan1 AS CARIISMI,D.dep_adi AS DEPO,S.sip_miktar AS SIPARISMIKTARI,S.sip_b_fiyat AS BIRIMFIYATI,S.sip_tutar AS TUTAR,S.sip_fiyat_liste_no AS FIYATLÝSTENO,S.sip_teslimturu AS SORUMLULUKMERKEZIKOD
FROM CARI_HESAPLAR C,DEPOLAR D,FIRMALAR F,SIPARISLER S,STOKLAR ST,ONAYLAR O
WHERE O.sip_RECno=S.sip_RECno AND
C.cari_kod=S.sip_musteri_kod AND
S.sip_stok_kod=ST.sto_kod AND
S.sip_depono=D.dep_RECid_RECno AND S.sip_OnaylayanKulNo=0 AND
ST.sto_kod=@stok_kod
END
Thanks in advance.
I think you may be trying to use a single stored proc to use multiple where clauses depending on the values being passed in?
First thing is to look at your if statement.
IF(@cari_kod=NULL AND @stok_kod!=NULL AND @ilktarih=NULL AND @sontarih=NULL)
This is NOT how you check for null or not null. Checking for equality with NULL will not return true or false. If there is a null on either side of an equality check it will be false. Consider the following as an example.
if null = null
select 'NULL'
else
select 'Not Null'
Would expect this to return 'NULL'? Try it and see. 😉
How about your inequality checks....
if null != null
select 'Not NULL'
else
select 'Null'
You can rewrite your check like this.
IF(@cari_kod is NULL AND @stok_kod is not NULL AND @ilktarih is NULL AND @sontarih is NULL)
Now let's look at how you are joining these tables. You are using the old style join (which I call equijoins). This works but can be prone to logic errors. You can pretty easily end up with accidental cross joins (which you have done). Because you don't have a where condition for table F to something else you have a cross join.
Here is how you could rewrite your joins. This tends to be a lot easier to read and also helps eliminate accidental cross joins.
FROM CARI_HESAPLAR C
join DEPOLAR D on S.sip_depono = D.dep_RECid_RECno
join FIRMALAR F --you need a join condition here
join SIPARISLER S C.cari_kod = S.sip_musteri_kod
join STOKLAR ST on S.sip_stok_kod = ST.sto_kod
join ONAYLAR O on O.sip_RECno = S.sip_RECno
WHERE S.sip_OnaylayanKulNo = 0
AND C.cari_kod = @cari_kod
Now assuming your intention is a type of "catch-all" query you should read the article[/url] from Gail's blog. It explains clearly how to build this type of query in such a way as to make it work correctly and fast.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply