This sounds like a parameter sniffing issue, as it's differently behaved in SSMS and fixed by recompiling. There are two ways to easily get around parameter sniffing (perhaps more, but these are what I know). I ran into this issue and posted about it many years ago: http://www.sqlservercentral.com/Forums/Topic1055385-391-1.aspx (Though apparently I didn't follow up with the solution that was deployed, which was OPTIMIZE FOR UNKNOWN in this case, because this company had two customers, one of which had 99% of the data in all the tables). Wow, I'm SO glad I don't work there anymore. The query in that thread is the tip of the iceberg in the disaster that was going on. Moving on...

Assign the parameters to variables before using them in a WHERE clause. (This isn't a good example because this particular query won't cause different query plans based on parameter values, but it shows the technique)

CREATE PROCEDURE test(@p1 sysname)

AS

DECLARE @p1_copy sysname = @p1;

SELECT * FROM sys.columns c WHERE c.name = @p1_copy;

The other technique is to tell the optimizer to pretend that it doesn't know the value of the parameter when it first compiles a query plan:

CREATE PROCEDURE test2(@p1 sysname)

AS

SELECT * FROM sys.columns c WHERE c.name = @p1 OPTION (OPTIMIZE FOR (@p1 UNKNOWN));

Of course, RECOMPILE should also work, but you have to determine if it imposes its own performance hit.