oracle store procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

oracle store procedure

Author

Topic

Msandlana
Starting Member

33 Posts

Posted - 2008-06-26 : 04:59:59

Hi everyone I don't know if I'm going out of the line, my problen is that I have a store procedure to search store in SQL 2005 and I want to do the procedure in oracle which I have a little knowledge of oracle
ALTER Procedure [dbo].[SearchStore]--'pharmacy',18
(
@varSearch varchar(100),
@intProvinceId int
)
As
Declare @varSelectSql varchar(1000)
Declare @varSql varchar(3000)
Begin

Set @varSelectSql= 'Select * from vwStore Where 1 = 1'
Set @varSql = ''

If (@intProvinceId <> 0)
Begin
Set @varSelectSql = @varSelectSql + ' And ProvinceId = '+ cast(@intProvinceId as varchar(10))
End

If (@varSearch <> '')
Begin
Set @varSelectSql = @varSelectSql + ' And (Store Like ''%' + cast(@varSearch as varchar(50))+'%'''
Set @varSelectSql = @varSelectSql + ' Or AddressLine1 Like ''%' + cast(@varSearch as varchar(50))+'%'''
Set @varSelectSql = @varSelectSql + ' Or City like ''%' + cast(@varSearch as varchar(50))+'%'''
Set @varSelectSql = @varSelectSql + ' or StoreType like ''%' + cast(@varSearch as varchar(50))+'%'''+')'
End
Set @varSql = @varSelectSql
--Exec(@varSql)
Print(@varSql)
End

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 09:39:21

If you want to convert this to ORACLE code, post at Oracle forums such as www.orafaq.com

Madhivanan

Failing to plan is Planning to fail