当前位置:文档之家› 使用C#创建SQL Server的存储过程

使用C#创建SQL Server的存储过程

使用C#创建SQL Server的存储过程
使用C#创建SQL Server的存储过程

使用C#创建SQL Server的存储过程

介绍

通常,开发人员使用的是T-SQL来创建SQL Server的存储过程、函数和触发器。而现在的SQL Server 2005已经完全支持.NET通用语言运行时(CLR)了。这就意味着,你可以使用.NET的语言,如C#、https://www.doczj.com/doc/622018554.html,之类的来开发SQL Server 的存储过程、函数和触发器。 SQL Server 和 CLR 的集成给我们带来了n多好处,如实时编译、类型安全、增强的安全性以及增强的编程模型等。本文中,我将向大家演示如何使用C#创建SQL Server的存储过程。

背景

我们在使用SQL Server存储过程时,最常做的工作就是从数据库中读取或保存数据。其常用应用如下:

·执行一些简单的逻辑,没有任何返回值。也没有输出参数。

·执行一些逻辑,并通过一个或更多的输出参数返回结果。

·执行一些逻辑,并返回从表中读取的一条或多条记录。

·执行一些逻辑,并返回一行或多行记录。这些记录不是从表中读取的,而是你自定义的一些数据行。

为了演示如何用C#开发出这几种应用的SQL Server存储过程,我将一个一个地举出示例。

启用CLR集成

在你开始用C#写存储过程之前,必须要启用你的SQL Server的CLR集成特性。默认情况它是不启用的。打开你的SQL Server Management Studio并执行如下脚本。

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

这里,我们执行了系统存储过程“sp_configure”,为其提供的两个参数分别为:“clr enabled”和“1”。如果要停用CLR集成的话也是执行这个存储过程,只不过第二个参数要变为“0”而已。另外,为了使新的设置产生效果,不要忘记调用“RECONFIGURE”。

SQL Server项目

现在打开Visual Studio,并从文件菜单中选择“新建项目”。在“新建项目”对话框中选择“Visual C#”下的“Database”。然后选择“SQL Server 项目”模板。

起好项目名称后就单击“确定”按钮。

很快,你所创建的项目就要求你选择一个SQL Server数据库。

按照提示一步一步地做就好了,就算你选择了取消,也可以在“项目”–“属性”对话框中再一次选择数据库。举个例子,假如你的电脑上有一个Northwind 数据库,那么就在“新建数据库引用”对话框中选中它,然后单击“确定”按钮。之后,SQL Server项目在部署的时候就会将我们开发的存储过程写入这个数据库(继续往后看你就清楚是怎么回事了)。

接下来,右键单击你新建的这个项目,选择“添加”-“存储过程”。然后将会出现如下图所示的对话框:

选择“存储过程”模板,并起一个合适的名字,然后单击“添加”按钮。

添加完后你就会发现,实际上这是创建了一个已经导入了需要用到的命名空间的类。

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

注意一下加粗显示的命名空间(译者注:后两个using)。

System.Data.SqlTypes命名空间包含了很多不同的类型,它们可以用来代替SQL Server的数据类型。 Microsoft.SqlServer.Server命名空间下的类负责SQL Server的CLR集成。

没有返回值的存储过程

在这一节中,我们将会看到如何写一个执行了一些逻辑,但是却没有任何返回值和输出参数的存储过程。在这个例子里,我们将创建一个名为“ChangeCompanyName”的存储过程,它用来修改Customers表中CompanyName 字段的值。这个存储过程需要两个参数– CustomerID(需要更改公司名称的客户的ID)和CompanyName(新的公司名称)。“ChangeCompanyName”存储过程完成后的代码如下:

[SqlProcedure]

public static void ChangeCompanyName

(SqlString CustomerID, SqlString CompanyName)

