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

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 pas开发者_JS百科s 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 ....

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

No; that would do a comparison on the parameter values, and return the parameter values. To do that, you would have to substitute the values at the caller, for example:

string idColumn = "id", nameColumn = "name";
string tsql = string.Format(@"
create procedure foo1 (@param bigint)
as select [{0}], [{1}] from footballtem where [{0}]=@param", idColumn,nameColumn);

and have 50 SPs; you can do the same in TSQL, using sp_ExecuteSQL against an already replaced string, but IMO it would be better to do this at the app tier than inside the database.

Also; question whether you really need stored procedures... that one isn't really going to help much; a parameterised TSQL query is much simpler, just as fast, and easier to deploy.

I'm not sure if I understand you correctly, but you can specify a default value for a stored procedure parameter in T-SQL. So you can omit it while calling.

CREATE PROCEDURE Proc1 @param1 int, @param2 int = -1 AS SELECT case when @param2=-1 then somefield else @param2 end as column from sometable where somekeyfield=@param1; GO

(assuming MS SQL Server)

MS SQL server does not support procedure overloading (as Oracle Does) but does support input and output parameters like this:

create procedure foo1 (
     @param bigint
    , @id bigint out
    , @name varchar(50) out

   )as 

select 
 @id = fbt.id
,@name = fbt.name 
from 
   footballteam fbt 
where fbt.id =@param

@id and @name have to be passed in as null value output paramters of the correct type. After execution (cmd.executeNonQuery) you can inspect the command object to get the new parameter values back out.

I am not sure I am reading your question correctly, but if I am then this should get what you want..

*Adding better code sample after question *

//_assumes the following using statements at the top of code file:_
//using System.Data;
//using System.Data.SqlClient;
     public string getTeam(int CityID)
          {
             string name;

             using (var cmd = new SqlCommand("foo1",new SqlConnection("myConnectionStringGoesHere")))
             {
                cmd.Parameters.Add(new SqlParameter("@param", CityID));
                cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt){Direction=ParameterDirection.Output});
                cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar,50) { Direction = ParameterDirection.Output });

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();

                name = cmd.Parameters["@name"].Value.ToString();

                cmd.Connection.Close();

             }

             return name;
          }

I think you were asking for the following:

create procedure foo1 (@id bitint out, @name bigint out, @param bigint)
as
select @id=cityid, @name=cityname from footballteam where teamname = @param

But your question makes it seem like you are trying to dynamically change the column names per query.

There is a way to do overloading on MSSQL. Here how it goes:

For example we have a sp_Personel procedure which takes personel type as parameter and lists personel of that type.

CREATE PROCEDURE [dbo].[sp_Personel]
           @PersonelType int
AS
SELECT Name, JoinDate, PersonelType, Salary
FROM Personel
WHERE PersonelType = @PersonelType
END

Now, you want another procedure which will be for personel join dates.

CREATE PROCEDURE [dbo].[sp_Personel];2
           @JoinDate datetime
AS
SELECT Name, JoinDate, PersonelType, Salary
FROM Personel
WHERE JoinDate <= @JoinDate 
END

To call second procedure from management studio;

[dbo].[sp_Personel];2 N'9/26/2010'