October 4, 2010 at 11:31 am
DDL is as follows
CREATE TABLE [Agn_Info](
[ai_agn_seq] [int] NOT NULL,
[ai_type] [int] NULL,
[Ai_Name] [varchar](35) NULL,
[Ai_cust_id] [char](7) NULL,
[Ai_mac_id] [char](8) NULL,
[ai_doris_id] [char](8) NULL,
[ai_ts_id] [varchar](12) NULL,
[ai_lic_id] [varchar](12) NULL,
[Ai_attn] [varchar](35) NULL,
[Ai_Street1] [varchar](35) NULL,
[Ai_Street2] [varchar](35) NULL,
[Ai_City] [varchar](20) NULL,
[Ai_State] [char](2) NULL,
[Ai_Zipcode] [char](10) NULL,
[Ai_mailstreet1] [varchar](35) NULL,
[Ai_mailcity] [varchar](20) NULL,
[Ai_mailstate] [char](2) NULL,
[Ai_mailzipcode] [char](10) NULL,
[ai_language] [int] NULL,
[Ai_loc_phn] [char](12) NULL,
[Ai_fax_phn] [char](12) NULL,
[ai_fax_express] [char](12) NULL,
[Ai_800_phn] [char](12) NULL,
[ai_mdm_phn] [varchar](75) NULL,
[ai_time_zn] [int] NULL,
[ai_gmt_dif] [int] NULL,
[ai_str_tim] [char](8) NULL,
[ai_end_tim] [char](8) NULL,
[ai_sup_fax] [char](12) NULL,
[ai_sup_con] [varchar](35) NULL,
[Ai_updaddr] [datetime] NULL,
[ai_url] [varchar](50) NULL,
[ai_notes] [text] NULL,
[ai_supp_notes] [text] NULL,
[ai_office] [int] NULL,
[ai_acd_grp] [int] NULL,
[ai_def_pri] [smallint] NULL,
[ai_status] [int] NULL,
[ai_heading] [varchar](35) NULL,
[ai_conglom] [varchar](7) NULL,
[ai_dos] [char](5) NULL,
[ai_dos_ver] [char](5) NULL,
[ai_pcanypurch] [tinyint] NULL,
[ai_any_ver] [char](5) NULL,
[ai_netrempurch] [tinyint] NULL,
[ai_nr_ver] [char](4) NULL,
[ai_oppurch] [tinyint] NULL,
[ai_opsys] [varchar](15) NULL,
[ai_opver] [char](8) NULL,
[ai_op2purch] [tinyint] NULL,
[ai_op2sys] [varchar](15) NULL,
[ai_op2ver] [char](8) NULL,
[ai_ournet] [tinyint] NULL,
[ai_duplex] [tinyint] NULL,
[ai_server] [varchar](10) NULL,
[ai_serverpurch] [tinyint] NULL,
[ai_work_st] [varchar](11) NULL,
[ai_workstpurch] [tinyint] NULL,
[ai_faxservpurch] [tinyint] NULL,
[ai_fxboard] [tinyint] NULL,
[ai_asfaxve] [char](5) NULL,
[ai_asfaxcopies] [int] NULL,
[ai_remotemanager] [tinyint] NULL,
[ai_remotemanagercopies] [int] NULL,
[ai_backuppurch] [tinyint] NULL,
[ai_tb_type] [char](6) NULL,
[ai_faxsoftpurch] [tinyint] NULL,
[ai_citrixonmain] [tinyint] NULL,
[ai_winormeta] [char](1) NULL,
[ai_oppatch] [tinyint] NULL,
[ai_payterms] [int] NULL,
[ai_pendlegal] [tinyint] NULL,
[ai_baddebt] [tinyint] NULL,
[ai_skilllevel] [tinyint] NULL,
[ai_updated] [datetime] NULL,
[ai_referral] [tinyint] NULL,
[ai_startupkit] [tinyint] NULL,
[ai_old_cust_id] [char](7) NULL,
[ai_old_agn_seq] [int] NULL,
[ai_prospect_id] [char](10) NULL,
[ai_prospect_seq] [int] NULL,
[ai_county] [varchar](20) NULL,
[ai_incity] [tinyint] NULL,
[ai_selfserviceprovider] [tinyint] NULL,
[ai_qc_survey] [tinyint] NULL,
[ai_asp] [tinyint] NULL,
[ai_passcode] [varchar](20) NULL,
[ai_ts_update] [datetime] NULL,
[ai_unique] [int] NULL,
[ai_companycode] [varchar](5) NULL,
[ai_previouslease] [varchar](25) NULL,
[ai_currentlease] [varchar](25) NULL,
[ai_currentpermits] [varchar](25) NULL,
[ai_leaseexpires] [datetime] NULL,
[ai_leasename] [varchar](50) NULL,
[ai_leaseAddr1] [varchar](50) NULL,
[ai_leaseAddr2] [varchar](50) NULL,
[ai_leaseAddr3] [varchar](50) NULL,
[ai_leaseCity] [varchar](50) NULL,
[ai_leaseState] [varchar](50) NULL,
[ai_leaseZip] [varchar](10) NULL,
[ai_leaseUsers] [int] NULL,
[ai_webbilling] [tinyint] NULL,
[ai_betaclient] [tinyint] NULL,
[ai_ranking] [tinyint] NULL,
[ai_ranking_override] [tinyint] NULL,
[ai_territory_seq] [int] NULL,
[ai_route_seq] [int] NULL,
[ai_route_order] [int] NULL,
[ai_primary_seq] [int] NULL,
[ai_primary_name] [varchar](35) NULL,
[ai_ascnet] [tinyint] NULL,
[ai_alert] [tinyint] NULL,
[Ai_watch] [tinyint] NULL,
[Ai_ClientType] [varchar](1) NULL,
[Ai_Active] [tinyint] NULL,
CONSTRAINT [PK_agn_info] PRIMARY KEY CLUSTERED
(
[ai_agn_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [agn_payterms](
[pm_term_seq] [int] IDENTITY(1,1) NOT NULL,
[pm_term_name] [char](15) NULL,
[pm_active] [tinyint] NULL,
[pm_comment] [char](30) NULL,
[pm_company] [char](5) NULL
) ON [PRIMARY]
CREATE TABLE [agn_status](
[st_seq_num] [int] IDENTITY(6,1) NOT NULL,
[st_name] [varchar](35) NULL,
[st_active] [tinyint] NULL,
[st_comment] [varchar](30) NOT NULL,
[st_company] [char](5) NULL,
CONSTRAINT [PK_agn_status] PRIMARY KEY CLUSTERED
(
[st_seq_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [agn_agntype](
[at_type_seq] [int] IDENTITY(4,1) NOT NULL,
[at_agn_type] [char](15) NULL,
[at_active] [tinyint] NULL,
[at_comment] [varchar](30) NOT NULL,
[at_company] [char](5) NULL,
[at_require_prospect] [tinyint] NULL,
[at_right_seq] [int] NULL,
CONSTRAINT [PK_agn_agntype] PRIMARY KEY NONCLUSTERED
(
[at_type_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Agn_Language](
[alg_seq_num] [int] IDENTITY(1,1) NOT NULL,
[alg_language] [varchar](50) NULL,
[alg_active] [tinyint] NULL,
[alg_company] [char](5) NULL,
[alg_comment] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[alg_seq_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [agn_prod](
[ap_prd_seq] [int] NOT NULL,
[ap_cust_id] [char](7) NULL,
[ap_agn_seq] [int] NULL,
[ap_mac_id] [char](8) NULL,
[ap_mac_bil] [char](8) NULL,
[ap_mac_ship] [char](10) NULL,
[ap_type] [char](4) NULL,
[ap_item_number] [char](20) NULL,
[ap_old_item_number] [char](20) NULL,
[ap_main_type] [tinyint] NULL,
[ap_pol_idx] [char](12) NULL,
[ap_users] [smallint] NULL,
[ap_users_billed] [int] NULL,
[ap_no_supp] [tinyint] NULL,
[ap_ns_date] [datetime] NULL,
[ap_status] [int] NULL,
[ap_reinstated] [datetime] NULL,
[ap_version] [char](8) NULL,
[ap_cfg_ver] [char](8) NULL,
[ap_lic_exp] [datetime] NULL,
[ap_anv_mon] [tinyint] NULL,
[ap_basefee] [numeric](10, 2) NULL,
[ap_old_basefee] [numeric](10, 2) NULL,
[ap_taxrate] [numeric](8, 6) NULL,
[ap_taxfee] [numeric](10, 2) NULL,
[ap_old_taxfee] [numeric](10, 2) NULL,
[ap_paytype] [tinyint] NULL,
[ap_start] [datetime] NULL,
[ap_dos] [char](5) NULL,
[ap_dos_ver] [char](5) NULL,
[ap_network] [char](8) NULL,
[ap_net_ver] [char](8) NULL,
[ap_our_sys] [tinyint] NULL,
[ap_lsdsale] [tinyint] NULL,
[ap_usertype] [int] NULL,
[ap_tam_lic] [char](12) NULL,
[ap_note] [text] NULL,
[ap_ps] [char](4) NULL,
[ap_pc_any] [tinyint] NULL,
[ap_any_ver] [char](3) NULL,
[ap_upduser] [datetime] NULL,
[ap_def_grp] [int] NULL,
[ap_def_pri] [tinyint] NULL,
[ap_sup_off] [int] NULL,
[ap_recalc] [tinyint] NULL,
[ap_userdefined1] [varchar](35) NULL,
[ap_userdefined2] [varchar](35) NULL,
[ap_userdefined3] [varchar](35) NULL,
[ap_userdefined4] [varchar](35) NULL,
[ap_userdefined5] [varchar](35) NULL,
[ap_userdefined6] [varchar](35) NULL,
[ap_userdefined7] [varchar](35) NULL,
[ap_userdefined8] [varchar](35) NULL,
[ap_userdefined9] [varchar](35) NULL,
[ap_billcycle] [int] NULL,
[ap_old_billcycle] [int] NULL,
[ap_contract] [tinyint] NULL,
[ap_contract_number] [char](10) NULL,
[ap_old_contract_number] [char](10) NULL,
[ap_remotefee] [numeric](10, 2) NULL,
[ap_old_remotefee] [numeric](18, 0) NULL,
[ap_bill_type] [int] NULL,
[ap_exception] [tinyint] NULL,
[ap_exception_start] [datetime] NULL,
[ap_exception_expiration] [datetime] NULL,
[ap_exception_type] [int] NULL,
[ap_iowaexcept] [tinyint] NULL,
[ap_remotes] [tinyint] NULL,
[ap_next_bill_date] [datetime] NULL,
[ap_old_next_bill_date] [datetime] NULL,
[ap_last_bill_date] [datetime] NULL,
[ap_bill_fee] [numeric](10, 2) NULL,
[ap_old_bill_fee] [numeric](10, 2) NULL,
[ap_migrated] [datetime] NULL,
[ap_migrated_from] [char](4) NULL,
[ap_iso_fee] [numeric](18, 2) NULL,
[ap_ssp_fee] [numeric](18, 2) NULL,
[ap_new_pri] [tinyint] NULL,
[ap_pri_dat] [datetime] NULL,
[ap_applied_msoffice] [int] NULL,
[ap_unique] [int] IDENTITY(1,1) NOT NULL,
[ap_unique_backup] [int] NULL,
[ap_companycode] [varchar](5) NULL,
[ap_extended_signed] [datetime] NULL,
[ap_extended_through] [datetime] NULL,
[ap_extended_years] [tinyint] NULL,
[TimeStamp] [datetime] NOT NULL,
CONSTRAINT [PK_agn_prod_1] PRIMARY KEY CLUSTERED
(
[ap_prd_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [Agn_Territory](
[atr_seq_num] [int] IDENTITY(1,1) NOT NULL,
[atr_territory_name] [varchar](35) NULL,
[atr_company] [varchar](5) NULL,
CONSTRAINT [PK_agn_territory] PRIMARY KEY CLUSTERED
(
[atr_seq_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [agn_regionTerritory](
[art_id] [int] IDENTITY(1,1) NOT NULL,
[art_region] [int] NOT NULL,
[art_territory] [int] NOT NULL,
CONSTRAINT [PK_agn_regionterritory] PRIMARY KEY CLUSTERED
(
[art_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [agn_Regions](
[arg_id] [int] IDENTITY(1,1) NOT NULL,
[arg_region] [varchar](30) NOT NULL,
[arg_manager] [bigint] NULL,
[arg_company] [varchar](5) NULL,
CONSTRAINT [PK_agn_regions] PRIMARY KEY CLUSTERED
(
[arg_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [EmpList](
[el_unique] [int] IDENTITY(1,1) NOT NULL,
[el_emp_seq] [int] NOT NULL,
[el_fname] [varchar](30) NULL,
[el_lname] [varchar](30) NULL,
[el_ext] [char](5) NULL,
[el_fax] [char](12) NULL,
[el_pss_wrd] [char](12) NULL,
[el_dept] [char](8) NULL,
[el_build] [varchar](10) NULL,
[el_act_emp] [tinyint] NULL,
[el_mac_dpt] [char](6) NULL,
[el_acc_dpt] [char](9) NULL,
[el_division] [varchar](64) NULL,
[el_div_cod] [char](8) NULL,
[el_abra_sg] [int] NULL,
[el_hm_phn] [char](13) NULL,
[el_loc_phn] [char](12) NULL,
[el_hm_str1] [varchar](30) NULL,
[el_hm_str2] [varchar](30) NULL,
[el_hm_city] [varchar](30) NULL,
[el_hm_st] [char](2) NULL,
[el_hm_zip] [char](10) NULL,
[el_fname2] [varchar](30) NULL,
[el_lname2] [varchar](30) NULL,
[el_security] [tinyint] NOT NULL,
[el_intemail] [varchar](100) NULL,
[el_birthdate] [datetime] NULL,
[el_startdate] [datetime] NULL,
[el_displaybirthdate] [tinyint] NULL,
[el_company] [char](10) NULL,
[el_updated] [datetime] NULL,
[el_seniority] [datetime] NULL,
[el_terminated] [datetime] NULL,
[el_extemail] [varchar](65) NULL,
[el_employeetype] [char](3) NULL,
[el_vacationtype] [char](10) NULL,
[el_sex] [char](1) NULL,
[el_websiteid] [char](7) NULL,
[el_location] [varchar](12) NULL,
[el_supervisor_no] [char](9) NULL,
[el_supervisor_name] [varchar](62) NULL,
[el_update] [tinyint] NULL,
[el_update_security] [tinyint] NULL,
[el_username] [char](15) NULL,
[el_user8] [char](8) NULL,
[el_nextperf] [datetime] NULL,
[el_lastperf] [datetime] NULL,
[el_photo] [varchar](50) NULL,
[el_salarygrade] [varchar](12) NULL,
[el_personid] [varchar](50) NULL,
[el_jobtitle] [varchar](49) NULL,
[el_jobcode] [varchar](21) NULL,
[el_bucode] [varchar](10) NULL,
[el_posnid] [varchar](21) NULL,
[el_buname] [varchar](40) NULL,
[el_posnname] [varchar](88) NULL,
[el_fullname] [varchar](60) NULL,
[el_admin] [tinyint] NULL,
[el_tester] [tinyint] NOT NULL,
[el_ss] AS (CONVERT([varchar],[el_emp_seq],0)),
CONSTRAINT [PK__EmpList__0D7A0286] PRIMARY KEY CLUSTERED
(
[el_emp_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [UQ_el_user8] UNIQUE NONCLUSTERED
(
[el_user8] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I then simplified the stored procedure trying to test it.
New stored proc
CREATE PROCEDURE [usp_Client2]
@ClientSeq int=NULL,
@ClientName varchar(37)=NULL,
@Phone varchar(14)=NULL,
@GreatPlainsID varchar(10)=NULL,
@ClientID varchar(9)=NULL,
@ConglomerateID varchar(9)=NULL,
@ConglomerateFlag tinyint=NULL,
@License varchar(14)=NULL,
@State varchar(4)=NULL,
@Attrition varchar(1)=NULL,
@ClientType varchar(1)=NULL,
@Active tinyint=NULL
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @CMD nvarchar(max)
declare @SELECT nvarchar(max)
declare @WHERE nvarchar(max)
SELECT
CongFlag = CASE
WHEN count(cong.ai_conglom) > 1 THEN cast(1 AS BIT)
ELSE cast(0 AS BIT) END,
ai_conglom
INTO #Congs
FROM
PubData..agn_info cong
GROUP BY ai_conglom
SELECT atr_seq_num,
Territory = atr_territory_name,
Region = arg_region,
ManagerName = el_fullname
INTO #Terr
FROM pubdata..Agn_Territory TER
inner join pubdata..agn_regionTerritory REGT ON TER.atr_seq_num = REGT.art_territory
inner join pubdata..agn_regions AS REG ON REGT.art_region = REG.arg_id
inner join pubdata..emplist AS EMP ON REG.arg_manager = EMP.el_emp_seq
CREATE TABLE #Results(
SeqNumber INT NOT NULL,
ClientType VARCHAR(1),
ClientID VARCHAR(7),
ClientName VARCHAR(35),
GreatPlainsID VARCHAR(8),
ProspectSeq int,
ProspectID VARCHAR(10),
AccountTypeNO int,
AccountType VARCHAR(15),
StatusNO int,
STATUS VARCHAR(35),
AttentionName VARCHAR(35),
Heading VARCHAR(35),
ConglomerateID VARCHAR(7),
LocalPhone VARCHAR(12),
SecondPhone VARCHAR(12),
Fax VARCHAR(12),
URLAddress varchar(50),
PhysicalStreet varchar(35),
PhysicalCity varchar(20),
PhysicalState varchar(2),
PhysicalZip varchar(10),
MailingStreet varchar(35),
MailingCity varchar(20),
MailingState varchar(2),
MailingZip varchar(10),
LanguageNO int,
LANGUAGE varchar(50),
PayTerm int,
PayTermName varchar(35) ,
Alert tinyint,
Referral tinyint,
ASCNet tinyint,
BadDebt tinyint,
Legal tinyint,
ACTIVE tinyint,
LastUpdated datetime,
Watch tinyint,
CompanyCode varchar(5),
Territory varchar(35),
Region varchar(30),
ManagerName varchar(60),
ConglomerateFlag bit,
UserQuantity smallint,
ProductType varchar(4),
AgencyPassCode varchar(20),
StartTime varchar(8),
EndTime varchar(8),
GMTOffset INT
)
IF ISNULL(@ClientType,'C') = 'C'
BEGIN
SELECT
@SELECT = N'
INSERT INTO #Results
SELECT
SeqNumber = AI.ai_agn_seq,
ClientType = AI.Ai_ClientType,
ClientID = AI.Ai_cust_id,
ClientName = AI.Ai_Name,
GreatPlainsID = AI.Ai_mac_id,
ProspectSeq = AI.ai_prospect_seq,
ProspectID = AI.ai_prospect_id,
AccountTypeNO = AI.ai_type,
AccountType = at_agn_type,
StatusNO = AI.ai_status,
Status = st_name,
AttentionName = AI.Ai_attn,
Heading = AI.ai_heading,
ConglomerateID = AI.ai_conglom,
LocalPhone = AI.Ai_loc_phn,
SecondPhone = AI.Ai_800_phn,
Fax = AI.Ai_fax_phn,
URLAddress = AI.ai_url,
PhysicalStreet = AI.Ai_Street1,
PhysicalCity = AI.Ai_City,
PhysicalState = AI.Ai_State,
PhysicalZip = AI.Ai_Zipcode,
MailingStreet = AI.Ai_mailstreet1,
MailingCity = AI.Ai_mailcity,
MailingState = AI.Ai_mailstate,
MailingZip = AI.Ai_mailzipcode,
LanguageNO = AI.Ai_language,
Language = alg_language,
PayTerm = AI.ai_payterms,
PayTermName=PT.pm_term_name,
Alert = AI.ai_alert,
Referral = AI.ai_referral,
ASCNet = AI.ai_ascnet,
BadDebt = AI.ai_baddebt,
Legal = AI.ai_pendlegal,
Active = AI.Ai_Active,
LastUpdated = AI.ai_ts_update,
Watch = AI.ai_watch,
CompanyCode = AI.ai_companycode,
AREA.Territory,
AREA.Region,
AREA.ManagerName,
ConglomerateFlag = isnull(Cong.CongFlag, 0),
UserQuantity = ap_users,
ProductType = ap_type,
AgencyPassCode = AI.ai_passcode,
StartTime = AI.ai_str_tim,
EndTime = AI.ai_end_tim,
GMTOffset = AI.ai_gmt_dif
FROM PubData..Agn_Info AS AI
LEFT JOIN pubdata..agn_payterms PT ON AI.ai_payterms= PT.pm_term_seq
LEFT JOIN Pubdata..agn_status AS ST ON AI.ai_status = ST.st_seq_num
LEFT JOIN Pubdata..agn_agntype AS AT ON AI.ai_type = AT.at_type_seq
LEFT JOIN Pubdata..Agn_Language AS AL ON AI.ai_language = AL.alg_seq_num
LEFT JOIN Pubdata..agn_prod PROD ON PROD.ap_agn_seq = AI.ai_agn_seq
LEFT JOIN #Congs Cong ON AI.ai_conglom = Cong.ai_conglom and Ai.ai_cust_id = Cong.ai_conglom
LEFT JOIN #Terr AREA ON ai.ai_territory_seq = area.atr_seq_num
WHERE
ISNULL(prod.ap_main_type,1) = 1 '
SET @WHERE = N' AND 1=1 '
IF @ClientSeq IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N' AND ai.ai_agn_seq = @ClientSeq '
END
IF @ClientName IS NOT NULL
BEGIN
SET @clientName = @ClientName + '%'
SET @WHERE = @WHERE + N' AND AI.ai_name like @ClientName'
END
IF @Phone IS NOT NULL
BEGIN
SET @Phone = REPLACE(@Phone,'-','')
SET @Phone = REPLACE(@Phone,'(','')
SET @Phone = REPLACE(@Phone,')','')
SET @Phone = REPLACE(@Phone,' ','')
--SET @Phone = @Phone + '%'
--SET @WHERE = @WHERE + N' AND (AI.ai_loc_phn LIKE @Phone OR AI.ai_800_phn LIKE @Phone) '
SET @WHERE = @WHERE + N' AND REPLACE(ai.ai_loc_phn,''-'','''') LIKE @Phone + ''%'' '
END
IF @GreatPlainsID IS NOT NULL
BEGIN
SET @GreatPlainsID = @GreatPlainsID + '%'
SET @WHERE = @WHERE + N' AND AI.ai_mac_id like @GreatPlainsID '
END
IF @ClientID IS NOT NULL
BEGIN
SET @ClientID = @ClientID + '%'
SET @WHERE = @WHERE + N' AND AI.ai_cust_id LIKE @ClientID '
END
IF @ConglomerateID IS NOT NULL
BEGIN
SET @ConglomerateID = @ConglomerateID + '%'
SET @WHERE = @WHERE + N' AND ai.ai_conglom LIKE @ConglomerateID '
END
IF @License IS NOT NULL
BEGIN
SET @License = @License + '%'
SET @WHERE = @WHERE + N' AND EXISTS (SELECT 1 FROM agn_prod apd WHERE ai.ai_agn_seq = apd.ap_agn_seq and apd.ap_tam_lic LIKE @LicenseID) '
END
IF @State IS NOT NULL
BEGIN
SET @State = @State + '%'
SET @WHERE = @WHERE + N' AND (AI.ai_state like @State OR AI.ai_mailstate like @State) '
END
IF @Attrition IS NOT NULL
BEGIN
if @Attrition = 'A'
BEGIN
SET @WHERE = @WHERE + N' AND Alert = 1 '
END
if @Attrition = 'W'
BEGIN
SET @WHERE = @WHERE + N' AND Watch = 1 '
END
if @Attrition = 'B'
BEGIN
SET @WHERE = @WHERE + N' AND Alert = 1 OR Watch = 1 '
END
END
IF @Active IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N' AND Active = @xActive '
END
-- Don't return all clients if no search fields entered
IF @WHERE = N' AND 1=1 '
BEGIN
SET @WHERE = N' AND 1=0 '
END
SELECT @CMD = @SELECT + @WHERE
PRINT @CMD
EXEC sp_executesql @cmd,N'@ClientName varchar(37),
@ClientSeq int,
@Phone char(14),
@GreatPlainsID char(10),
@ClientID char(9),
@ConglomerateID varchar(9),
@ConglomerateFlag tinyint,
@License char(14),
@State char(4),
@Attrition char(1),
@ClientType char(1),
@Active tinyint',
@ClientName=@ClientName,
@ClientSeq=@ClientSeq,
@Phone=@Phone,
@GreatPlainsID=@GreatPlainsID,
@ClientID=@ClientID,
@ConglomerateID=@ConglomerateID,
@ConglomerateFlag=@ConglomerateFlag,
@License=@License,
@State=@State,
@Attrition=@Attrition,
@ClientType=@ClientType,
@Active=@Active
END
SELECT * FROM #Results
DROP TABLE #Congs
DROP TABLE #Terr
When I run it specifying a phone number it generates this dynamic query but no records returned.
If I take the dynamic query, create a variable varchar(20) called phone and then run it through REPLACE(@Phone,'-',''') followed by SET @Phone = @Phone + '%'
the query retunrs the single record I expect.
DECLARE @Phone VARCHAR(20)
SET @Phone = '800-555-1212'
SET @Phone = REPLACE(@Phone,'-','')
SET @Phone = @Phone + '%'
SELECT
SeqNumber = AI.ai_agn_seq,
ClientType = AI.Ai_ClientType,
ClientID = AI.Ai_cust_id,
ClientName = AI.Ai_Name,
GreatPlainsID = AI.Ai_mac_id,
ProspectSeq = AI.ai_prospect_seq,
ProspectID = AI.ai_prospect_id,
AccountTypeNO = AI.ai_type,
AccountType = at_agn_type,
StatusNO = AI.ai_status,
Status = st_name,
AttentionName = AI.Ai_attn,
Heading = AI.ai_heading,
ConglomerateID = AI.ai_conglom,
LocalPhone = AI.Ai_loc_phn,
SecondPhone = AI.Ai_800_phn,
Fax = AI.Ai_fax_phn,
URLAddress = AI.ai_url,
PhysicalStreet = AI.Ai_Street1,
PhysicalCity = AI.Ai_City,
PhysicalState = AI.Ai_State,
PhysicalZip = AI.Ai_Zipcode,
MailingStreet = AI.Ai_mailstreet1,
MailingCity = AI.Ai_mailcity,
MailingState = AI.Ai_mailstate,
MailingZip = AI.Ai_mailzipcode,
LanguageNO = AI.Ai_language,
Language = alg_language,
PayTerm = AI.ai_payterms,
PayTermName=PT.pm_term_name,
Alert = AI.ai_alert,
Referral = AI.ai_referral,
ASCNet = AI.ai_ascnet,
BadDebt = AI.ai_baddebt,
Legal = AI.ai_pendlegal,
Active = AI.Ai_Active,
LastUpdated = AI.ai_ts_update,
Watch = AI.ai_watch,
CompanyCode = AI.ai_companycode,
AREA.Territory,
AREA.Region,
AREA.ManagerName,
ConglomerateFlag = isnull(Cong.CongFlag, 0),
UserQuantity = ap_users,
ProductType = ap_type,
AgencyPassCode = AI.ai_passcode,
StartTime = AI.ai_str_tim,
EndTime = AI.ai_end_tim,
GMTOffset = AI.ai_gmt_dif
FROM PubData..Agn_Info AS AI
LEFT JOIN pubdata..agn_payterms PT ON AI.ai_payterms= PT.pm_term_seq
LEFT JOIN Pubdata..agn_status AS ST ON AI.ai_status = ST.st_seq_num
LEFT JOIN Pubdata..agn_agntype AS AT ON AI.ai_type = AT.at_type_seq
LEFT JOIN Pubdata..Agn_Language AS AL ON AI.ai_language = AL.alg_seq_num
LEFT JOIN Pubdata..agn_prod PROD ON PROD.ap_agn_seq = AI.ai_agn_seq
LEFT JOIN #Congs Cong ON AI.ai_conglom = Cong.ai_conglom and Ai.ai_cust_id = Cong.ai_conglom
LEFT JOIN #Terr AREA ON ai.ai_territory_seq = area.atr_seq_num
WHERE
ISNULL(prod.ap_main_type,1) = 1 AND 1=1 AND REPLACE(ai.ai_loc_phn,'-','') LIKE @Phone
What I don't understand is why it works when I run it the second time but not via the stored proc.
October 4, 2010 at 12:03 pm
Michael Valentine Jones (10/4/2010)
If the phone number is always stored in format 999-999-9999, then the code below should work without dynamic SQL.
Hi Michael, this would work except that I'm passing in 12 optional parameters to be used for a search. So I'm building a dynamic SQL Query based on which parameters are specified. Because of that, it needs to be dynamic. It should be parameterized as well to help prevent SQL Injection and to minimize the number of stored execution plans.
Even if I wound up with 12! execution plans in storage, that's better than storing 12! potential ones multiplied by the various values the variables can hold. Not to mention avoiding setting one of the longer string ones to something like
@ClientName = ' ');DROP TABLE agn_info;--'
October 4, 2010 at 12:20 pm
What I don't understand is why it works when I run it the second time but not via the stored proc.
As I explained above, I think the problem is varchar vs char. In your original script you define your @phone parameter in the sp_executesql to be char(14). When you run it seperately you are using a varchar.
An example to show the problem:
declare @string1 varchar(14),
@string2 char(14)
select @string1='9999999999' + '%'
select @string2=@string1
select '('+@string2+')'
So your like statement has trailing spaces after the %
October 4, 2010 at 2:31 pm
And you are of course correct.
I'm still a little upset that
'1234567890%' is not like '1234567890 '
though come to think of it... in the opposite order it wouldn't be... derrr.
This once again teaches me why I hate char data types... personally the overhead of varchar for small size text fields is worth it for me to not have to worry about padded strings.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply