I use this method when I need a dynamic solution:
use AdventureWorks2000
go
if exists(select * from sysobjects where id = object_id('dbo.proc_dynamic') and type = 'P')
drop procedure dbo.proc_dynamic
go
create procedure dbo.proc_dynamic
@name varchar(50)
as
begin
select LocationId, [Name],...