1. [转]使用DAC（专用管理员连接）连接到数据库 ?
????在“连接到数据库引擎”对话框的“服务器名称”框中，键入 ADMIN:，并在其后继续键入服务器实例的名称。例如，若要连接到名为 ACCT\PAYABLE 的服务器实例，请键入 ADMIN:ACCT\PAYABLE。
注1： 无法连接到?ADMIN:WIN-SV3NUJLD0C5\MSSQLSERVER_NEW在与?SQL?Server?建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且?SQL?Server?已配置为允许远程连接。?(provider:?SQL?网络接口,?error:?43?-?在获取专用的管理员连接(DAC)端口时出错。?确保正在运行?SQL?浏览器，或检查错误日志中是否有该端口号)?(.Net?SqlClient?Data?Provider)
------------------------------
有关帮助信息，请单击:?http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
原因可能是：
The?firewall?on?the?server?has?refused?the?connection.
A?specified?SQL?Server?instance?name?is?not?valid.
The?SQL?Server?Browser?service?(sqlbrowser)?is?not?started.（打开Sql?Server里的?SQL?Server?Brower?数据库浏览服务）
?
注2：无法连接到?ADMIN:192.168.1.100
?
不支持专用管理员连接。?(ObjectExplorer)
如何启用远程DAC连接？
只有?SQL?Server?sysadmin?角色的成员可以使用?DAC?连接。默认情况下，只能从服务器上运行的客户端建立连接。除非通过?sp_configure?使用?remote?admin?connections?选项进行配置，否则不允许使用网络连接。
DAC?支持加密和?SQL?Server?的其他安全性功能。DAC?只允许将用户上下文切换到其他管理用户。
默认情况下，DAC?仅侦听环回?IP?地址?(127.0.0.1)?端口?1434。
remote?admin?connections?设置的可能值如下：
0?-?指明仅允许本地连接使用?DAC
1?-?指明允许远程连接使用?DAC?
--启用远程DAC连接
sp_configure?'remote?admin?connections',?1;
GO
RECONFIGURE;
GO
注意：配置远程管理连接之后，会立即启用?DAC?侦听器而不必重新启动?SQL?Server，并且客户端可以立即远程连接到?DAC
如何启用远程DAC连接？
只有?SQL?Server?sysadmin?角色的成员可以使用?DAC?连接。默认情况下，只能从服务器上运行的客户端建立连接。除非通过?sp_configure?使用?remote?admin?connections?选项进行配置，否则不允许使用网络连接。
DAC?支持加密和?SQL?Server?的其他安全性功能。DAC?只允许将用户上下文切换到其他管理用户。
默认情况下，DAC?仅侦听环回?IP?地址?(127.0.0.1)?端口?1434。
remote?admin?connections?设置的可能值如下：
0?-?指明仅允许本地连接使用?DAC
1?-?指明允许远程连接使用?DAC?
--启用远程DAC连接
sp_configure?'remote?admin?connections',?1;
GO
RECONFIGURE;
GO
注意：配置远程管理连接之后，会立即启用?DAC?侦听器而不必重新启动?SQL?Server，并且客户端可以立即远程连接到?DAC
?
2. [转]具体解密代码
create?PROCEDURE?[dbo].[sp__windbi$decrypt]
(@procedure?sysname?=?NULL,?@revfl?int?=?1)
AS
/*
王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
目前这个存储过程只能解密存储过程，至于解密函数、触发器、视图的存储过程本网站会进一步关注，调用形式为：
[email protected],0
如果第二个参数使用1的话，会给出该存储过程的一些提示。
--版本2.0
*/
SET?NOCOUNT?ON
IF?@revfl?=?1
BEGIN
PRINT?'警告：该存储过程会删除并重建原始的存储过程。'
PRINT?'?在运行该存储过程之前确保你的数据库有一个备份。'
PRINT?'?该存储过程通常应该运行在产品环境的一个备份的非产品环境下。'
PRINT?'?为了运行这个存储过程，[email protected]'
RETURN?0
END
DECLARE?@intProcSpace?bigint,?@t?bigint,?@maxColID?smallint,@intEncrypted?
tinyint,@procNameLength?int
select?@maxColID?=?max(subobjid),@intEncrypted?=?imageval?FROM?
sys.sysobjvalues?WHERE?objid?=?object_id(@procedure)
GROUP?BY?imageval
--[email protected]'Rows?in?sys.sysobjvalues'
select?@procNameLength?=?datalength(@procedure)?+?29
DECLARE?@real_01?nvarchar(max)
DECLARE?@fake_01?nvarchar(max)
DECLARE?@fake_encrypt_01?nvarchar(max)
DECLARE?@real_decrypt_01?nvarchar(max),@real_decrypt_01a?nvarchar(max)
declare?@objtype?varchar(2),@ParentName?nvarchar(max)
select?@real_decrypt_01a?=?''
--提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称
select?@objtype=type,@parentname=object_name(parent_object_id)
from?sys.objects?where?[object_id]=object_id(@procedure)
--?从sys.sysobjvalues里提出加密的imageval记录
SET?@real_01=(SELECT?top?1?imageval?FROM?sys.sysobjvalues?WHERE?objid?=?
object_id(@procedure)?and?valclass?=?1?order?by?subobjid)
--创建一个临时表
create?table?#output?(?[ident]?[int]?IDENTITY?(1,?1)?NOT?NULL?,
[real_decrypt]?NVARCHAR(MAX)?)
--开始一个事务，稍后回滚
BEGIN?TRAN
--更改原始的存储过程，用短横线替换
if?@objtype='P'
??SET?@fake_01='ALTER?PROCEDURE?'+?@procedure?+'?WITH?ENCRYPTION?AS?
??'+REPLICATE('-',?40003?-?@procNameLength)
else?if?@objtype='FN'
??SET?@fake_01='ALTER?FUNCTION?'+?@procedure?+'()?RETURNS?INT?WITH?ENCRYPTION?AS?BEGIN?RETURN?1?
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/?END'
else?if?@objtype='V'
??SET?@fake_01='ALTER?view?'+?@procedure?+'?WITH?ENCRYPTION?AS?select?1?as?col
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
else?if?@objtype='TR'
??SET?@fake_01='ALTER?trigger?'+?@procedure?+'?ON?'+@parentname+'WITH?ENCRYPTION?AFTER?INSERT?AS?RAISERROR?(''N'',16,10)
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
EXECUTE?(@fake_01)
--从sys.sysobjvalues里提出加密的假的
SET?@fake_encrypt_01=(SELECT?top?1?imageval?FROM?sys.sysobjvalues?WHERE?objid?=?
object_id(@procedure)?and?valclass?=?1?order?by?subobjid?)
if?@objtype='P'
??SET?@fake_01='Create?PROCEDURE?'+?@procedure?+'?WITH?ENCRYPTION?AS?
??'+REPLICATE('-',?40003?-?@procNameLength)
else?if?@objtype='FN'
??SET?@fake_01='CREATE?FUNCTION?'+?@procedure?+'()?RETURNS?INT?WITH?ENCRYPTION?AS?BEGIN?RETURN?1?
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/?END'
else?if?@objtype='V'
??SET?@fake_01='Create?view?'+?@procedure?+'?WITH?ENCRYPTION?AS?select?1?as?col
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
else?if?@objtype='TR'
??SET?@fake_01='Create?trigger?'+?@procedure?+'?ON?'+@parentname+'WITH?ENCRYPTION?AFTER?INSERT?AS?RAISERROR?(''N'',16,10)
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
--开始计数
SET?@intProcSpace=1
--使用字符填充临时变量
SET?@real_decrypt_01?=?replicate(N'A',?(datalength(@real_01)?/2?))
--循环设置每一个变量，创建真正的变量
--每次一个字节
SET?@intProcSpace=1
--如有必要，[email protected]_xx变量并解密
WHILE?@intProcSpace<=(datalength(@real_01)/2)
BEGIN
--真的和假的和加密的假的进行异或处理
SET?@real_decrypt_01?=?stuff(@real_decrypt_01,?@intProcSpace,?1,
NCHAR(UNICODE(substring(@real_01,?@intProcSpace,?1))?^
(UNICODE(substring(@fake_01,?@intProcSpace,?1))?^
UNICODE(substring(@fake_encrypt_01,?@intProcSpace,?1)))))
SET?@intProcSpace=@intProcSpace+1
END
--通过sp_helptext逻辑向表#output里插入变量
insert?#output?(real_decrypt)?select?@real_decrypt_01
--?select?real_decrypt?AS?'#output?chek'?from?#output?--测试
--?-------------------------------------
--开始从sp_helptext提取
--?-------------------------------------
declare?@dbname?sysname
,@BlankSpaceAdded?int
,@BasePos?int
,@CurrentPos?int
,@TextLength?int
,@LineId?int
,@AddOnLen?int
,@LFCR?int?--回车换行的长度
,@DefinedLength?int
,@SyscomText?nvarchar(4000)
,@Line?nvarchar(255)
Select?@DefinedLength?=?255
SELECT?@BlankSpaceAdded?=?0?--跟踪行结束的空格。注意Len函数忽略了多余的空格
CREATE?TABLE?#CommentText
(LineId?int
,Text?nvarchar(255)?collate?database_default)
--使用#output代替sys.sysobjvalues
DECLARE?ms_crs_syscom?CURSOR?LOCAL
FOR?SELECT?real_decrypt?from?#output
ORDER?BY?ident
FOR?READ?ONLY
--获取文本
SELECT?@LFCR?=?2
SELECT?@LineId?=?1
OPEN?ms_crs_syscom
FETCH?NEXT?FROM?ms_crs_syscom?into?@SyscomText
WHILE?@@fetch_status?>=?0
BEGIN
SELECT?@BasePos?=?1
SELECT?@CurrentPos?=?1
SELECT?@TextLength?=?LEN(@SyscomText)
WHILE?@CurrentPos?!=?0
BEGIN
--通过回车查找行的结束
SELECT?@CurrentPos?=?CHARINDEX(char(13)+char(10),?@SyscomText,?
@BasePos)
--如果找到回车
IF?@CurrentPos?!=?0
BEGIN
--[email protected]@Lines目前的内容并继续
While?(isnull(LEN(@Line),0)?+?@BlankSpaceAdded?+?
@CurrentPos-@BasePos?+?@LFCR)?>?@DefinedLength
BEGIN
SELECT?@AddOnLen?=?@DefinedLength-(isnull(LEN(@Line),0)?+?
@BlankSpaceAdded)
INSERT?#CommentText?VALUES
(?@LineId,
isnull(@Line,?N'')?+?isnull(SUBSTRING(@SyscomText,?
@BasePos,?@AddOnLen),?N''))
SELECT?@Line?=?NULL,?@LineId?=?@LineId?+?1,
@BasePos?=?@BasePos?+?@AddOnLen,?@BlankSpaceAdded?=?0
END
SELECT?@Line?=?isnull(@Line,?N'')?+?
isnull(SUBSTRING(@SyscomText,?@BasePos,?@CurrentPos-@BasePos?+?@LFCR),?N'')
SELECT?@BasePos?=?@CurrentPos+2
INSERT?#CommentText?VALUES(?@LineId,?@Line?)
SELECT?@LineId?=?@LineId?+?1
SELECT?@Line?=?NULL
END
ELSE
--如果回车没找到
BEGIN
IF?@BasePos?<=?@TextLength
BEGIN
--[email protected]
While?(isnull(LEN(@Line),0)?+?@BlankSpaceAdded?+?
@TextLength-@BasePos+1?)?>?@DefinedLength
BEGIN
SELECT?@AddOnLen?=?@DefinedLength?-?
(isnull(LEN(@Line),0)?+?@BlankSpaceAdded)
INSERT?#CommentText?VALUES
(?@LineId,
isnull(@Line,?N'')?+?isnull(SUBSTRING(@SyscomText,?
@BasePos,?@AddOnLen),?N''))
SELECT?@Line?=?NULL,?@LineId?=?@LineId?+?1,
@BasePos?=?@BasePos?+?@AddOnLen,?@BlankSpaceAdded?=?
0
END
SELECT?@Line?=?isnull(@Line,?N'')?+?
isnull(SUBSTRING(@SyscomText,?@BasePos,?@TextLength-@BasePos+1?),?N'')
if?LEN(@Line)?<?@DefinedLength?and?charindex('?',?
@SyscomText,?@TextLength+1?)?>?0
BEGIN
SELECT?@Line?=?@Line?+?'?',?@BlankSpaceAdded?=?1
END
END
END
END
FETCH?NEXT?FROM?ms_crs_syscom?into?@SyscomText
END
IF?@Line?is?NOT?NULL
INSERT?#CommentText?VALUES(?@LineId,?@Line?)
select?Text?from?#CommentText?order?by?LineId
CLOSE?ms_crs_syscom
DEALLOCATE?ms_crs_syscom
DROP?TABLE?#CommentText
--?-------------------------------------
--结束从sp_helptext提取
--?-------------------------------------
--删除用短横线创建的存储过程并重建原始的存储过程
ROLLBACK?TRAN
DROP?TABLE?#output
(@procedure?sysname?=?NULL,?@revfl?int?=?1)
AS
/*
王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
目前这个存储过程只能解密存储过程，至于解密函数、触发器、视图的存储过程本网站会进一步关注，调用形式为：
[email protected],0
如果第二个参数使用1的话，会给出该存储过程的一些提示。
--版本2.0
*/
SET?NOCOUNT?ON
IF?@revfl?=?1
BEGIN
PRINT?'警告：该存储过程会删除并重建原始的存储过程。'
PRINT?'?在运行该存储过程之前确保你的数据库有一个备份。'
PRINT?'?该存储过程通常应该运行在产品环境的一个备份的非产品环境下。'
PRINT?'?为了运行这个存储过程，[email protected]'
RETURN?0
END
DECLARE?@intProcSpace?bigint,?@t?bigint,?@maxColID?smallint,@intEncrypted?
tinyint,@procNameLength?int
select?@maxColID?=?max(subobjid),@intEncrypted?=?imageval?FROM?
sys.sysobjvalues?WHERE?objid?=?object_id(@procedure)
GROUP?BY?imageval
--[email protected]'Rows?in?sys.sysobjvalues'
select?@procNameLength?=?datalength(@procedure)?+?29
DECLARE?@real_01?nvarchar(max)
DECLARE?@fake_01?nvarchar(max)
DECLARE?@fake_encrypt_01?nvarchar(max)
DECLARE?@real_decrypt_01?nvarchar(max),@real_decrypt_01a?nvarchar(max)
declare?@objtype?varchar(2),@ParentName?nvarchar(max)
select?@real_decrypt_01a?=?''
--提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称
select?@objtype=type,@parentname=object_name(parent_object_id)
from?sys.objects?where?[object_id]=object_id(@procedure)
--?从sys.sysobjvalues里提出加密的imageval记录
SET?@real_01=(SELECT?top?1?imageval?FROM?sys.sysobjvalues?WHERE?objid?=?
object_id(@procedure)?and?valclass?=?1?order?by?subobjid)
--创建一个临时表
create?table?#output?(?[ident]?[int]?IDENTITY?(1,?1)?NOT?NULL?,
[real_decrypt]?NVARCHAR(MAX)?)
--开始一个事务，稍后回滚
BEGIN?TRAN
--更改原始的存储过程，用短横线替换
if?@objtype='P'
??SET?@fake_01='ALTER?PROCEDURE?'+?@procedure?+'?WITH?ENCRYPTION?AS?
??'+REPLICATE('-',?40003?-?@procNameLength)
else?if?@objtype='FN'
??SET?@fake_01='ALTER?FUNCTION?'+?@procedure?+'()?RETURNS?INT?WITH?ENCRYPTION?AS?BEGIN?RETURN?1?
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/?END'
else?if?@objtype='V'
??SET?@fake_01='ALTER?view?'+?@procedure?+'?WITH?ENCRYPTION?AS?select?1?as?col
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
else?if?@objtype='TR'
??SET?@fake_01='ALTER?trigger?'+?@procedure?+'?ON?'+@parentname+'WITH?ENCRYPTION?AFTER?INSERT?AS?RAISERROR?(''N'',16,10)
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
EXECUTE?(@fake_01)
--从sys.sysobjvalues里提出加密的假的
SET?@fake_encrypt_01=(SELECT?top?1?imageval?FROM?sys.sysobjvalues?WHERE?objid?=?
object_id(@procedure)?and?valclass?=?1?order?by?subobjid?)
if?@objtype='P'
??SET?@fake_01='Create?PROCEDURE?'+?@procedure?+'?WITH?ENCRYPTION?AS?
??'+REPLICATE('-',?40003?-?@procNameLength)
else?if?@objtype='FN'
??SET?@fake_01='CREATE?FUNCTION?'+?@procedure?+'()?RETURNS?INT?WITH?ENCRYPTION?AS?BEGIN?RETURN?1?
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/?END'
else?if?@objtype='V'
??SET?@fake_01='Create?view?'+?@procedure?+'?WITH?ENCRYPTION?AS?select?1?as?col
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
else?if?@objtype='TR'
??SET?@fake_01='Create?trigger?'+?@procedure?+'?ON?'+@parentname+'WITH?ENCRYPTION?AFTER?INSERT?AS?RAISERROR?(''N'',16,10)
??/*'+REPLICATE('*',?datalength(@real_01)?/2?-?@procNameLength)+'*/'
--开始计数
SET?@intProcSpace=1
--使用字符填充临时变量
SET?@real_decrypt_01?=?replicate(N'A',?(datalength(@real_01)?/2?))
--循环设置每一个变量，创建真正的变量
--每次一个字节
SET?@intProcSpace=1
--如有必要，[email protected]_xx变量并解密
WHILE?@intProcSpace<=(datalength(@real_01)/2)
BEGIN
--真的和假的和加密的假的进行异或处理
SET?@real_decrypt_01?=?stuff(@real_decrypt_01,?@intProcSpace,?1,
NCHAR(UNICODE(substring(@real_01,?@intProcSpace,?1))?^
(UNICODE(substring(@fake_01,?@intProcSpace,?1))?^
UNICODE(substring(@fake_encrypt_01,?@intProcSpace,?1)))))
SET?@intProcSpace=@intProcSpace+1
END
--通过sp_helptext逻辑向表#output里插入变量
insert?#output?(real_decrypt)?select?@real_decrypt_01
--?select?real_decrypt?AS?'#output?chek'?from?#output?--测试
--?-------------------------------------
--开始从sp_helptext提取
--?-------------------------------------
declare?@dbname?sysname
,@BlankSpaceAdded?int
,@BasePos?int
,@CurrentPos?int
,@TextLength?int
,@LineId?int
,@AddOnLen?int
,@LFCR?int?--回车换行的长度
,@DefinedLength?int
,@SyscomText?nvarchar(4000)
,@Line?nvarchar(255)
Select?@DefinedLength?=?255
SELECT?@BlankSpaceAdded?=?0?--跟踪行结束的空格。注意Len函数忽略了多余的空格
CREATE?TABLE?#CommentText
(LineId?int
,Text?nvarchar(255)?collate?database_default)
--使用#output代替sys.sysobjvalues
DECLARE?ms_crs_syscom?CURSOR?LOCAL
FOR?SELECT?real_decrypt?from?#output
ORDER?BY?ident
FOR?READ?ONLY
--获取文本
SELECT?@LFCR?=?2
SELECT?@LineId?=?1
OPEN?ms_crs_syscom
FETCH?NEXT?FROM?ms_crs_syscom?into?@SyscomText
WHILE?@@fetch_status?>=?0
BEGIN
SELECT?@BasePos?=?1
SELECT?@CurrentPos?=?1
SELECT?@TextLength?=?LEN(@SyscomText)
WHILE?@CurrentPos?!=?0
BEGIN
--通过回车查找行的结束
SELECT?@CurrentPos?=?CHARINDEX(char(13)+char(10),?@SyscomText,?
@BasePos)
--如果找到回车
IF?@CurrentPos?!=?0
BEGIN
--[email protected]@Lines目前的内容并继续
While?(isnull(LEN(@Line),0)?+?@BlankSpaceAdded?+?
@CurrentPos-@BasePos?+?@LFCR)?>?@DefinedLength
BEGIN
SELECT?@AddOnLen?=?@DefinedLength-(isnull(LEN(@Line),0)?+?
@BlankSpaceAdded)
INSERT?#CommentText?VALUES
(?@LineId,
isnull(@Line,?N'')?+?isnull(SUBSTRING(@SyscomText,?
@BasePos,?@AddOnLen),?N''))
SELECT?@Line?=?NULL,?@LineId?=?@LineId?+?1,
@BasePos?=?@BasePos?+?@AddOnLen,?@BlankSpaceAdded?=?0
END
SELECT?@Line?=?isnull(@Line,?N'')?+?
isnull(SUBSTRING(@SyscomText,?@BasePos,?@CurrentPos-@BasePos?+?@LFCR),?N'')
SELECT?@BasePos?=?@CurrentPos+2
INSERT?#CommentText?VALUES(?@LineId,?@Line?)
SELECT?@LineId?=?@LineId?+?1
SELECT?@Line?=?NULL
END
ELSE
--如果回车没找到
BEGIN
IF?@BasePos?<=?@TextLength
BEGIN
--[email protected]
While?(isnull(LEN(@Line),0)?+?@BlankSpaceAdded?+?
@TextLength-@BasePos+1?)?>?@DefinedLength
BEGIN
SELECT?@AddOnLen?=?@DefinedLength?-?
(isnull(LEN(@Line),0)?+?@BlankSpaceAdded)
INSERT?#CommentText?VALUES
(?@LineId,
isnull(@Line,?N'')?+?isnull(SUBSTRING(@SyscomText,?
@BasePos,?@AddOnLen),?N''))
SELECT?@Line?=?NULL,?@LineId?=?@LineId?+?1,
@BasePos?=?@BasePos?+?@AddOnLen,?@BlankSpaceAdded?=?
0
END
SELECT?@Line?=?isnull(@Line,?N'')?+?
isnull(SUBSTRING(@SyscomText,?@BasePos,?@TextLength-@BasePos+1?),?N'')
if?LEN(@Line)?<?@DefinedLength?and?charindex('?',?
@SyscomText,?@TextLength+1?)?>?0
BEGIN
SELECT?@Line?=?@Line?+?'?',?@BlankSpaceAdded?=?1
END
END
END
END
FETCH?NEXT?FROM?ms_crs_syscom?into?@SyscomText
END
IF?@Line?is?NOT?NULL
INSERT?#CommentText?VALUES(?@LineId,?@Line?)
select?Text?from?#CommentText?order?by?LineId
CLOSE?ms_crs_syscom
DEALLOCATE?ms_crs_syscom
DROP?TABLE?#CommentText
--?-------------------------------------
--结束从sp_helptext提取
--?-------------------------------------
--删除用短横线创建的存储过程并重建原始的存储过程
ROLLBACK?TRAN
DROP?TABLE?#output
?
3、一般存储过程格式
if?exists?(select?1
????????????from??sysobjects
???????????where??id?=?object_id('usp_xxxx')
????????????and???type?=?'P')
???drop?proc?usp_xxxx
go
CREATE?PROCEDURE?usp_xxxx
(
????@param1????????????????int
????,@param2????????????varchar(200)=''????????
)
WITH?ENCRYPTION
AS
/******************************************************************************
**????????Name:?usp_xxxx
**????????Desc:?
**
**????????Return?Values:
**????????Author:?
**????????Date:?
*******************************************************************************/
DECLARE?@vtmpStr????varchar(200)
DECLARE?@queryStr????????varchar(5000)=''
DECLARE?@rowStr????????varchar(2000)=''
DECLARE?@sqlStr????????varchar(2000)=''
----?查询条件?--------------------------------------------
IF?@param1????>0????--供应商产品编号????
??SET?@queryStr='?And?AAAA?=?'+CAST(@param1?as?varchar(20))+@queryStr
????
IF?LEN(@param2) >0????--供应商名称????
??SET?@queryStr='?And?(?BBBB?LIKE?'+CHAR(39)+'%'+@param2+'%'+CHAR(39)?+'?)?'?+@queryStr
??
????
-----?分页?------------------------
IF?@rowStart?IS?NOT?NULL?AND?@rowEnd?IS?NOT?NULL????--启用分页
??SET?@rowStr=?'?WHERE?IDRank?BETWEEN?'+CAST(?@rowStart?as?Varchar(20)?)
????????????????+?'?AND?'+CAST(?@rowEnd?as?Varchar(20)?)
SET?@sqlStr='
??SELECT?*?FROM?(
????SELECT?DISTINCT
???????AAAAA
???????,BBBB
??????,ROW_NUMBER() OVER(ORDER BY '+ @orderby +') as IDRank?
???FROM?tb1?'
??
SET?@sqlStr+='?WHERE?1=1?'
??+?@queryStr?
??+?'?)?AS?IDWithRowNumber?'
??
??
Exec(@sqlStr+@rowStr+?'?ORDER?BY?IDRank?ASC?')????--查询出结果集
--[email protected][email protected]
?
SET?@sqlStr?=?REPLACE(@sqlStr,'SELECT?*?FROM','SELECT?COUNT(*)?as?count?FROM')???--统计记录总数
Exec(@sqlStr)?
--
If?@@Error<>0
????Begin
????????Select?@vtmpStr?='查询信息出错！'
????????Goto?err_handle
????End
????
Return
????
err_handle:
????Select?@vtmpStr?=?'调用usp_xxxx：'+@vtmpStr+char(30)
????RaisError?(@vtmpStr,16,1)
????????????from??sysobjects
???????????where??id?=?object_id('usp_xxxx')
????????????and???type?=?'P')
???drop?proc?usp_xxxx
go
CREATE?PROCEDURE?usp_xxxx
(
????@param1????????????????int
????,@param2????????????varchar(200)=''????????
)
WITH?ENCRYPTION
AS
/******************************************************************************
**????????Name:?usp_xxxx
**????????Desc:?
**
**????????Return?Values:
**????????Author:?
**????????Date:?
*******************************************************************************/
DECLARE?@vtmpStr????varchar(200)
DECLARE?@queryStr????????varchar(5000)=''
DECLARE?@rowStr????????varchar(2000)=''
DECLARE?@sqlStr????????varchar(2000)=''
----?查询条件?--------------------------------------------
IF?@param1????>0????--供应商产品编号????
??SET?@queryStr='?And?AAAA?=?'+CAST(@param1?as?varchar(20))+@queryStr
????
IF?LEN(@param2) >0????--供应商名称????
??SET?@queryStr='?And?(?BBBB?LIKE?'+CHAR(39)+'%'+@param2+'%'+CHAR(39)?+'?)?'?+@queryStr
??
????
-----?分页?------------------------
IF?@rowStart?IS?NOT?NULL?AND?@rowEnd?IS?NOT?NULL????--启用分页
??SET?@rowStr=?'?WHERE?IDRank?BETWEEN?'+CAST(?@rowStart?as?Varchar(20)?)
????????????????+?'?AND?'+CAST(?@rowEnd?as?Varchar(20)?)
SET?@sqlStr='
??SELECT?*?FROM?(
????SELECT?DISTINCT
???????AAAAA
???????,BBBB
??????,ROW_NUMBER() OVER(ORDER BY '+ @orderby +') as IDRank?
???FROM?tb1?'
??
SET?@sqlStr+='?WHERE?1=1?'
??+?@queryStr?
??+?'?)?AS?IDWithRowNumber?'
??
??
Exec(@sqlStr+@rowStr+?'?ORDER?BY?IDRank?ASC?')????--查询出结果集
--[email protected][email protected]
?
SET?@sqlStr?=?REPLACE(@sqlStr,'SELECT?*?FROM','SELECT?COUNT(*)?as?count?FROM')???--统计记录总数
Exec(@sqlStr)?
--
If?@@Error<>0
????Begin
????????Select?@vtmpStr?='查询信息出错！'
????????Goto?err_handle
????End
????
Return
????
err_handle:
????Select?@vtmpStr?=?'调用usp_xxxx：'+@vtmpStr+char(30)
????RaisError?(@vtmpStr,16,1)
?
附参考资料2:
http://blog.csdn.net/thecityofsky/article/details/6796939