Stored proc does not display results

Question

The stored proc below does not display any results with SQL Server 2005. I can take the same instructions and run it as a query and I get results, What am I missing.

ALTER PROCEDURE [dbo].[usp_SubtractStops]

@p NVARCHAR(1024) = '209 208 207 206 205 204 203 113 297 19 7 12 11 6 232 233 234 235 236 237 273 271 272 210 211 212 213 214 215 247', 
@q NVARCHAR(1024) = '209 208 207 206 205 204 203 113 297 19 7 12 11 6 232 233 234 235 236 237'

--SET @p = '209 208 207 206 205 204 203 113 297 19 7 12 11 6 232 233 234 235 236 237 273 271 272 210 211 212 213 214 215 247'
--SET @q = '209 208 207 206 205 204 203 113 297 19 7 12 11 6 232 233 234 235 236 237'

AS
BEGIN

SET NOCOUNT ON;

  SELECT StoppingPattern, 
         Replace(StoppingPattern, @p, @q)
    FROM tLicTripStops
   WHERE ',' + @p + ',' LIKE '%,' + StoppingPattern + ',%' 
     AND LastItemID = 247

END

Answer 1

How are you calling usp_SubtractStops? Are you allowing the parameters to default?

Will work:

• usp_SubtractStops
• usp_SubtractStops DEFAULT, DEFAULT

Will not work:

• usp_SubtractStops 'DEFAULT', 'DEFAULT'
• usp_SubtractStops NULL, NULL
• usp_SubtractStops '', ''

Other ideas:

• column data types match? (implicit conversion somewhere)

Edit, after comment:

You need to change the generated SQL. When I do it for one of my stored procs, it has a "TODO" comment to set the parameters. You are passing in NULL because they are not set. Because it's an explicit NULL, then it's overriding the defaults you set in code.

The only SQL you need is this, I've commented out the unneeded stuff

DECLARE @RC int
--DECLARE @p nvarchar(1024)
--DECLARE @q nvarchar(1024)

-- TODO: Set parameter values here.

EXECUTE @RC = [MyDB].[dbo].[usp_SubtractStops] 
  -- @p
  --,@q