Dear,

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.

https://help.k2.com/onlinehelp/k2blackpearl/devref/4.7/default.htm#Sample_Direct_Execution_Queries.html%3FTocPath%3DRuntime%2520APIs%2520and%2520Services%7CSmartObjects%7CADO.NET%2520Provider%7CADO%2520Query%2520Syntax%7C_____1

Regards,

A.Abdullah

Dear Abdhullah,

Thank you for the reply..

Please find the below sample procedure which i am following same syntax...

ALTER procedure rdbo].ogetrequiredvalues](@T decimal(18,2)=null,@T1 decimal(18,2)=null,@DT2 decimal(18,2)=null,@Item varchar(20)=null)
as
begin
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, '''', '''''') + ''')'

EXEC (@QUERY)

set @test1=(select top 1 COST from #TEMPCOST where CODE1=@Item)
set @test2=((@T1-@T2)*@CODE)
select coalesce(convert(decimal(18,2),substring(convert(varchar(20),@test2),1,15)),0) as TESTCOLUMN
end

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.

Dear,

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!

Regards.

Hello Chandru,

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.

Regards,

Vijay.