{

SqlConnection cnn = new SqlConnection

("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.doczj.com/doc/622018554.html,mandText = "update customers set

companyname=@p1 where customerid=@p2";

SqlParameter p1 = new SqlParameter("@p1", CompanyName); SqlParameter p2 = new SqlParameter("@p2", CustomerID);

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

int i=cmd.ExecuteNonQuery();

cnn.Close();

SqlContext.Pipe.Send(i.ToString());

}

仔细看一下这个ChangeCompanyName()方法。它是一个静态方法并且没有返回值(void)。它需要两个名为CustomerID和CompanyName的参数。请注意这两个参数的数据类型都是SqlString。 SqlString可以用来代替SQL Server 中的nvarchar数据类型。这个方法用了一个[SqlProcedure]属性来修饰。该属性用于标记ChangeCompanyName()方法是一个SQL Server存储过程。

在方法内我们创建了一个SqlConnection对象,并设置其连接字符串为“context connection = true”。“上下文连接”可以让你使用当前登录到数据库的用户作为你的登录数据库的验证信息。本例中,ChangeCompanyName()方法将会转换为存储过程,然后保存到Northwind数据库里。所以在这里的“上下文连接”指的就是Northwind数据库。这样你就不需要再写任何关于登录数据库的验证信息了。

接下来是打开数据库连接。然后通过设置SqlCommand对象的Connection和CommandText属性,让其执行更新操作。同时,我们还需要设置两个参数。这样通过调用ExecuteNonQuery()方法就可以执行更新操作了。再接下来就是关闭连接。

最后,将ExecuteNonQuery()方法的返回值发送到客户端。当然你也可以不做这一步。现在我们来了解一下SqlContext类的使用。 SqlContext类用于在服务端和客户端之间传递处理结果。本例使用了Send()方法发送一个字符串返回给调用者。

返回从表中读取的一条或多条记录的存储过程

我们在使用存储过程时,经常会SELECT一条或多条记录。你可以采用两种方法来创建这样的存储过程。

首先我们创建一个名为GetAllCustomers()的方法,代码如下:

[SqlProcedure]

public static void GetAllCustomers()

{

SqlConnection cnn = new SqlConnection

("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.doczj.com/doc/622018554.html,mandText = "select * from customers";

SqlDataReader reader = cmd.ExecuteReader();

SqlContext.Pipe.Send(reader);

reader.Close();

cnn.Close();

}

这个GetAllCustomers()方法用了一个[SqlProcedure]属性来修饰。在方法内创建一个SqlConnection和一个SqlCommand对象。然后使用ExecuteReader()方法来执行SELECT语句。接下来用Send()方法将取得的SqlDataReader数据发送到客户端。最后就是关闭SqlDataReader和SqlConnection。在这种方法中,是我们自己创建的SqlDataReader。其实,我们也可以把这个任务交给SqlContext类去完成,代码如下:

[SqlProcedure]

public static void GetCustomerByID

(SqlString CustomerID)

{

SqlConnection cnn = new SqlConnection

("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.doczj.com/doc/622018554.html,mandText = "select * from customers

where customerid=@p1";

SqlParameter p1 = new SqlParameter("@p1", CustomerID);

cmd.Parameters.Add(p1);

SqlContext.Pipe.ExecuteAndSend(cmd);

cnn.Close();

}

GetCustomerByID()方法需要一个参数– CustomerID,它将从Customers表中返回某个customer的记录。这个方法内的代码,除了ExecuteAndSend()方法外,你应该都已经比较熟悉了。 ExecuteAndSend()方法接收一个SqlCommand 对象作为参数,执行它就会返回数据集给客户端。

有输出参数的存储过程

我们在使用存储过程时,经常会通过输出参数返回一个经过计算的值。所以,现在让我们来看一看如何创建具有一个或多个输出参数的存储过程。

[SqlProcedure]

public static void GetCompanyName

(SqlString CustomerID,out SqlString CompanyName)

{

SqlConnection cnn = new SqlConnection

("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.doczj.com/doc/622018554.html,mandText = "select companyname from

customers where customerid=@p1";

SqlParameter p1 = new SqlParameter

("@p1", CustomerID);

cmd.Parameters.Add(p1);

object obj = cmd.ExecuteScalar();

cnn.Close();

CompanyName = obj.ToString();

}

这是一个名为GetCompanyName()的方法,它需要两个参数。第一个参数是CustomerID,它是一个输入参数;第二个参数是CompanyName,它是一个输出参数(用关键字out来指明)。这两个参数都是SqlString类型的。GetCompanyName()方法会接收一个CustomerID参数,然后返回CompanyName(作为输出参数)。

该方法内的代码首先设置了SqlConnection和SqlCommand对象。然后,使用ExecuteScalar()方法来执行SELECT语句。 ExecuteScalar()方法返回的值是一个object类型,它其实就是公司名称。最后将输出参数CompanyName设置为这个值

返回一行或多行自定义数据的存储过程

我们在使用存储过程时,更多的还是从某些表中读取数据。但是,某些情况下我们需要的数据可能不在任何表里。例如,你可能会基于某些计算来生成一个数据表格。因为它的数据不是从表中获得的,所以上面的方法就不在适用了。幸运的是,SQL Server的CLR集成特性给我们提供了一个解决这个问题的方法。请看如下代码:

[SqlProcedure]

public static void GetCustomRow()

SqlMetaData[] metadata = new SqlMetaData[2];

metadata[0] = new SqlMetaData

("CustomerID", SqlDbType.NVarChar,50);

metadata[1] = new SqlMetaData

("CompanyName", SqlDbType.NVarChar,50);

SqlDataRecord record = new SqlDataRecord(metadata);

record.SetString(0, "ALFKI");

record.SetString(1, "Alfreds Futterkiste");

SqlContext.Pipe.Send(record);

}

GetCustomRow()方法会返回一条记录并发送给客户端。这个方法首先声明了一个SqlMetaData对象。当你要用到自定义列的时候,就可以使用这个SqlMetaData类。在我们的示例中,创建了两个类型为NVarChar,长度为50的列。然后创建了一个SqlDataRecord对象。 SqlDataRecord类可以用来表示一个自定义行。它的构造函数需要一个SqlMetaData数组作为参数。SqlDataRecord对象的SetString()方法用来设置列的值。另外,还有许多不同的类似SetString()这样的方法,可以用来处理不同的数据类型。最后,调用Send()方法将SqlDataRecord对象发送到客户端。

在上面的示例中,我们只返回了一行数据给调用者。那么,如果要返回多行呢?请看下面的代码:

[SqlProcedure]

public static void GetMultipleCustomRows()

{

SqlMetaData[] metadata = new SqlMetaData[2];

metadata[0] = new SqlMetaData

("CustomerID", SqlDbType.NVarChar, 50);

metadata[1] = new SqlMetaData

("CompanyName", SqlDbType.NVarChar, 50);

SqlDataRecord record = new SqlDataRecord(metadata);

SqlContext.Pipe.SendResultsStart(record);

record.SetString(0, "ALFKI");

record.SetString(1, "Alfreds Futterkiste");

SqlContext.Pipe.SendResultsRow(record);

record.SetString(0, "ANATR");

record.SetString(1, "Ana Trujillo Emparedados y helados"); SqlContext.Pipe.SendResultsRow(record);

SqlContext.Pipe.SendResultsEnd();

}

GetMultipleCustomRows()方法将会返回多个SqlDataRecord对象到客户端。接下来创建自定义列和设置列的值都和之前的例子一样。但是,我们使用的是SendResutlsStart()方法来传输数据。 SendResultsRow()方法也是发送一个SqlDataRecord对象到客户端,但是我们可以多次调用它,从而做到发送多条记录。最后,调用SendResultsEnd()方法用来标记已经完成数据传输操作。

我们已经开发完了存储过程。现在就可以将这个项目编译为一个程序集

(.DLL)。但是我们的工作并没有到此结束。我们还需要部署这个程序集和存储过程到SQL Server数据库。有两种方法可以完成这个工作–手动和自动。手动方法是使用T-SQL语句注册你的程序集,并将存储过程部署到SQL Server 数据库中。在本例中,我将使用自动的方法来部署存储过程到SQL Server数据库。

右键单击你的项目,然后在菜单中选择“部署”选项。

如此就会自动地完成注册程序集和部署存储过程的工作。注意,只有在你创建项目时添加了数据库引用的时候,才会出现“部署”选项。如果因为某些原因你没能添加数据库引用,那么你可以通过项目属性对话框来设置它。

如果你在SQL Server Management Studio查看Northwind数据库的话,那么就应该可以看到和下图相似的结果。

注意,在存储过程节点下出现了我们创建的所有方法(有“锁”图标的),并且在程序集节点下出现了我们的程序集。

就是这些东西,很简单吧。现在你就可以在你的程序中调用这些存储过程了。你也可以在SQL Server Management Studio中来测试它们。

SQLserver数据库课程设计范例

1 概述 1.1课题简介 书店书目书种繁多,来源多样,购买者众多,图书信息、供应商信息、客户信息、销售信息庞大,不易管理。因此,很有必要创建一个小型书店管理系统,以便于书店对图书的管理。1.2设计目的 应用对数据库系统原理的理论学习,通过上机实践的方式将理论知识与实践更好的结合起来,巩固所学知识。 数据库应用课程实践:实践和巩固在课堂教学中学习有关知识,熟练掌握对于给定结构的数据库的创建、基本操作、程序系统的建立和调试以及系统评价。 数据库原理软件设计实践:实践和巩固在课堂教学中学习的关于关系数据库原理的有关知识和数据库系统的建立方法,熟练掌握对于给定实际问题,为了建立一个关系数据库信息管理系统,必须得经过系统调研、需求分析、概念设计、逻辑设计、物理设计、系统调试、维护以及系统评价的一般过程,为毕业设计打下基础。 1.3设计内容 运用基于E-R 模型的数据库设计方法和关系规范化理论做指导完成从系统的分析到设计直至系统的最终实现,开发小型书店管理系统,完成小型书店管理系统的全部功能。 首先做好需求分析,并完成数据流图和数据字典。 其次做概念分析,利用实体联系的方法将需求分析的用户需求抽象为信息结构,得到E-R 图。然后就是逻辑结构设计,将E-R 图转换为计算机系统所支持的逻辑模型 2 需求分析 2.1功能分析 首先,建立一些基本表(尽可能满足3N),对大部分基本信息组合、存储;其次通过建立视图实现对冗余数据的有必要保留(查询并计算基本表属性得到新的作为视图属性)并实现对以下基本信息的显示。 图书信息:图书名称、订购数量、订购时间、订购单价、金额、出版社名称、作者名称;供应商名称等; 供应商信息:供应商名称、地址、电话,联系人; 客户信息:客户编号、名称、年龄、性别、累计购书金额等; 销售信息:时间、销售名称、数量、销售单价、客户编号、客户名称、金额等。 在此基础上进行以下目标查询,由于有些查询常用且较复杂,为了简化其应用,所以将它们定义

存储过程的典型例子

可能有不少朋友使用SQL SERVER做开发也已经有段日子,但还没有或者很少在项目中使用存储过程,或许有些朋友认为根本没有必要使用存储过程等等。其实当你一个项目做完到了维护阶段时,就会发现存储过程给我们带来了好处了,修改方便,不能去改我们的应用程序,只需要改存储过程的内容,而且还可以使我们的程序速度得到提高。 QUOTE: SQL SERVER 联机丛书中的定义: 存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。 可以创建一个过程供永久使用,或在一个会话中临时使用(局部临时过程),或在所有会话中临时使用(全局临时过程)。 也可以创建在 Microsoft SQL Server 启动时自动运行的存储过程。 要使用存储过程,首先我们必需熟悉一些基本的T-SQL语句,因为存储过程是由于一组T-SQL语句构成的,并且,我们需要了解一些关于函数、过程的概念,因为我们需要在应用程序中调用存储过程,就像我们调用应用程序的函数一样,不过调用的方法有些不同。 下面我们来看一下存储过程的建立和使用方法。 一、创建存储过程 和数据表一样,在使用之前我们需要创建存储过程,它的简明语法是: QUOTE: CREATE PROC 存储过程名称 [参数列表(多个以“,”分隔)] AS SQL 语句 例: QUOTE: CREATE PROC upGetUserName @intUserId INT, @ostrUserName NVARCHAR(20) OUTPUT -- 要输出的参数 AS BEGIN -- 将uName的值赋给 @ostrUserName 变量,即要输出的参数 SELECT @ostrUserName=uName FROM uUser WHERE uId=@intUserId END 其中 CREATE PROC 语句(完整语句为CREATE PROCEDURE)的意思就是告诉SQL SERVER,

SQLServer存储过程返回值总结.

SQLServer 存储过程返回值总结 1. 存储过程没有返回值的情况 (即存储过程语句中没有 return 之类的语句用方法 int count = ExecuteNonQuery(..执行存储过程其返回值只有两种情况 (1假如通过查询分析器执行该存储过程,在显示栏中假如有影响的行数,则影响几行 count 就是几 (2假如通过查询分析器执行该存储过程, 在显示栏中假如显示 ' 命令已成功完成。 ' 则 count = -1;在显示栏中假如有查询结果,则 count = -1 总结:A.ExecuteNonQuery(该方法只返回影响的行数,假如没有影响行数,则该方法的返回值只能是 -1,不会为 0。 B.不论 ExecuteNonQuery(方法是按照 CommandType.StoredProcedure 或者 CommandType.Text 执行, 其效果和 A 一样。 --------------------------------------------------------------------------------------------------------------------------------------------------- 2. 获得存储过程的返回值 --通过查询分析器获得 (1不带任何参数的存储过程 (存储过程语句中含有 return ---创建存储过程 CREATE PROCEDURE testReturn AS return 145 GO ---执行存储过程

DECLARE @RC int exec @RC=testReturn select @RC ---说明 查询结果为 145 (2带输入参数的存储过程 (存储过程语句中含有 return ---创建存储过程 create procedure sp_add_table1 @in_name varchar(100, @in_addr varchar(100, @in_tel varchar(100 as if(@in_name = '' or @in_name is null return 1 else begin insert into table1(name,addr,tel values(@in_name,@in_addr,@in_tel return 0

SQL存储过程实例

题目1 1、学校图书馆借书信息管理系统建立三个表: 学生信息表:student 图书表:book 借书信息表:borrow 请编写SQL语句完成以下的功能: 1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、 学生名称、图书编号、图书名称、借出日期;参考查询结果如下图所示: 2)查询所有借过图书的学生编号、学生名称、专业;参考查询结果如下图所示:

3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期; 参考查询结果如下图所示: 4)查询目前借书但未归还图书的学生名称及未还图书数量;参考查询结果如下图所 示: 附加:建表语句:

标准答案:

题目2 程序员工资表:ProWage 创建一个存储过程,对程序员的工资进行分析,月薪1500到10000不等,如果有百分之五十的人薪水不到2000元,给所有人加薪,每次加100,再进行分析,直到有一半以上的人大于2000元为止,存储过程执行完后,最终加了多少钱? 例如:如果有百分之五十的人薪水不到2000,给所有人加薪,每次加100元,直到有一半以上的人工资大于2000元,调用存储过程后的结果如图:

请编写T-SQL来实现如下功能: 1)创建存储过程,查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000 元之上,如果不到分别每次给每个程序员加薪100元,至之一半程序员的工资达到2200,3000,3500,4000,5000或6000元。 2)创建存储过程,查询程序员平均工资在4500元,如果不到则每个程序员每次加200元, 至到所有程序员平均工资达到4500元。 建表语句

sql server存储过程详细代码(超赞!)

use jxgl --首先判断有没有已经建立up_getallstudents存储过程,有则先删除 if exists (select name from sysobjects where name='up_getallstudents'and type ='p') drop procedure up_getallstudents --编写存储过程up_getallstudents,用于获取学生表students的所有记录 create procedure up_getallstudents as select*from students --使用execute执行存储过程up_getallstudents exec up_getallstudents --也可写成 execute up_getallstudents --编写一个存储过程up_insertstudent,完成学生表students数据的插入 --1、不带默认值的参数 create procedure up_insertstudent @sid varchar(15),@sname varchar(30),@ssex char(10), @sbirth datetime,@sbirthplace varchar(300) as begin insert into students (stu_id,stu_name,stu_sex,stu_birth,stu_birthplace) values (@sid,@sname,@ssex,@sbirth,@sbirthplace) end exec up_insertstudent'200712110111','肖玉峰','男','1975-02-05','山东省滕州市木石镇' --等同于 exec up_insertstudent @sname='肖玉峰',@sid='200712110111',@ssex='男',@sbirth= '1975-02-05',@sbirthplace='山东省滕州市木石镇' drop procedure up_insertstudent delete students where stu_name='肖玉峰'

SQLserver数据库管理系统需求分析

SQLserver数据库管理系统需求分析 ——成绩管理分析 一、概述 二、SQLserver简介及知识介绍 三、数据库管理系统知识 四、需求分析—成绩管理 一、概述 成绩管理系统可以实现对成绩的管理,在此系统里可以查询、添加、删除学生的成绩,方便用户的管理。学生成绩管理系统是应对学生人数增多、信息量增大的问题,实现管理的现代化、网络化,逐步摆脱当前学生成绩管理系统的人工管理方式,提高成绩管理效率而开发的。希望该程序能够解决学生信息存储、学生成绩查询、录入还有课程查询等一系列功能,并提供了对各功能模块的查询和更新功能,且这两种功能基本上是通过存储过程来实现的,其中学生成绩查询和学生信息查询是成绩管理系统的重点。 二、SQLserver简介及知识介绍 1、简介 美国Microsoft公司推出的一种关系型数据库系统。SQLServer是一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统,实现了与WindowsNT的有机结合,提供了基于事务的企业级信息管理系统方案。 其主要特点如下: (1)高性能设计,可充分利用WindowsNT的优势。 (2)系统管理先进,支持Windows图形化管理工具,支持本地和远程的系统管理和配置。

(3)强壮的事务处理功能,采用各种方法保证数据的完整性。 (4)支持对称多处理器结构、存储过程、ODBC,并具有自主的SQL语言。 SQLServer以其内置的数据复制功能、强大的管理工具、与Internet的紧密集成和开放的系统结构为广大的用户、开发人员和系统集成商提供了一个出众的数据库平台。 2、SQLserver的发展 SQL语句可以用来执行各种各样的操作,例如更新数据库中的数据,从数据库中提取数据等。目前,绝大多数流行的关系型数据库管理系统,如Oracle,Sybase,Microsoft SQL Server,Access等都采用了SQL语言标准。虽然很多数据库都对SQL语句进行了再开发和扩展,但是包括Select,Insert,Update,Delete,Create,以及Drop在内的标准的SQL命令仍然可以被用来完成几乎所有的数据库操作。 SQL Server 是一个关系数据库管理系统。它最初是由Microsoft Sybase 和Ashton-Tate 三家公司共同开发的,于1988 年推出了第一个OS/2 版本。在Windows NT 推出后,Microsoft 与Sybase 在SQL Server 的开发上就分道扬镳了,Microsoft 将SQL Server 移植到Windows NT系统上,专注于开发推广SQL Server 的Windows NT 版本。Sybase 则较专注于SQL Server 在UNIX操作系统上的应用。 SQL Server 2000 是Microsoft 公司推出的SQL Server 数据库管理系统,该版本继承了SQL Server 7.0 版本的优点,同时又比它增加了许多更先进的功能。具有使用方便可伸缩性好与相关软件集成程度高等优点,可跨越从运行Microsoft Windows 98 的膝上型电脑到运行Microsoft Windows 2000 的大型多处理器的服务器等多种平台使用。 3、SQL Server 2008的新功能及知识介绍 (一)、可信任的

SqlServer存储过程基本语法

动态语句基本语法 1 :普通SQL语句可以用exec执行 Select * from tableName exec('select * from tableName') exec sp_executesqlN'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL declare @fnamevarchar(20) set @fname = 'FiledName' Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。exec('select ' + @fname + ' from tableName') -- 请注意加号前后的单引号的边上加空格 当然将字符串改成变量的形式也可 declare @fnamevarchar(20) set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3. 输出参数 declare @numint, @sqlsnvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中? declare @numint, @sqlsnvarchar(4000) set @sqls='select @a=count(*) from tableName ' execsp_executesql @sqls,N'@aint output',@num output select @num 1 :普通SQL语句可以用Exec执行例: Select * from tableName Exec('select * from tableName')

(Oracle管理)SQL中调用ORACLE存储过程

SQL Server 调用Oracle的存储过程收藏 原文如下: 通过SQL Linked Server 执行Oracle 存储过程小结 1 举例 我们可以通过下面的方法在SQL Server中通过Linked Server 来执行Oracle 存储过程。 (1) Oracle Package PACKAGE Test_PACKAGE AS TYPE t_t is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER; PROCEDURE Test_procedure1 ( p_BATCH_ID IN VARCHAR2, p_Number IN number, p_MSG OUT t_t, p_MSG1 OUT t_t ); END Test_PACKAGE; PACKAGE BODY Test_PACKAGE AS PROCEDURE Test_procedure1 ( p_BATCH_ID IN VARCHAR2, p_Number IN number, p_MSG OUT t_t, p_MSG1 OUT t_t ) AS BEGIN p_MSG(1):='c'; p_MSG(2):='b'; p_MSG(3):='a';

p_MSG1(1):='abc'; RETURN; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END Test_procedure1; END Test_PACKAGE; (2) 在SQL Server中通过Linked Server 来执行Oracle 存储过程 declare @BatchID nvarchar (40) declare @QueryStr nvarchar (1024) declare @StatusCode nvarchar(100) declare @sql nvarchar(1024) set @BatchID='AAA' SET @QueryStr='{CALL GSN. Test_PACKAGE.Test_procedure1('''''+@BatchID +''''',''''4'''',{resultset 3, p_MSG},{resultset 1, p_MSG1})}' (3)执行结果 (a) select @sql='SELECT @StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,'' '+@QueryStr+''')' exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCod e output print @StatusCode 答案:@StatusCode=’a’ (b) select @sql='SELECT top 3 @StatusCode=p_msg FROM OPENQUERY (HI4DB

Sqlserver2005数据库练习题(1)

Sqlserver2005数据库练习题 第1章数据库技术基础 1.简述数据库、数据库管理系统、数据库系统的含义? 2.简述数据库、数据库管理系统、数据库系统之间的联系? 3.简单描述层次模型、网状模型、关系模型的含义? 4.简述一个学生都包含哪些属性? 5.对于一个学生表来说学生姓名是否可以作为主键,为什么? 6.描述你所在的班级都有哪些实体? 第2章安装Sql Server 2005与服务器管理 1.安装sqlserver 2005需要计算机的最低配置是什么? 2.x86操作系统是什么概念? 3.x64操作系统是什么概念? 4.安装数据库时应该选择什么样的操作系统? 5.开始安装时需要有哪些准备工作? 6.开始安装界面中的其他信息是什么意思? 7.用户许可协议必须为接受么? 8.用户许可协议是否可以打印? 9.系统配置界面中需要检查哪些项? 10.详细信息中哪些项要求必须安装成功才能进行下一步的操作? https://www.doczj.com/doc/622018554.html,+目录指的是什么? 12.iis功能要求指的是什么? 13.mdac版本的最低要求是什么? 14.实例名中的默认实例是什么含义? 15.实例名中的命名实例是什么含义? 16.服务器名指的是什么? 17.服务帐户中使用内置系统帐户是什么含义? 18.服务帐户中使用域帐户是什么含义? 19.安装结束时启动服务包括什么? 20.sqlserver2005提供的服务有哪些? 21.安装结束后,哪些服务要求必须启动? 22.ms sqlserver是什么服务? 23.sql server agent是什么服务? 24.sql server analysis services是什么服务? 25.sql server browser是什么服务? 26.如何对服务进行启动、暂停和停止操作? 27.启动数据库时需要做什么工作? 28.如何注册数据库? 29.如何删除注册的服务? 30.如何更改数据库的登陆模式?

SQLServer的简介及发展历程

S Q L S e r v e r的简介及发展历程SQL简介 SYSTEMR开发的一种查询语言,它的前身是SQUARE语言。SQL语言结构简洁,功能强大,简单易学,所以自从IBM 语言作为查询语言。 织,负责开发美国的商务和通讯标准。ANSI同时也是ISO和InternationalElectrotechnicalCommission(IEC)的 ANSI随之发布的相应标准是ANSISQL-92。ANSISQL-92有时被称为ANSISQL。尽管不同的关系数据库使用的SQL版本有一些差异,但大多数都遵循ANSISQL标准。SQLServer使用ANSISQL-92的扩展集,称为T-SQL,其遵循ANSI 制定的SQL-92标准。 SQL发展历史 1970:E.J.Codd发表了关系数据库理论(relationaldatabasetheory); 1974-79:IBM以Codd的理论为基础开发了“Sequel”,并重命名为"SQL"; 1979:Oracle发布了商业版SQL 1981-84:出现了其他商业版本,分别来自IBM(DB2),DataGeneral(DG/SQL),RelationalTechnology(INGRES); SQL/86:ANSI跟ISO的第一个标准; SQL/89:增加了引用完整性(referentialintegrity); SQL/92(akaSQL2):被数据库管理系统(DBMS)生产商广发接受; 包括oids; SQL/2003:包含了XML相关内容,自动生成列值(columnvalues); 2005-09-30:“Dataisthenextgenerationinside...SQListhenewHTML”!TimO'eilly提出了Web2.0理念,称数据将是核心,SQL将成为“新的HTML"; SQL/2006:定义了SQL与XML(包含XQuery)的关联应用; 2006:Sun公司将以SQL基础的数据库管理系统嵌入JavaV6 2007:SQLServer2008(Katmi)在过去的SQL2005基础上增强了它的安全性,主要在:简单的数据加密,外键管理,增强了审查,改进了数据库镜像,加强了可支持性。 SQLServer的基本信息 SQLServer是一个关系数据库管理系统。它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的,于1988年推出了第一个OS/2版本。在WindowsNT推出后,Microsoft与Sybase在SQLServer的开发上就分道扬镳了,Microsoft将SQLServer移植到WindowsNT系统上,专注于开发推广SQLServer的WindowsNT版本。Sybase则较专注于SQLServer在UNIX?操作系统上的应用。数据库引擎是SQLServer系统的核心服务,负责完成数据的存储、处理和安全管理。

SQL存储过程使用方法.

SQL存储过程使用方法所有的大型数据库都支持存储过程,比如Oracle、MS SQL等,(但MS Access不支持,不过,在Access里可以使用参数化的查询)。存储过程是利用SQL Server所提供的Tranact-SQL语言所编写的程序。Tranact-SQL语言是SQL Server提供专为设计数据库应用程序的语言,它是应用程序和SQL Server数据库间的主要程序式设计界面。它好比Oracle数据库系统中的Pro-SQL和Informix的数据库系统能够中的Informix-4GL语言一样。这类语言主要提供以下功能,让用户可以设计出符合引用需求的程序: 1、变量说明 2、ANSI兼容的SQL命令(如Select,Update…. 3、一般流程控制命令(if…else…、while…. 4、内部函数存储过程的书写格 CREATE PROCEDURE [拥有者.]存储过程名[;程序编号] [(参数#1,…参数#1024] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS 程序行其中存储过程名不能超过128 个字。每个存储过程中最多设定1024个参数(SQL Server 7.0以上版本,参数的使用方法如下: @参数名数据类型 [VARYING] [=内定值] [OUTPUT] 每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE 外,其他SQL Server所支持的数据类型都可使用。 [=内定值]相当于我们在建立数据库时设定一个字段的默认值,这里是为这个参数设定默认值。[OUTPUT]是用来指定该参数是既有输入又有输出值的,也就是在调用了这个存储过程时,如果所指定的参数值是我们需要输入的参数,同时也需要在结果中输出的,则该项必须为OUTPUT,而如果只是做输出参数用,可以用CURSOR,同时在使用该参数时,必须指定VARYING和OUTPUT这两个语句。 CREATE PROCEDURE order_tot_amt @o_id int,@p_tot int output AS SELECT @p_tot = sum(Unitprice*Quantity FROM orderdetails WHERE ordered=@o_id 该例子是建立一个简单的存储过程order_tot_amt,这个存储过程根据用户输入的定单ID号码(@o_id,由定单明细表(orderdetails中计算该定单销售总额[单价(Unitprice*数量(Quantity],这一金额通过@p_tot这一参数输出给调用这一存储过程的程序使用存储过程有许多好处,它可以封装复杂的数据逻辑,充分发挥大型数据库本身的优势。我们知道,ASP并不适合做复杂的数据运算,而通过OLD DB访问数据库,由于数据需要在ASP和数据库之间传递,相当消耗系统资源。事实上,如果数据库仅仅起着

SQLSERVER数据库、表的创建及SQL语句命令

SQLSERVER数据库、表的创建及SQL语句命令 SQLSERVER数据库,安装、备份、还原等问题: 一、存在已安装了sql server 2000,或2005等数据库,再次安装2008,会出现的问题 1、卸载原来的sql server 2000、2005,然后再安装sql server 2008,否则经常sql server服务启动不了 2、sql server服务启动失败,解决方法: 进入sql server configure manager,点开Sql server 网络配置(非sql native client 配置),点sqlzhh(我sqlserver 的名字)协议,将VIA协议禁用。再启动Sql Server服务,成功 如图: 二、在第一次安装SQLSERVER2008结束后,查看安装过程明细,描述中有较多项插件或程度,显示安装失败。 解决方法:

1、重新启动安装程度setup.exe,选择进行修复安装,至完成即可。 三、先创建数据库XXX,再进行还原数据库时,选择好备份文件XXX.bak,确定后进行还原,会报如下图的错误。 解决方法: 选择好备份数据库文件后,再进入“选项”中,勾选“覆盖现在数据库”即可。

四、查看数据库版本的命令:select @@version 在数据库中,点击“新建查询”,然后输入命令,执行结果如下 五、数据库定义及操作命令: 按照数据结构来组织、存储和管理数据的仓库。由表、关系以及操作对象组成,把数据存放在数据表中。 1、修改数据库密码的命令: EXEC sp_password NULL, '你的新密码', 'sa' sp_password Null,'sa','sa'

SqlServer存储过程的事务模式编写

SQL Server在存储过程中编写事务处理代码的三种方法 SQL Server中数据库事务处理是相当有用的,鉴于很多SQL初学者编写的事务处理代码存往往存在漏洞,本文我们介绍了三种不同的方法,举例说明了如何在存储过程事务处理中编写正确的代码。希望能够对您有所帮助。 在编写SQL Server 事务相关的存储过程代码时,经常看到下面这样的写法: begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran 这样编写的SQL存在很大隐患。请看下面的例子: create table demo(id int not null) go begin tran insert into demo values (null) insert into demo values (2) commit tran go 执行时会出现一个违反not null 约束的错误信息,但随后又提示(1 row(s) affected)。我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。这是什么原因呢? 原来SQL Server在发生runtime 错误时,默认会rollback引起错误的语句,而继续执行后续语句。 如何避免这样的问题呢? 有三种方法:

1. 在事务语句最前面加上set xact_abort on set xact_abort on begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran go 当xact_abort 选项为on 时,SQL Server在遇到错误时会终止执行并rollback 整个事务。 2. 在每个单独的DML语句执行后,立即判断执行状态,并做相应处理。 begin tran update statement 1 ... if@@error<>0 begin rollback tran goto labend end delete statement 2 ... if@@error<>0 begin rollback tran goto labend end commit tran labend: go 3. 在SQL Server 2005中,可利用try...catch 异常处理机制。

(完整版)SQLSERVER存储过程大总结

SQLSERVER存储过程使用说明书 引言 首先介绍一下什么是存储过程:存储过程就是将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,并且这样的语句是放在数据库中的,还可以根据条件执行不同SQL语句,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。 请大家先看一个小例子: create proc query_book as select * from book go --调用存储过程 exec query_book 请大家来了解一下存储过程的语法。 Create PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 一、参数简介 1、procedure_name 新存储过程的名称。过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。 要创建局部临时过程,可以在 procedure_name 前面加一个编号 符 (#procedure_name),要创建全局临时过程,可以在 procedure_name 前面加两

个编号符 (##procedure_name)。完整的名称(包括 # 或 ##)不能超过 128 个字符。指定过程所有者的名称是可选的。 2、;number 是可选的整数,用来对同名的过程分组,以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。例如,名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。Drop PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符,则数字不应包含在标识符中,只应 在 procedure_name 前后使用适当的定界符。 3、@parameter 过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有 2100 个参数。 使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。 4、data_type 参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过,cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor,也必须同时指定 VARYING 和 OUTPUT 关键字。 说明:对于可以是cursor 数据类型的输出参数,没有最大数目的限制。 5、VARYING 指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标参数。 6、default 参数的默认值。如果定义了默认值,不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字,那么默认值中可以包含通配符(%、_、[] 和 [^])。 7、OUTPUT

SQL Server存储过程的基本概念以及语法

SQL Server存储过程的基本概念以及语法【转】 存储过程的概念 SQL Server提供了一种方法,它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成,以实现某个任务,这种方法就是存储过程。 存储过程是SQL语句和可选控制流语句的预编译集合,存储在数据库中,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其他强大的编程功能。 在SQL Server中存储过程分为两类:即系统提供的存储过程和用户自定义的存储过程。 可以出于任何使用SQL语句的目的来使用存储过程,它具有以下优点: 可以在单个存储过程中执行一系列SQL语句。 可以从自己的存储过程内引用其他存储过程,这可以简化一系列复杂语句。 存储过程在创建时即在服务器上进行编译,所以执行起来比单个SQL语句快,而且减少网络通信的负担。 安全性更高。 创建存储过程 在SQL Server中,可以使用三种方法创建存储过程: ①使用创建存储过程向导创建存储过程。 ②利用SQL Server 企业管理器创建存储过程。 ③使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。 下面介绍使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程 创建存储过程前,应该考虑下列几个事项: ①不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 ②存储过程可以嵌套使用,嵌套的最大深度不能超过32层。 ③创建存储过程的权限默认属于数据库所有者,该所有者可将此权限授予其他用户。 ④存储过程是数据库对象,其名称必须遵守标识符规则。 ⑤只能在当前数据库中创建存储过程。 ⑥ 一个存储过程的最大尺寸为128M。 使用CREATE PROCEDURE创建存储过程的语法形式如下: QUOTE: CREATE PROC[EDURE]procedure_name[;number][;number] [{@parameter data_type}[VARYING][=default][OUTPUT]][,...n] WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

C++调用SQL存储过程实例

c/c++调用mysql存储过程 mysql5.0以后就支持存储过程了,目前mysql的6.0Alpha版也已经推出。6.0不仅支持大型数据库如oracle等的绝大部分功能,如存储过程、视图、触发器、job等等,而且修正了这些功能所存在的bug,其中6.0.1还支持64位windows,以及表空间。 在c/c++中访问mysql,常见的只是一些简单的业务,使用c中嵌入简单的查询、插入、更新等操作即可。随着业务的复杂化,完成一个业务功能需要非常多的sql操作环节,把这些语句都嵌入c代码中会导致c代码越来越繁琐、不清晰,这时候,自然就想到了存储过程来封装所有的数据库逻辑,通过c简单调用mysql存储过程接口即可达到目的,极大地减轻了c程序员的工作量,也便于前端业务处理逻辑与数据库处理逻辑的分离。下面就介绍c 语言调用存储过程的简单的方法。 1、首先创建一张表 用于存放用户信息 Create table student( id int auto_increment, name varchar(20), age tinyint, remark varchar(50), primary key(id) ); 2、插入几条信息 Insert into student values(1,"zhouys",90, ""); commit; 3、查看用户信息 mysql> select * from student;

+------+-----------+------+----------+ | id | name | age | remark | +------+-----------+------+----------+ | 1 | zhouys | 90 | | +------+-----------+------+-----------+ 1 row in set (0.00 sec) mysql> 4、创建存储过程 如下: delimiter // create procedure querystudent( in in_id int , #0-字符id 1-数字id # out out_ret int, #返回结果 out out_name varchar(20), #名字 out out_age int #年龄) label_a:begin declare v_name varchar(20) ; declare v_age tinyint ; #参数判断 if (in_id<=0) then set out_ret=-1; #id error

SqlServer 使用存储过程 导出为Excel

SqlServer 使用存储过程导出为Excel 一个脱离office组件的可以将语句结果导出到Excel的过程 --1.执行时所连接的服务器决定文件存放在哪个服务器 [sql]view plain copy print? 1.CREATE PROC ExportFile 2. @QuerySql VARCHAR(max) 3. ,@Server VARCHAR(20) 4. ,@User VARCHAR(20) = 'sa' 5. ,@Password VARCHAR(20) 6. ,@FilePath NVARCHAR(100) = 'c:\ExportFile.csv' 7.AS 8.DECLARE @tmp VARCHAR(50) = '[##Table' + CONVERT(VARCHAR(36),NEWID())+']' 9.BEGIN TRY 10.DECLARE @Sql VARCHAR(max),@DataSource VARCHAR(max)=''; 11.--判断是否为远程服务器 12. IF @Server <> '.'AND @Server <> '127.0.0.1' 13.SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@Server+';User ID='+@Us er+';Password='+@Password+''').' 14.--将结果集导出到指定的数据库 15.SET @Sql = REPLACE(@QuerySql,' from ',' into '+@tmp+ ' from ' + @DataSource) 16. PRINT @Sql 17.EXEC(@Sql) 18. 19.DECLARE @Columns VARCHAR(max) = '',@Data NVARCHAR(max)='' 20.SELECT @Columns = @Columns + ',''' + name +''''--获取列名(xp_cmdshell导出文件没有列名) 21. ,@Data = @Data + ',Convert(Nvarchar,[' + name +'])'--将结果集所在的字段更新为nvarchar(避 免在列名和数据union的时候类型冲突) 22.FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..'+@tmp) 23.SELECT @Data = 'SELECT ' + SUBSTRING(@Data,2,LEN(@Data)) + ' FROM ' + @tmp 24.SELECT @Columns = 'Select ' + SUBSTRING(@Columns,2,LEN(@Columns)) 25.--使用xp_cmdshell的bcp命令将数据导出 26.EXEC sp_configure 'xp_cmdshell',1 27. RECONFIGURE 28.DECLARE @cmd NVARCHAR(4000) = 'bcp "' + @Columns+' Union All ' + @Data+'" queryout ' + @Fi lePath + ' -c' + CASE WHEN RIGHT(@FilePath,4) = '.csv'THEN' -t,'ELSE''END + ' -T' 29. PRINT @cmd 30.exec sys.xp_cmdshell @cmd 31.EXEC sp_configure 'xp_cmdshell',0 32. RECONFIGURE 33.EXEC('DROP TABLE ' + @tmp) 34.END TRY 35.BEGIN CATCH

相关主题
相关文档 最新文档