How To Use Dynamic Sql in Sql Server ?
来源:互联网 发布:linux boot目录不足 编辑:程序博客网 时间:2024/05/16 11:15
How To Use Dynamic Sql in Sql Server ?
动态SQL 在sql server Procedure中的应用
Create PROCEDURE [dbo].[Proc_Get_Serial_No]
(
@Table_Name varchar(20),
@No_File varchar(20)
)
AS
Declare @Serial_No int
Begin
Declare @Sql nvarchar(max)
Set @Sql ='select @Serial_No= isnull(Max('+@No_File+'),0)+1 From '+@Table_Name+''
Execute sp_executesql @Sql,
N'@Serial_No int output',
@Serial_No output
print @Serial_No
End
---Result---
Serial_No
-----------
1
注意:对于Intput 的参数需要用 '+@parameter+'
对于output 的参数则需要在 执行动态sql 的时间以定义参数的形式说明。
如上面的: N'@Serial_No int output',然后才是参数输出,如 @Serial_No output
--If Your want to output more the one value, your can reference the sql section as below。
Note: The Output Parameter Define.
Create PROCEDURE [dbo].[Proc_Get_Serial_No]
(
@Table_Name varchar(20),
@No_File varchar(20)
)
AS
Declare @Serial_No int
Declare @x varchar(10)
Begin
Declare @Sql nvarchar(max)
Set @Sql ='select @x=''y'', @Serial_No= isnull(Max('+@No_File+'),0)+1 From '+@Table_Name+''
Execute sp_executesql @Sql,
N'@Serial_No int output,@x varchar(10) output',
@Serial_No output,
@x output
Select @Serial_No as Serial_No,@x as 'xx'
End
---Result---
Serial_No xx
----------- ----------
1 y
- How To Use Dynamic Sql in Sql Server ?
- How To: Use Forms Authentication with SQL Server in ASP.NET 2.0
- How To: Use Forms Authentication with SQL Server in ASP.NET 2.0
- How to use PL SQL Developer in Ubuntu
- How To Use SQL XML ?
- use dynamic sql to output the result
- how to add linked server in SQL server.
- How To: Reset Identity column in SQL Server
- How to search the available space in SQL server.
- How to delete duplicate rows without children in sql server
- How to shrink the tempdb database in SQL Server
- How to enable remote connections in SQL Server 2008?
- How to backup and restore database in SQL Server
- How to remove UCS-2 surrogate pairs in SQL Server?
- How to modify the xml in sql server
- (MS SQL)HOW TO USE FETCH
- How to use the Sql Tuning Advisor
- Sql Server - How to write a Stored procedure in Sql server
- 外贸英语:物流行业术语
- XX已死?不见得吧
- 数据库发展及 Hibernate ORM 之我见 O(∩_∩)O~
- 物流常用英语词汇
- 串口的设置程序
- How To Use Dynamic Sql in Sql Server ?
- 记clr调用webService出现的错误
- source insight快捷键及使用技巧
- Oracle的外部表
- 谈谈游戏软件的开发管理
- 《第一财经周刊》封面文章:那些忧伤的年轻人
- 【C/C++】推荐的C++书籍以及阅读顺序
- C#高级编程(第六版) 目录
- Java实现获取本机上ADSL的IP