Let me give this a shot. If I understand your post correctly, you are saying you want to use a stored procedure. You're practically there with what you have supplied. I would do the following:

if object_id('GetResults') is not null

drop procedure GetResults

go

create procedure GetResults

@PassedCriteria varchar(5) -- i'm just guessing at this length

as

declare @sql nvarchar(max)

declare @params nvarchar(max)

declare @criteria nvarchar(max)

set @PassedCriteria = '%' + @PassedCriteria + '%'

set @sql = 'select Descripcion as Des, CIF as CIF from tabla1 '

set @sql = @sql + ' where Descripcion like @Criteria '

set @sql = @sql + ' union '

set @sql = @sql + ' select Descripcion2 as Des, NULL as CIF from tabla2 '

set @sql = @sql + ' where Descripcion2 like @Criteria '

set @params = '@Criteria varchar(5)'

exec sp_executesql @sql, @params, @Criteria = @PassedCriteria

go

-- now test:

GetResults @PassedCriteria = 'a'

I hope this is what you're looking for and I haven't missed the mark.

Lisa