将存储过程写入C#中 有需要时则动态插入到数据库 免得麻烦

 

随时记录自己遇到的问题．

有时程序在调用数据库的存储过程时，调用前需要检查存储过程是否存在,　存在就直接调用,不存在就创建存储过程,然后在调用
操作方法如下：

if exists( select 1 from sys.all_objects where [type]="p" and [name]="yourprocedurename")

begin

exec yourprocedurename 

end

在.net 中执行一段程序，检查数据库中有没有某一个存储过程，如果没有，就在数据中创建一个存储过程．操作代码如下：

        public static string UP_GetRecordByPage = @"

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N"[dbo].[UP_GetRecordByPage]") AND type in (N"P", N"PC"))

BEGIN

EXEC dbo.sp_executesql @statement = N"

CREATE PROCEDURE [dbo].[UP_GetRecordByPage]

    @tblName      varchar(255),       -- 表名

    @fldName      varchar(255),       -- 主键字段名

    @PageSize     int = 10,           -- 页尺寸

    @PageIndex    int = 1,            -- 页码

    @IsReCount    bit = 0,            -- 返回记录总数, 非 0 值则返回

    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序

    @strWhere     varchar(1000) = """" -- 查询条件 (注意: 不要加 where)

AS



declare @strSQL   varchar(6000)       -- 主语句

declare @strTmp   varchar(100)        -- 临时变量(查询条件过长时可能会出错，可修改100为1000)

declare @strOrder varchar(400)        -- 排序类型

declare @strSQL2   varchar(6000)       -- 主语句





if @OrderType != 0

begin

    set @strTmp = ""<(select min""

    set @strOrder = "" order by ["" + @fldName +""] desc""

end

else

begin

    set @strTmp = "">(select max""

    set @strOrder = "" order by ["" + @fldName +""] asc""

end



set @strSQL = ""select top "" + str(@PageSize) + "" * from [""

    + @tblName + ""] where ["" + @fldName + ""]"" + @strTmp + ""([""

    + @fldName + ""]) from (select top "" + str((@PageIndex-1)*@PageSize) + "" [""

    + @fldName + ""] from ["" + @tblName + ""]"" + @strOrder + "") as tblTmp)""

    + @strOrder



if @strWhere != """"

    set @strSQL = ""select top "" + str(@PageSize) + "" * from [""

        + @tblName + ""] where ["" + @fldName + ""]"" + @strTmp + ""([""

        + @fldName + ""]) from (select top "" + str((@PageIndex-1)*@PageSize) + "" [""

        + @fldName + ""] from ["" + @tblName + ""] where "" + @strWhere + "" ""

        + @strOrder + "") as tblTmp) and "" + @strWhere + "" "" + @strOrder



if @PageIndex = 1

begin

    set @strTmp =""""

    if @strWhere != """"

        set @strTmp = "" where "" + @strWhere



    set @strSQL = ""select top "" + str(@PageSize) + "" * from [""

        + @tblName + ""]"" + @strTmp + "" "" + @strOrder

end



exec (@strSQL)



if @IsReCount != 0

begin

    set @strSQL2 = ""select count(*) as Total from ["" + @tblName + ""]""+"" where "" + @strWhere

	exec (@strSQL2)

end

" 

END

";