存储过程的一些小练习

 

新建存储过程

CREATE PROC Pro_GetNews
AS
SELECT * FROM T_News;
GO

执行存储过程

EXEC Pro_GetNews

利用存储过程添加数据

--参数名称一定要加@前缀
CREATE PROC Pro_GetNewsByTitle
@newstitle VARCHAR(64)
AS
SELECT NewsTitle,SUBSTRING(NewsContent,1,20)+'....' AS NewsContent,CreateTime FROM T_News
WHERE NewsTitle LIKE @newstitle;
GO

根据条件利用存储过程查询数据

--存储过程，根据与内容和标题匹配数据
CREATE PROC Pro_GetNewsByTitleAndContent
@newskey VARCHAR(8)
AS
SELECT T1.NewsTitle,SUBSTRING(T1.NewsContent,1,20)+'......' AS NewsContent,T1.CreateTime FROM T_News T1
WHERE T1.NewsTitle LIKE @newskey OR T1.NewsContent LIKE @newskey;
GO

EXEC Pro_GetNewsByTitleAndContent '%国%'
GO

新建存储过程并返回一个值

--新建存储过程，向新闻表插入一条数据，并返回自动生成的最大编号
CREATE PROC Pro_GetMaxId
@maxid INT OUT
AS
INSERT INTO T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)
VALUES
('本周全国大部分地区降温','本周全国大部分地区降温，最高降温达10°','003',GETDATE(),'39ec3206-4467-44b4-8fa1-711443eb4ce5');
SET @maxid=@@IDENTITY;
GO

DECLARE @maxid INT;
EXEC Pro_GetMaxId @maxid OUT;
PRINT @maxid;
GO

VS中调用存储过程显示数据并动态生成一个表

namespace 新闻系统
{
    public partial class cunchuguocheng : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataLoad();
        }
        private void DataLoad()
        {
           
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                string news = TextBox1.Text.Trim();
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Pro_NewsClass";
                    cmd.Parameters.Add(new SqlParameter("@News", "%" +news+ "%"));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    StringBuilder sb1 = new StringBuilder();
                    string newstitle = string.Empty;
                    string content = string.Empty;
                    string time = string.Empty;
                    sb1.Append("<table border=1>");
                    foreach (DataRow row in dt.Rows)
                    {
                        sb1.Append("<tr>");
                        newstitle = row["NewsTitle"].ToString();
                        content = row["NewsContent"].ToString();
                        time = row["CreateTime"].ToString();
                        //string a = row["@a"].ToString();
                        sb1.Append("<td>" + newstitle + "</td>");
                        sb1.Append("<td>"+content+"</td>");
                        sb1.Append("<td>" + time + "</td>");
                        sb1.Append("</tr>");
                       
                    }
                    sb1.Append("</table>");
                    divResult.InnerHtml = sb1.ToString();
                }
            }
        }

        protected void btnNewsClass_Click(object sender, EventArgs e)
        {
            DataLoad();
        }
    }
}