T-SQL - tips and code examples
MS SQL Server dialect
T-SQL example 1
/*------------------------------------------------------------------------------------------
Procedure:
get_d_meamen_select
Description:
Returns record set for d_meamen_select.
Log:
Date Name Description
27.01.2003 Michael Zuskin Created
------------------------------------------------------------------------------------------*/
if exists (select name from sysobjects where name = 'get_d_meamen_select' and type = 'P')
drop procedure get_d_meamen_select
go
/*--------------------------------- Begin procedure --------------------------------------*/
create procedure get_d_meamen_select
/*------------------------------------ Arguments -----------------------------------------*/
@al_mezahe_ona integer,
@as_mispar_baigud varchar(20),
@as_mispar_zihuy varchar(20), /* mispar t.z./darkon/teudat leda/teudat maavar */
@as_shem_mishpaha varchar(40),
@as_shem_prati varchar(30),
@ai_status_meamen smallint,
@al_mezahe_sug_liga integer
as
/*------------------------------------ Declare -------------------------------------------*/
declare
@ll_mezahe_meamen integer,
@ls_mispar_baigud varchar(20),
@ls_mispar_zihuy varchar(20),
@ls_tz varchar(20),
@ll_mezahe_zihuy integer,
@ls_shem_mishpaha varchar(40),
@ls_shem_prati varchar(30),
@ls_reshimat_kvotzot varchar(1000),
@li_status_meamen smallint,
@ls_shem_kvotza varchar(30),
@ll_medinat_darkon integer,
@ls_shem_loazi_mekutzar varchar(3),
@li_is_meamen_in_sug_liga smallint
declare cur_meamen cursor fast_forward read_only for
select meamen.mezahe_meamen,
meamen.mispar_baigud,
zihuy.mispar_zihuy,
zihuy.tz,
zihuy.mezahe_zihuy,
IsNull(zihuy.shem_mishpaha, zihuy.english_shem_mishpaha),
IsNull(zihuy.shem_prati, zihuy.eng_shem_prati),
meamen.status,
zihuy.medinat_darkon
from zihuy,
meamen
where meamen.zihuy_meamen = zihuy.mezahe_zihuy and
(@as_mispar_baigud is null or meamen.mispar_baigud like @as_mispar_baigud + '%') and
(@as_mispar_zihuy is null or zihuy.mispar_zihuy like @as_mispar_zihuy + '%') and
(@ai_status_meamen is null or meamen.status = @ai_status_meamen) and
(
@as_shem_mishpaha is null or
zihuy.shem_mishpaha like @as_shem_mishpaha + '%' or
Upper(zihuy.english_shem_mishpaha) like Upper(@as_shem_mishpaha + '%')
) and
(
@as_shem_prati is null or
zihuy.shem_prati like @as_shem_prati + '%' or
Upper(zihuy.eng_shem_prati) like Upper(@as_shem_prati + '%')
)
declare cur_shem_kvotza cursor fast_forward read_only for
select shem_kvotza
from kvotza
where mezahe_kvotza in(select mezahe_kvotza
from tafkid
where sug_yeshut = 6 /* meamen */ and
mezahe_yeshut = @ll_mezahe_meamen and
mezahe_ona <= @al_mezahe_ona))
create table #recordset (
mezahe_meamen integer,
mispar_baigud varchar(20),
mispar_zihuy varchar(20),
tz varchar(20),
mezahe_zihuy integer,
shem_male varchar(71),
shem_mishpaha varchar(40),
shem_prati varchar(30),
reshimat_kvotzot varchar(1000), /* commas-delimited list of kvotzot */
status_meamen smallint
)
/*----------------------------------- Begin logic ----------------------------------------*/
open cur_meamen
while (1 = 1) begin
fetch from cur_meamen into
@ll_mezahe_meamen,
@ls_mispar_baigud,
@ls_mispar_zihuy,
@ls_tz,
@ll_mezahe_zihuy,
@ls_shem_mishpaha,
@ls_shem_prati,
@li_status_meamen,
@ll_medinat_darkon
if (@@fetch_status = -1) break
if (@@fetch_status = -2) continue
/***************************************************************
If mezahe_sug_liga passed - make sure that the meamen has tafkid
in at least one kvotza belonging to a liga of this sug:
***************************************************************/
if (@al_mezahe_sug_liga is not null) begin
select @li_is_meamen_in_sug_liga = 0
select @li_is_meamen_in_sug_liga = 1
from tafkid
where sug_yeshut = 6 /* meamen */ and
mezahe_yeshut = @ll_mezahe_meamen and
mezahe_ona = @al_mezahe_ona and
mezahe_kvotza in
(select mezahe_kvotza
from kvotza_liga
where mezahe_ona = @al_mezahe_ona and
mezahe_liga in
(select mezahe_liga
from sug_liga
where mezahe_sug_liga = @al_mezahe_sug_liga))
if (@li_is_meamen_in_sug_liga = 0) continue
end
/******************************************
Build the commas-delimited list of kvotzot:
******************************************/
select @ls_reshimat_kvotzot = ''
open cur_shem_kvotza
while (1 = 1) begin
fetch from cur_shem_kvotza into @ls_shem_kvotza
if (@@fetch_status = -1) break
if (@@fetch_status = -2) continue
if (@ls_reshimat_kvotzot <> '')
select @ls_reshimat_kvotzot = @ls_reshimat_kvotzot + ', '
select @ls_reshimat_kvotzot = @ls_reshimat_kvotzot + @ls_shem_kvotza
end
close cur_shem_kvotza
/***************************************************************************************
If mispar_zihuy is darkon - add to it the country short code ("12345" ---> "USA 12345"):
***************************************************************************************/
if (@ll_medinat_darkon is not null) begin
select @ls_shem_loazi_mekutzar = shem_loazi_mekutzar
from medina
where mezahe_medina = @ll_medinat_darkon
select @ls_mispar_zihuy = @ls_shem_loazi_mekutzar + ' ' + @ls_mispar_zihuy
end
/********************************************
Include the current meamen in the record set:
********************************************/
insert into #recordset values (
@ll_mezahe_meamen,
@ls_mispar_baigud,
@ls_mispar_zihuy,
@ls_tz,
@ll_mezahe_zihuy,
Rtrim(Ltrim(@ls_shem_mishpaha + ' ' + @ls_shem_prati)), /* shem_male */
@ls_shem_mishpaha,
@ls_shem_prati,
@ls_reshimat_kvotzot,
@li_status_meamen
)
end /* of cur_meamen loop */
close cur_meamen
deallocate cur_meamen
deallocate cur_shem_kvotza
/*------------------------------- Return record set --------------------------------------*/
select * from #recordset order by shem_male
drop table #recordset
/*------------------------------- End of procedure ---------------------------------------*/
go
grant execute on get_d_meamen_select to public
go
exec get_d_meamen_select 4, null, null, null, null, null, null
go
T-SQL example 2
if exists (select name from sysobjects where name = 'get_d_pail_select' and type = 'P')
drop procedure get_d_pail_select
go
/*--------------------------------- Begin procedure --------------------------------------*/
create procedure get_d_pail_select
/*------------------------------------ Arguments -----------------------------------------*/
@al_mezahe_pail integer,
@as_mispar_zihuy varchar(20), /* mispar t.z./darkon/teudat leda/teudat maavar */
@as_shem_mishpaha varchar(40),
@as_shem_prati varchar(30),
@al_mezahe_ona integer,
@ai_guf_peilut smallint,
@ai_sug_tafkid smallint,
@al_mezahe_merkaz integer,
@al_mezahe_moadon integer,
@al_mezahe_kvotza integer,
@ai_status_pail smallint
as
/*------------------------------------ Declare -------------------------------------------*/
declare
@ll_mezahe_pail integer,
@li_sug_zihuy smallint,
@ls_mispar_zihuy varchar(20),
@ls_tz varchar(20),
@ll_mezahe_zihuy integer,
@ls_shem_mishpaha varchar(40),
@ls_shem_prati varchar(30),
@ls_reshimat_moadonim varchar(1000),
@li_status_pail smallint,
@ls_shem_moadon varchar(40),
@ll_medinat_darkon integer,
@ls_shem_loazi_mekutzar varchar(3)
declare cur_pail cursor fast_forward read_only for
select distinct pail.mezahe_pail,
zihuy.sug_zihuy, /* t.z./darkon/teudat leda/teudat maavar */
zihuy.mispar_zihuy, /* mispar t.z./darkon/teudat leda/teudat maavar */
zihuy.tz,
zihuy.mezahe_zihuy,
IsNull(zihuy.shem_mishpaha, zihuy.english_shem_mishpaha),
IsNull(zihuy.shem_prati, zihuy.eng_shem_prati),
pail.status,
zihuy.medinat_darkon
from zihuy,
pail,
tafkid
where pail.zihuy_pail = zihuy.mezahe_zihuy and
tafkid.sug_yeshut = 7 /* pail */ and
tafkid.mezahe_yeshut = pail.mezahe_pail and
(@al_mezahe_pail is null or pail.mezahe_pail = @al_mezahe_pail) and
(@as_mispar_zihuy is null or zihuy.mispar_zihuy like @as_mispar_zihuy + '%') and
(@ai_status_pail is null or pail.status = @ai_status_pail) and
(
@as_shem_mishpaha is null or
zihuy.shem_mishpaha like @as_shem_mishpaha + '%' or
Upper(zihuy.english_shem_mishpaha) like Upper(@as_shem_mishpaha + '%')
) and
(
@as_shem_prati is null or
zihuy.shem_prati like @as_shem_prati + '%' or
Upper(zihuy.eng_shem_prati) like Upper(@as_shem_prati + '%')
) and
(@ai_sug_tafkid is null or tafkid.sug_tafkid = @ai_sug_tafkid) and
(@ai_guf_peilut is null or tafkid.guf_peilut = @ai_guf_peilut) and
(@al_mezahe_merkaz is null or tafkid.mezahe_merkaz = @al_mezahe_merkaz) and
(@al_mezahe_moadon is null or tafkid.mezahe_moadon = @al_mezahe_moadon) and
(@al_mezahe_kvotza is null or tafkid.mezahe_kvotza = @al_mezahe_kvotza) and
tafkid.mezahe_ona /* <- it's really mezahe_onat_hathala */ <= @al_mezahe_ona and
(tafkid.mezahe_onat_siyum >= @al_mezahe_ona or tafkid.mezahe_onat_siyum is null)
create table #recordset (
mezahe_pail integer,
mispar_zihuy varchar(20),
tz varchar(20),
mezahe_zihuy integer,
shem_male varchar(71),
shem_mishpaha varchar(40),
shem_prati varchar(30),
reshimat_moadonim varchar(1000),
status_pail smallint
)
/*----------------------------------- Begin logic ----------------------------------------*/
open cur_pail
while (1 = 1) begin
fetch from cur_pail into
@ll_mezahe_pail,
@li_sug_zihuy,
@ls_mispar_zihuy,
@ls_tz,
@ll_mezahe_zihuy,
@ls_shem_mishpaha,
@ls_shem_prati,
@li_status_pail,
@ll_medinat_darkon
if (@@fetch_status = -1) break
if (@@fetch_status = -2) continue
/*******************************************
Build the commas-delimited list of moadonim:
*******************************************/
exec get_get_moadonim_shel_pail @ll_mezahe_pail, @al_mezahe_ona, @ls_reshimat_moadonim output
/***************************************************************************************
If mispar_zihuy is darkon - add to it the country short code ("12345" ---> "USA 12345"):
***************************************************************************************/
if (@li_sug_zihuy = 2 /* darkon */) begin
select @ls_shem_loazi_mekutzar = shem_loazi_mekutzar
from medina
where mezahe_medina = @ll_medinat_darkon
select @ls_mispar_zihuy = @ls_shem_loazi_mekutzar + ' ' + @ls_mispar_zihuy
end
/******************************************
Include the current pail in the record set:
******************************************/
insert into #recordset values (
@ll_mezahe_pail,
@ls_mispar_zihuy,
@ls_tz,
@ll_mezahe_zihuy,
Rtrim(Ltrim(@ls_shem_mishpaha + ' ' + @ls_shem_prati)), /* shem_male */
@ls_shem_mishpaha,
@ls_shem_prati,
@ls_reshimat_moadonim,
@li_status_pail
)
end /* of cur_pail loop */
close cur_pail
deallocate cur_pail
/*------------------------------- Return record set --------------------------------------*/
select * from #recordset order by shem_male
drop table #recordset
/*------------------------------- End of procedure ---------------------------------------*/
GO
grant execute on get_d_pail_select to public
go
exec get_d_pail_select @al_mezahe_pail = null,
@as_mispar_zihuy = null,
@as_shem_mishpaha = 'ëäï',
@as_shem_prati = null,
@al_mezahe_ona = 4,
@ai_guf_peilut = null,
@ai_sug_tafkid = null,
@al_mezahe_merkaz = null,
@al_mezahe_moadon = null,
@al_mezahe_kvotza = null,
@ai_status_pail = null
T-SQL example 3
/*------------------------------------------------------------------------------------------
Procedure:
get_d_shofet_select
Description:
Returns record set for d_shofet_select.
Log:
Date Name Description
20.01.2003 Michael Zuskin Created
------------------------------------------------------------------------------------------*/
if exists (select name from sysobjects where name = 'get_d_shofet_select' and type = 'P')
drop procedure get_d_shofet_select
go
/*--------------------------------- Begin procedure --------------------------------------*/
create procedure get_d_shofet_select
/*------------------------------------ Arguments -----------------------------------------*/
@al_mezahe_ona integer,
@as_mispar_zihuy varchar(20), /* mispar t.z./darkon/teudat leda/teudat maavar */
@as_mispar_baigud varchar(20),
@as_shem_mishpaha varchar(40),
@as_shem_prati varchar(30),
@ai_derug_shofet smallint,
@ai_derug_ozer_shofet smallint,
@ai_derug_mevaker smallint,
@adec_tziun_memutza_from decimal(5,2),
@adec_tziun_memutza_to decimal(5,2),
@ai_status_shofet smallint,
@ai_status_rishui smallint
as
/*------------------------------------ Declare -------------------------------------------*/
declare
@ll_mezahe_shofet integer,
@ls_mispar_baigud varchar(20),
@ls_mispar_zihuy varchar(20),
@ls_tz varchar(9),
@ls_shem_mishpaha varchar(40),
@ls_shem_prati varchar(30),
@li_derug_shofet smallint,
@li_derug_ozer_shofet smallint,
@li_derug_mevaker smallint,
@ldec_tziun_memutza decimal(5,2),
@li_status_shofet smallint,
@li_status_rishui smallint,
@ll_medinat_darkon integer,
@ls_shem_loazi_mekutzar varchar(3)
declare cur_shofet cursor fast_forward read_only for
select shofet.mezahe_shofet,
shofet.mispar_baigud,
zihuy.mispar_zihuy,
zihuy.tz,
IsNull(zihuy.shem_mishpaha, zihuy.english_shem_mishpaha),
IsNull(zihuy.shem_prati, zihuy.eng_shem_prati),
shofet.status,
zihuy.medinat_darkon
from zihuy,
shofet
where shofet.mezahe_shofet > 0 and
-- shofet.mezahe_shofet = zihuy.mezahe_zihuy and
shofet.zihuy_shofet = zihuy.mezahe_zihuy and
(@as_mispar_baigud is null or shofet.mispar_baigud like @as_mispar_baigud + '%') and
(@as_mispar_zihuy is null or zihuy.mispar_zihuy like @as_mispar_zihuy + '%') and
(@ai_status_shofet is null or shofet.status = @ai_status_shofet) and
(
@as_shem_mishpaha is null or
zihuy.shem_mishpaha like @as_shem_mishpaha + '%' or
Upper(zihuy.english_shem_mishpaha) like Upper(@as_shem_mishpaha + '%')
) and
(
@as_shem_prati is null or
zihuy.shem_prati like @as_shem_prati + '%' or
Upper(zihuy.eng_shem_prati) like Upper(@as_shem_prati + '%')
)
create table #recordset (
mezahe_shofet integer,
mispar_zihuy varchar(20), /* mispar t.z./darkon/teudat leda/teudat maavar */
tz varchar(9),
mispar_baigud varchar(20),
shem_male varchar(71),
shem_mishpaha varchar(40),
shem_prati varchar(30),
derug_shofet smallint,
derug_ozer_shofet smallint,
derug_mevaker smallint,
tziun_memutza decimal(5,2),
status_shofet smallint,
status_rishui smallint)
/*----------------------------------- Begin logic ----------------------------------------*/
open cur_shofet
while (1 = 1) begin
fetch from cur_shofet into
@ll_mezahe_shofet,
@ls_mispar_baigud,
@ls_mispar_zihuy,
@ls_tz,
@ls_shem_mishpaha,
@ls_shem_prati,
@li_status_shofet,
@ll_medinat_darkon
if (@@fetch_status = -1) break
if (@@fetch_status = -2) continue
select @li_derug_shofet = null
select @li_derug_ozer_shofet = null
select @li_derug_mevaker = null
select @ldec_tziun_memutza = null
select @li_status_rishui = null
/**************
Get derug data:
**************/
select @li_derug_shofet = derug_shofet,
@li_derug_ozer_shofet = derug_ozer_shofet,
@li_derug_mevaker = derug_mevaker,
@ldec_tziun_memutza = tziun_memutza
from shofet_derug
where mezahe_shofet = @ll_mezahe_shofet and
mezahe_ona = @al_mezahe_ona and
(@ai_derug_shofet is null or derug_shofet = @ai_derug_shofet) and
(@ai_derug_ozer_shofet is null or derug_ozer_shofet = @ai_derug_ozer_shofet) and
(@ai_derug_mevaker is null or derug_mevaker = @ai_derug_mevaker) and
(@adec_tziun_memutza_from is null or tziun_memutza >= @adec_tziun_memutza_from) and
(@adec_tziun_memutza_to is null or tziun_memutza <= @adec_tziun_memutza_to)
if (@@rowcount = 0) begin
if (@ai_derug_shofet is not null) continue
if (@ai_derug_ozer_shofet is not null) continue
if (@ai_derug_mevaker is not null) continue
if (@adec_tziun_memutza_from is not null) continue
if (@adec_tziun_memutza_to is not null) continue
end
/***************
Get rishui data:
***************/
select @li_status_rishui = status
from shofet_rishui
where mezahe_shofet = @ll_mezahe_shofet and
mezahe_ona = @al_mezahe_ona and
(@ai_status_rishui is null or status = @ai_status_rishui)
if (@@rowcount = 0) begin
if (@ai_status_rishui is not null) continue
end
/***************************************************************************************
If mispar_zihuy is darkon - add to it the country short code ("12345" ---> "USA 12345"):
***************************************************************************************/
if (@ll_medinat_darkon is not null) begin
select @ls_shem_loazi_mekutzar = shem_loazi_mekutzar
from medina
where mezahe_medina = @ll_medinat_darkon
select @ls_mispar_zihuy = @ls_shem_loazi_mekutzar + ' ' + @ls_mispar_zihuy
end
/********************************************
Include the current shofet in the record set:
********************************************/
insert into #recordset values (
@ll_mezahe_shofet,
@ls_mispar_zihuy, /* mispar t.z./darkon/teudat leda/teudat maavar */
@ls_tz,
@ls_mispar_baigud,
Rtrim(Ltrim(@ls_shem_mishpaha + ' ' + @ls_shem_prati)), /* shem_male */
@ls_shem_mishpaha,
@ls_shem_prati,
@li_derug_shofet,
@li_derug_ozer_shofet,
@li_derug_mevaker,
@ldec_tziun_memutza,
@li_status_shofet,
@li_status_rishui
)
end /* of cur_shofet loop */
close cur_shofet
deallocate cur_shofet
/*------------------------------- Return record set --------------------------------------*/
select * from #recordset order by shem_male
drop table #recordset
/*------------------------------- End of procedure ---------------------------------------*/
go
grant execute on get_d_shofet_select to public
go
exec get_d_shofet_select null, null, null, null, null, null, null, null, null, null, null, null
go
T-SQL example 4
/*------------------------------------------------------------------------------------------
Procedure:
get_get_next_mispar_ratz
Returns (via ai_mispar_ratz output argument):
positive mispar ratz ===> success;
-1 ===> error;
-2 ===> the entity not found in mispar_ratz table.
Log:
Date Name Description
07.01.2003 Michael Zuskin Created
------------------------------------------------------------------------------------------*/
if exists (select name from sysobjects where name = 'get_get_next_mispar_ratz' and type = 'P')
drop procedure get_get_next_mispar_ratz
go
create procedure get_get_next_mispar_ratz
@as_shem_yeshut varchar(50),
@ai_mispar_ratz int output
as
begin transaction
-- Get next number and lock the row:
select @ai_mispar_ratz = 0
select @ai_mispar_ratz = mispar_haba
from mispar_ratz with (rowlock)
where shem_yeshut = @as_shem_yeshut
if (@@error != 0) begin
rollback
select @ai_mispar_ratz = -1
return
end
if (@ai_mispar_ratz = 0 /* the entity not found */) begin
select @ai_mispar_ratz = -2
return
end
-- Update next number:
update mispar_ratz
set mispar_haba = mispar_haba + 1
where shem_yeshut = @as_shem_yeshut
if (@@error != 0) begin
rollback
select @ai_mispar_ratz = -1
return
end
-- Finish:
commit
return
go
DateTime conversion and using it in PowerBuilder:
// From datetime to string:
Convert(Char(10), datetime_column, 103)
// From string to datetime:
Convert(DateTime, string_column, 103)
//===============================
// As a retrieval argument of DW:
//===============================
WHERE (Convert (DateTime, Convert (Char (10), loc.estimated_observation_date, 103),103)
>= Convert (DateTime, Convert (Char (10), :adt_estimated_date_from, 103),103)
OR :adt_estimated_date_from IS NULL)
AND
Convert (DateTime, Convert (Char (10), loc.estimated_observation_date, 103),103) <= :adt_estimated_date_to
//=======================================
// In dynamically built SELECT statement:
//=======================================
IF NOT IsNull(ldt_observation_date_from) THEN ls_where = ls_where + &
" AND Convert (DateTime, Convert (Char (10), rmsn_variety_observation.observation_date, 103),103) >= " + &
"Convert (DateTime, '" + String(ldt_observation_date_from, "dd/mm/yyyy") + "', 103)"
IF NOT IsNull(ldt_observation_date_to) THEN ls_where = ls_where + &
" AND Convert (DateTime, Convert (Char (10), rmsn_variety_observation.observation_date, 103),103) <= " + &
"Convert (DateTime, '" + String(ldt_observation_date_to, "dd/mm/yyyy") + "', 103)"