How to do Sql server procedure using parameters like where @para1 = @para2

Question

I have a procedure with a single select statement. I am need to create some 50 procedures like the one below..

create procedure foo1 as 
select cityid, cityname from footballteam 

the footballteam will be common in all my procedures, Instead of creating 50 single procedures, I want to code like below and send 3 parameters from my c# page

create procedure foo1 (@id bigint, @name varchar(50), @param bigint)as 
select @id, @name from footballtem where @id =@param

can i pass like this in sql server ?/ How to do like this

will I am able to do procedure overloading in sql server, some time I need to pass only two parameters and i want to get a particular value , I will pass three or more parameters ....

Answer 1

For a pure TSQL answer:

create table footballtem(id int identity(1,1),cityid int, cityname varchar(50))
go
insert footballtem(cityid, cityname) values (123, 'abc')
insert footballtem(cityid, cityname) values (456, 'def')
go
create procedure foo1 (@id sysname, @name sysname, @param bigint) as
declare @sql nvarchar(100) = 'select ' + QUOTENAME(@id) + ','
       + QUOTENAME(@name) + ' from footballtem where '
       + QUOTENAME(@id) + '=@param'
exec sp_ExecuteSql @sql, N'@param bigint', @param
go
exec foo1 'cityid','cityname',123

(credit is due to Mikael Eriksson re QUOTENAME)

Note that QUOTENAME makes the @name and @id injection safe.

Note also, though, that the varying parameter (@param) is safe from injection - we don't need to validate that anywhere; and that this will allow query-plan re-use via sp_ExecuteSql