[懒人合集]网站通用程序集 2 【转】

最新推荐文章于 2025-07-27 19:19:40 发布

weixin_34319999

最新推荐文章于 2025-07-27 19:19:40 发布

阅读量67

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/JemBai/archive/2008/09/08/1287035.html

本文提供了一组针对 SQL Server 的通用存储过程，包括查询、插入、更新和删除操作。这些存储过程支持参数化查询，提高了数据库操作的灵活性和安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLSERVER 通用存储过程

Code
/*******create by liqiang665@163.com**********/

/*******通用SQL操作:查找,增加,更新,删除存储过程******/

/*******2008-07-10***********************/

/*
--说明
--创建数据库
create database mydatabase
on
(
name='xx',
filename='d:/mydb/xx.mdf'
)
log on
(
name='xxlog',
filename='d:/mydb/xxlog.ldf'
)
go
use
mydatabase
go
--然后创建表
create table mytable(

)
go
再加入以下存储过程即可
*/
--查询过程 select filter from tablename where condition order by sort
if object_id('lq_select_all_table') is not null
drop proc lq_select_all_table
go

create proc lq_select_all_table
@filter varchar(4000)='*',
@tablename varchar(255),
@condition varchar(1000)='',
@sort varchar(255)=''
as
if @filter is null or @filter=''
set @filter='*'
if @condition is not null and @condition<>''
set @condition=' where '+@condition
if @sort is not null and @sort<>''
set @sort=' order by '+@sort

set xact_abort on
begin tran lq_select
exec(' select '+@filter+' from '+@tablename+' '+@condition+' '+@sort)
commit tran lq_select
if @@error<>0 rollback tran lq_select

go

--插入操作 insert into values/selct,插入成功后返回最后的ID号
if object_id('lq_insert_all_table') is not null
drop proc lq_insert_all_table
go

create proc lq_insert_all_table
@tablename varchar(255),
@filter varchar(1000),
@values text,
@primarykeyid int out,
@selecttype bit=0
as
set xact_abort on
begin tran lq_insert
if @filter is null or @filter=''
    if @selecttype=0
    exec(' insert into '+@tablename+' '+'values( '''+@values+''' )')
    else
    exec(' insert into '+@tablename+' '+@values)
else
    if @selecttype=0
    exec(' insert into '+@tablename+'('+@filter+')' + 'values ('''+@values+''' )')
    else
    exec(' insert into '+@tablename+'('+@filter+')' + ' '+@values)
if @@rowcount>0
set @primarykeyid=@@IDENTITY
commit tran lq_insert
if @@error<>0 rollback tran lq_insert

go

--更新操作 update table1 set aa=bb from table2 where condition
if object_id('lq_update_all_table') is not null
drop proc lq_update_all_table
go

create proc lq_update_all_table
@tablename1 varchar(255),
@filterandvalues text,
@tablename2 varchar(255)='',
@condition varchar(1000)=''
as
if @condition is not null and @condition<>''
set @condition=' where '+@condition
set xact_abort on
begin tran lq_update
if @tablename2 is null or @tablename2=''
exec (' update '+@tablename1+' set '+@filterandvalues+' '+@condition)
else
exec (' update '+@tablename1+' set '+@filterandvalues+' from '+@tablename2+' '+@condition)
commit tran lq_update
if @@error>0 rollback tran lq_update

go

--删除 delete from table where condition
if object_id('lq_delete_all_table') is not null
drop proc lq_delete_all_table
go

create proc lq_delete_all_table
@tablename varchar(255),
@condition varchar(1000)=''
as
if @condition is not null and @condition<>''
set @condition=' where '+@condition
set xact_abort on
begin tran lq_delete
exec(' delete from '+@tablename+' '+@condition)
commit tran lq_delete
if @@error>0 rollback tran lq_delete

go

使用程序集的方法.因为是涉及到SQLSERVER,Access,mysql.所以要在web.config里写上连接字符串,如:

同时需要把bin目录下的相关动态链接库拷贝到相应的网站bin目录下面并引用.

其它的就懒得说了.

程序集可以从这里下载LQWebCmd程序集下载

转载于:https://www.cnblogs.com/JemBai/archive/2008/09/08/1287035.html