how to pass a string

[angelleynes]

Hello,

i have the following code from foxpro and sql, basically i need to know if i'm passing a string properly to my stored procedure in SQL, i'm trying to pass lcWhichWay variable which is a string and i don't get results but when i put "AND kbenen.abene=1" directly i get results. help pls.

thanks!

&&FoxPro
DO CASE
CASE piModule=1 && releases
lcWhichWay=" AND kbenen.rbene=1 "
CASE piModule=2
lcWhichWay=" AND kbenen.abene=1 "
OTHERWISE
&& noope
RETURN
ENDCASE

lcExeString="EXEC kpoalist '"+gcClient+"', '"+gcComp+"','" +lcWhichWay+"'"
llResult=SQL_EXEC(lcExeString,'quickiepoa')



&&SQL Stored procedure
CREATE PROCEDURE [dbo].[kpoalist]
@cClient char(6), @cCompkey char(4), @sModule varchar(30) AS

SELECT kpoa.poakey,kpoa.countycode,kpoadesc.poapick,benename=ISNULL(kbenen.benename,''),
kpoa.poarecord,kpoa.poainstr,kpoa.poabook,kpoa.poapage, countypick=ISNULL(countylookup.countypick,''),
state=ISNULL(countylookup.state,'')
FROM kpoa
INNER JOIN kpoadesc ON kpoa.poakey=kpoadesc.poakey
LEFT OUTER JOIN kbenen ON kbenen.poakey=kpoa.poakey
LEFT OUTER JOIN countylookup ON countylookup.countycode=kpoa.countycode
WHERE kbenen.client = @cClient
AND kpoadesc.companykey=@cCompkey
+ @sModule
GO

 

[Mike Lewis]

angelleynes,

I'm not sure if this is the answer to your problem, but it seems to me that you shouldn't be including commas in your EXEC command. In other words, take out the bits that insert the commas in the following command:

lcExeString="EXEC kpoalist '"+gcClient+"', '"+gcComp+"','" lcWhichWay+"'"

Perhaps you could give that a try and let us know the result.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Mike Lewis]

Also, what exactly is SQL_EXEC() ?

Did you mean SQLEXEC() - without the underscore? If so, you need to include the connection handle as the first parameter. Or is SQL_EXEC() your own function?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[angelleynes]

yeah, sql_exec is my own function written in vfp, it works fine when i include AND kbenen.rbene = 1 in my stored procedure but it won't get a result when i pass it as @sModule.

 

[Mike Lewis]

Did you try leaving out the commas?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[angelleynes]

i got around it, thanks!