简单程序

if exists(select * from sysobjects where name='myproc')
drop proc myproc
go
CREATE PROCEDURE myproc
@tablename varchar(20),
@zid varchar(20)
as
set nocount on
begin
 exec('select * from ' +@tablename+' where '+@zid)
end
go
--exec myproc 'article',' z_id>5'

 

 SqlCommand com = new SqlCommand();
        com.Connection = DBModel.Getcon();
        com.CommandText = "myproc";
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter parame1 = new SqlParameter("@tablename", SqlDbType.VarChar);
        parame1.Value = "article";
        SqlParameter parame = new SqlParameter("@zid",SqlDbType.VarChar);
        parame.Value = "z_id > 5";
        com.Parameters.Add(parame);
        com.Parameters.Add(parame1);
        com.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataSet ds = new DataSet();
        da.Fill(ds);
        this.GridView1.DataSource = ds;
        this.GridView1.DataBind();

带有返回值的存储过程用调用：

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='mytest')
DROP PROCEDURE mytest
go
create PROCEDURE mytest(@tablename varchar(200), @row int output)
as
begin
SET NOCOUNT ON;
declare  @Sql NVARCHAR(2000)
declare @count int
  begin
  set @Sql='select @count=count(*) from ' +@tablename;
  EXEC SP_EXECUTESQL @Sql,N'@count int output',@count=@row output;
  end
SET NOCOUNT OFF;
end
go
declare @row int
exec mytest 'tips',@row output
print @row

 

 SqlCommand com = new SqlCommand();
        com.Connection = DBModel.Getcon();
        com.CommandText = "mytest";
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter param = new SqlParameter("@tablename", SqlDbType.VarChar);
        param.Direction = ParameterDirection.Input;
        param.Value = "tips";
        SqlParameter param1= new SqlParameter("@row", SqlDbType.Int);
        param1.Direction = ParameterDirection.Output;
        com.Parameters.Add(param1);
        com.Parameters.Add(param);

        com.ExecuteNonQuery();
        int count = (int)com.Parameters["@row"].Value;
        this.TextBox1.Text = count.ToString();