help on rewriting stored procedure

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 2000 Forums

Transact-SQL (2000)

help on rewriting stored procedure

Author

Topic

msfoxy
Starting Member

2 Posts

Posted - 2007-05-25 : 02:26:03

Hi,

I have a stored procedure that goes like this:

CREATE PROCEDURE usp_GetAdvancedResults

@networkname varchar(50),
@make varchar(50),
@po varchar(50),
@multiassetno varchar(8000),
@multisiteid varchar(8000),
@multicc varchar(8000),
@multisno varchar(8000),
@multidnno varchar(8000),

AS

declare @sql varchar(8000)

set @sql='
select active
,case when audit_date = ''4000-01-01'' then null else audit_date end as audit_date
,capacity
,capacity_unit
,catalogue_id
,cdm_type
,company
,cost_centre
,currency
,depreciation_units
,dnnumber
,case when entered_date = ''4000-01-01'' then null else entered_date end as entered_date
,extension_number
,firmware
,floor
,case when install_date = ''4000-01-01'' then null else install_date end as install_date
,job_number
,lease_months
,lease_rate
,lease_schedule_no
,case when lease_start_date = ''4000-01-01'' then null else lease_start_date end as lease_start_date
,lease_type
,location_id
,devicem1.logical_name
,maint_annual_cost
,maint_contact_mobile
,maint_contact_name
,maint_contact_phone
,maint_contract_number
,maint_coverage
,case when maint_end_date = ''4000-01-01'' then null else maint_end_date end as maint_end_date
,maint_name
,make
,model
,model_type
,network_address
,network_identifier
,network_name
,operating_system
,operating_system_version
,owner_user
,parent_id
,case when purchase_date = ''4000-01-01'' then null else purchase_date end as purchase_date
,purchase_order
,purchase_price
,reference_no
,resource_unit_code
,serial_no
,site_id
,spare_capacity
,subtype
,task_number
,type
,updated_by
,usageprgn
,war_contact_email
,war_contact_mobile
,war_contact_name
,war_contact_phone
,war_duration
,war_level
,war_number
,case when war_start_date = ''4000-01-01'' then null else war_start_date end as war_start_date
,war_vendor
,wets_code

from devicem1
inner join devicem2 on devicem1.logical_name = devicem2.logical_name
where
active=''t'' '
+
CASE WHEN @networkname='' THEN ''
WHEN @networkname='@@' THEN ' AND network_name is null'
WHEN @networkname='$$' THEN ' AND network_name is not null'
WHEN charindex('^',@networkname)>0 THEN ' AND network_name not like ''' + replace(@networkname,'^','') + '%'''
WHEN charindex('*',@networkname)>0 THEN ' AND network_name like ''' + replace(@networkname,'*','%') + ''''
ELSE ' AND network_name like ''' + @networkname + '%''' END
+
CASE WHEN @make='AA' THEN ''
WHEN @make='@@' THEN ' AND make is null'
WHEN @make='$$' THEN ' AND make is not null'
WHEN charindex('^',@make)>0 THEN ' AND make not like ''' + replace(@make,'^','') + '%'''
WHEN charindex('*',@make)>0 THEN ' AND make like ''' + replace(@make,'*','%') + ''''
ELSE ' AND make like ''' + @make + '%''' END
+
CASE WHEN @po='' THEN ''
WHEN @po='@@' THEN ' AND purchase_order is null'
WHEN @po='$$' THEN ' AND purchase_order is not null'
WHEN charindex('^',@po)>0 THEN ' AND purchase_order not like ''' + replace(@po,'^','') + '%'''
WHEN charindex('*',@po)>0 THEN ' AND purchase_order like ''' + replace(@po,'*','%') + ''''
ELSE ' AND purchase_order like ''' + @po + '%''' END

