I am trying to create Smartobject List method but its not showng ...
In the stored procedure i am using #Temp table concept to get data from ORACLE and insert into temp table, After insertion i am checking for validation. FInally the SP willl return true or false.
Sql server level its working fine and returning vlaue, But while converted the same to SO and checked, not showing List method.
Thanks in advance...
It would be better if you share the stored procedure you have... because i remember that the syntax of the stored procedure could be a little bit tricky.
some syntax might work on sql... but it won't be understandable for k2 service broker...
please find below link, it is very helpful.
Thank you for the reply..
Please find the below sample procedure which i am following same syntax...
ALTER procedure [dbo].[GETREQUIREDVALUES](@T decimal(18,2)=null,@T1 decimal(18,2)=null,@DT2 decimal(18,2)=null,@Item varchar(20)=null)
declare @test1 int
declare @test2 tNew decimal(18,2)
declare @test3 varchar(10)='TEST'
declare @QUERY NVARCHAR(max)
IF OBJECT_ID('tempdb..#TEMPCOST') IS NOT NULL DROP TABLE #TEMPCOST
CREATE TABLE #TEMPCOST (CODE1 VARCHAR(20),COST DECIMAL(18,4))
SET @QUERY ='Select CODE,NO from XXXXX.XXXXX_DETAILS
where CODE2= ''' +@test3+''' AND CODE3=''' +@Item +''''
SET @QUERY = N'INSERT INTO #TEMPCOST (CODE1,COST)
select CODE,NO from OPENQUERY(TESTPATCH, ''' + REPLACE(@INVQUERY, '''', '''''') + ''')'
set @test1=(select top 1 COST from #TEMPCOST where CODE1=@Item)
select coalesce(convert(decimal(18,2),substring(convert(varchar(20),@test2),1,15)),0) as TESTCOLUMN
for the above procedure, if i create Smartobject, its not showing LIST method (Showing EXECUTE method) but i am returning values from the procedure and same returning value if we exceute on SQL server.
As i remember and i hope it helps...
but i remember when using "if" statements i had the same issue...
try replacing your "statements" with "switch/case" statements...
somehow having a return inside an "if" doesn't show to the tester as "list"...
Hope this helps!
i've seen similar kind of issue when i'm working with temp tables.
Try replacing temp tables with parameters of Type Table and see if it works.