Empty Results?

Okay, so i have a procedure where i'm trying to build a left join, and for some
reason, if i store it into a procedure, it doesnt work, if i run it in query analyzer
i have no problem.

Manual Code:

create PROCEDURE search_orders_11

@SPSirvarchar(3)= null,
@debug bit = 0 AS

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)

if @spsir='1'
select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'

select @sql=@sql+' WHERE 1 = 1'

SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '



here is the procedure:

create PROCEDURE search_orders_11

@SPSirvarchar(3)= null,
@debug bit = 0 AS

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)

if @spsir='1'
select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'

select @sql=@sql+' WHERE 1 = 1'

SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '



here is the execution:


exec search_orders_11 @spsir=1



i get the whole command completed sucessfully, but no result. am i missing something here...