+
CASE WHEN @assetno='@@' THEN ' AND devicem1.logical_name is null'
WHEN @assetno='$$' THEN ' AND devicem1.logical_name is not null'
WHEN charindex('^',@assetno)>0 THEN ' AND devicem1.logical_name not like ''' + replace(@assetno,'^','') + '%'''
WHEN charindex('*',@assetno)>0 THEN ' AND devicem1.logical_name like ''' + replace(@assetno,'*','%') + ''''
WHEN (@multiassetno<>'' and @assetno<>'') THEN ' AND (devicem1.logical_name in (select str from dbo.iter_charlist_to_table(''' + @multiassetno + ''',DEFAULT)) or devicem1.logical_name like ''%' + @assetno + '%'')'
WHEN (@assetno='' and @multiassetno<>'') THEN ' AND devicem1.logical_name in (select str from dbo.iter_charlist_to_table(''' + @multiassetno + ''',DEFAULT))'
WHEN (@assetno<>'' and @multiassetno='') THEN ' AND devicem1.logical_name like ''%' + @assetno + '%'''
ELSE '' END
+
CASE WHEN @site_id='@@' THEN ' AND site_id is null'
WHEN @site_id='$$' THEN ' AND site_id is not null'
WHEN charindex('^',@site_id)>0 THEN ' AND site_id not like ''' + replace(@site_id,'^','') + '%'''
WHEN charindex('*',@site_id)>0 THEN ' AND site_id like ''' + replace(@site_id,'*','%') + ''''
WHEN (@multisiteid<>'' and @site_id<>'') THEN ' AND (site_id in (select str from dbo.iter_charlist_to_table(''' + @multisiteid + ''',DEFAULT)) or site_id like ''%' + @site_id + '%'')'
WHEN (@site_id='' and @multisiteid<>'') THEN ' AND site_id in (select str from dbo.iter_charlist_to_table(''' + @multisiteid + ''',DEFAULT))'
WHEN (@site_id<>'' and @multisiteid='') THEN ' AND site_id like ''%' + @site_id + '%'''
ELSE '' END

+
CASE WHEN @cc='@@' THEN ' AND cost_centre is null'
WHEN @cc='$$' THEN ' AND cost_centre is not null'
WHEN charindex('^',@cc)>0 THEN ' AND cost_centre not like ''' + replace(@cc,'^','') + '%'''
WHEN charindex('*',@cc)>0 THEN ' AND cost_centre like ''' + replace(@cc,'*','%') + ''''
WHEN (@multicc<>'' and @cc<>'') THEN ' AND (cost_centre in (select str from dbo.iter_charlist_to_table(''' + @multicc + ''',DEFAULT)) or cost_centre like ''%' + @cc + '%'')'
WHEN (@cc='' and @multicc<>'') THEN ' AND cost_centre in (select str from dbo.iter_charlist_to_table(''' + @multicc + ''',DEFAULT))'
WHEN (@cc<>'' and @multicc='') THEN ' AND cost_centre like ''%' + @cc + '%'''
ELSE '' END

+
CASE WHEN @serialno='@@' THEN ' AND serial_no is null'
WHEN @serialno='$$' THEN ' AND serial_no is not null'
WHEN charindex('^',@serialno)>0 THEN ' AND serial_no not like ''' + replace(@serialno,'^','') + '%'''
WHEN charindex('*',@serialno)>0 THEN ' AND serial_no like ''' + replace(@serialno,'*','%') + ''''
WHEN (@multisno<>'' and @serialno<>'') THEN ' AND (serial_no in (select str from dbo.iter_charlist_to_table(''' + @multisno + ''',DEFAULT)) or serial_no like ''%' + @serialno + '%'')'
WHEN (@serialno='' and @multisno<>'') THEN ' AND serial_no in (select str from dbo.iter_charlist_to_table(''' + @multisno + ''',DEFAULT))'
WHEN (@serialno<>'' and @multisno='') THEN ' AND serial_no like ''%' + @serialno + '%'''
ELSE '' END
+
CASE WHEN @dnno='@@' THEN ' AND dnnumber is null'
WHEN @dnno='$$' THEN ' AND dnnumber is not null'
WHEN charindex('^',@dnno)>0 THEN ' AND dnnumber not like ''' + replace(@dnno,'^','') + '%'''
WHEN charindex('*',@dnno)>0 THEN ' AND dnnumber like ''' + replace(@dnno,'*','%') + ''''
WHEN (@multidnno<>'' and @dnno<>'') THEN ' AND (dnnumber in (select str from dbo.iter_charlist_to_table(''' + @multidnno + ''',DEFAULT)) or dnnumber like ''%' + @dnno + '%'')'
WHEN (@dnno='' and @multidnno<>'') THEN ' AND dnnumber in (select str from dbo.iter_charlist_to_table(''' + @multidnno + ''',DEFAULT))'
WHEN (@dnno<>'' and @multidnno='') THEN ' AND dnnumber like ''%' + @dnno + '%'''
ELSE '' END

exec(@sql)
GO

I would like to be able to rewrite it as this sql statement is limited only to 8000 characters. I tried:

select * from devicem1
where active='t'
and network_name = case when @networkname = '@@' then network_name is null
when @networkname = '$$' then network_name is not null

But the above is obviously wrong. Can somebody give me ideas on how to accomplish this.

Thanks a lot for any help.

Kristen
Test

22859 Posts

Posted - 2007-05-25 : 03:25:27

[code]
and (
(@networkname = '@@' AND network_name is null)
OR (@networkname = '$$' AND network_name is NOT null)
OR ...
)
[/code]
I don't think you need Dynamic SQL for the above job, so you are on the right path, but if you do, and its longer than 8000 characters, then you could have a look at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Execute+dynamic+SQL+that+is+longer

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources