i have modify sql code not seem working supposed to.
the sql code looks awful me, works part.
say had multiple vendors similar names: microsoft, microsoft corp, , microsoft, inc, etc.
all query returns microsoft, though existing code includes line pri_vendor_name '%' @pri_vendor_name '%' (or, @ least looks does).
i can't seem check see if code working because 1 big, nasty looking piece of code appending data long string execute.
current procedure: (get ready scream)
alter procedure [dbo].[getsignalmasterbyfilter] ( @planner varchar(50), @reorder int, @release int, @cmtted varchar(50), @partid varchar(50), @global_short_dt int, @pri_vendor_name varchar(50) ) begin declare @filter nvarchar(4000) set @filter = ' ' if @planner <> '' begin set @filter = ' , planner in(' + @planner + ')' end if @reorder = 1 begin set @filter = rtrim(@filter) + ' , (reorder_50 = ' + char(39) + 'y' + char(39) + ' ) ' end if @reorder = 2 begin set @filter = rtrim(@filter) + ' , (reorder_30 = ' + char(39) + 'y' + char(39) + ' ) ' end if @reorder = 3 begin set @filter = rtrim(@filter) + ' , (reorder_point = ' + char(39) + 'y' + char(39) + ' ) ' end --if @noaction = 1 --begin --set @filter = rtrim(@filter) + ' , reorder in (' + char(39) + 'excess' + char(39) + ',' + char(39) + 'watch' + char(39) + ')' --end if @release = 1 begin set @filter = rtrim(@filter) + ' , (release_50 = ' + char(39) + 'y' + char(39) + ' ) ' end if @release = 2 begin set @filter = rtrim(@filter) + ' , (release_30 = ' + char(39) + 'y' + char(39) + ' ) ' end if @release = 3 begin set @filter = rtrim(@filter) + ' , (release_point = ' + char(39) + 'y' + char(39) + ' ) ' end if @cmtted <> 'view all' begin set @filter = rtrim(@filter) + ' , cmtted > ' + char(39) + '0' + char(39) + ' , isnumeric(cmtted) = 1 ' end if @global_short_dt = 1 begin set @filter = rtrim(@filter) + ' , (global_short_dt not null or cast(cmtted int) > cast(on_hand int)) ' end if @global_short_dt = 2 begin set @filter = rtrim(@filter) + ' , (global_short_dt not null or cast(cmtted int) > cast(on_hand int)) , ((cast(qty_in_status float) + cast(on_order float) + cast(on_hand float)) < cast(cmtted int)) ' end if @partid <> '' begin set @filter = rtrim(@filter) + ' , partid like(' + char(39) + @partid + '%' + char(39) + ')' end if @pri_vendor_name <> '' begin set @filter = rtrim(@filter) + ' , pri_vendor_name like(' + char(39) + @pri_vendor_name + '%' + char(39) + ')' end declare @sql nvarchar(4000) set @sql = ' select distinct primary_vendor,case when pri_vendor_name null primary_vendor else primary_vendor +' + char(39) + ' - ' + char(39) + '+ pri_vendor_name end pri_vendor_name signalreportview primary_vendor not null ' + rtrim(@filter) + ' order pri_vendor_name' --print @sql exec sp_executesql @sql end what want replace nasty looking string variable i've started below, sql not strength isn't quite returning data yet:
my procedure version: not return data, appears cleaner , easier maintain in future.
alter procedure getsignalmasterbyfilter2( @planner varchar(50), @reorder int, @release int, @cmtted varchar(50), @partid varchar(50), @global_short_dt int, @pri_vendor_name varchar(50) ) begin select distinct primary_vendor, case when pri_vendor_name null primary_vendor else primary_vendor +' - '+ pri_vendor_name end pri_vendor_name signalreportview (primary_vendor not null) , ( isnull(@planner,0)=0 or planner in (@planner)) , ( (@reorder=1 , reorder_50='y') or (@reorder=2 , reorder_30='y') or (@reorder=3 , reorder_point='y') or (1=1) ) , ( (@release=1 , release_50='y') or (@release=2 , release_30='y') or (@release=3 , release_point='y') or (1=1) ) , ( (@cmtted='view all') or (0<cmtted , isnumeric(cmtted)=1) ) , ( ( (@global_short_dt=1) , ( (global_short_dt not null) or (cast(on_hand int) < cast(cmtted int)) ) ) or (1=1) ) , ( ( (@global_short_dt=2) , ( (global_short_dt not null) or ( (cast(on_hand int) < cast(cmtted int)) , ((cast(qty_in_status float) + cast(on_order float) + cast(on_hand float)) < cast(cmtted int)) ) ) ) or (1=1) ) , ( isnull(@partid,0)=0 or (partid '%'+@partid+'%') ) , ( isnull(@pri_vendor_name,0)=0 or (pri_vendor_name '%'+@pri_vendor_name+'%') ) order pri_vendor_name end so, question is:
is idea rewrite original script version should easier other developers maintain in future?
if no, can spot why existing sql not returning vendors?
if yes, can guide me design of version? not working - because have logic wrong. also, (1=1) clauses not set me, don't know way around them. since procedure not return data, can not use @ point.
i apologize not posting table structures, rather large, , stored procedure above queries nastier looking view (that can't follow).
try this:
alter procedure getsignalmasterbyfilter2( @planner varchar(50), @reorder int, @release int, @cmtted varchar(50), @partid varchar(50), @global_short_dt int, @pri_vendor_name varchar(50) ) begin select distinct primary_vendor, case when pri_vendor_name null primary_vendor else primary_vendor +' - '+ pri_vendor_name end pri_vendor_name signalreportview primary_vendor not null , ( @planner null or @planner = '' or planner in (@planner)) , ( @reorder not in (1,2,3) or (@reorder=1 , reorder_50='y') or (@reorder=2 , reorder_30='y') or (@reorder=3 , reorder_point='y') ) , ( @release not in (1,2,3) or (@release=1 , release_50='y') or (@release=2 , release_30='y') or (@release=3 , release_point='y') ) , ( @cmtted='view all' or 0<cmtted , isnumeric(cmtted)=1 ) , ( @global_short_dt not in (1,2) or (global_short_dt not null , @global_short_dt=1 , cast(on_hand int) < cast(cmtted int)) or (global_short_dt not null , @global_short_dt=2 , cast(on_hand int) < cast(cmtted int) , (cast(qty_in_status float) + cast(on_order float) + cast(on_hand float)) < cast(cmtted int)) ) , ( @partid null or @partid = '' or partid '%'+@partid+'%' ) , ( @pri_vendor_name null or @pri_vendor_name = '' or pri_vendor_name '%'+@pri_vendor_name+'%' ) order pri_vendor_name end i think have fixed logic mistakes, don't have tables not tested.
as performance, you'd have check both versions , see. there no guarantee either way.
Comments
Post a Comment