>Hi, all...
>
>I'm seenig some rather strange things in using SQLEXEC to call a stored proc in SQL Server 2000. It may be a lack of understanding on my part. Here goes...
>
>We have a 'generic' stored proc that accepts four parameters...a field list, a file list, a join clause, and a group by. Our VFP app builds those four variables at runtime (they are highly dynamic based on the sales hierarchy level a user choses), and then passes them on using SQLEXEC, like the following
>
>nResult = SQLEXEC(nMyConn,[EXEC DBO.GENERICQUERY ?cFieldList, ?cfileList, ?cJoin, ?cGroupBy],'myresultcursor')
>
>The Stored Proc called GENERICQUERY then builds a SQL string and does an EXECUTE. (Some may be laughing at this, but our query strings can be so dynamic that it just seems impossible to write straight T-SQL code...so we use VFP to build the string and pass it to SQL Sever).
>
>The problem is that sometimes the CFLDLIST or CJOIN can be much longer than 255 characters. When this happens, the query doesn't work. I've tried to break it out into strings, but it still doesn't work. (Maybe I have the syntax wrong, I'm not sure.
>
>All I know is that if I use straight Pass-Through to run the query, it works. But if I try to call my Generic SP, it doesn't. (I also wonder if I'm really losing any performance with straight Pass-Through instead of calling my Generic SP, since the Generic SP is getting variables all the time and thus has me wondering if it can possibly optimize an execution plan any better than straight pass-through in this instance).
>
>Any thoughts/clues/suggestions?
>
>Thanks,

Hi Kevin,

You don't gain any performance by using SP with dynamicaly ecxecuted sql because an execution plan cannot be reused in case like that.

Can you post the relevant part of your stored procedure?