MS SQL Server 2000

– . , . “” , . , , , , .

            . ,

select * from view_analitica where ind=1,

10 .

select * from view_analitica

table_analitica.

select * from table_analitica where ind=1

, table_analitica ,  , : .

, , . . Job’.

            :

  1. , .
  2. table_analitica , , , .

, , - , 3 . -.

: - . , , . , .

, .

 

recompile_all .

 

CREATE PROCEDURE recompile_all AS

declare @table_name nvarchar(30), @i int

DECLARE @tab CURSOR

set @i=0

set @tab = CURSOR LOCAL STATIC FOR select [name] from sysobjects where status>=0 and type in('p','v')

 

OPEN @tab

FETCH NEXT FROM @tab INTO @table_name

WHILE @@FETCH_STATUS = 0

BEGIN

/*set @i=@i+1

print str(@i)+'    '+ @table_name */

 exec sp_recompile @table_name

 FETCH NEXT FROM @tab INTO @table_name

END

CLOSE @tab

DEALLOCATE @tab


GO

 

get_type_definition -.

 

CREATE FUNCTION get_type_definition (@id_type int, @len int=0, @prec int=0, @scale int=0) 

RETURNS varchar(50) AS 

BEGIN

            declare @ret as varchar(50)

 

            set @ret=(select top 1 name from systypes (nolock) where xtype=@id_type and not name='sysname')

 

            select @ret=@ret +     case @ret        when 'decimal' then '('+ltrim(str(@prec))+','+ltrim(str(@scale))+')'

                                                                       when 'numeric'             then '('+ltrim(str(@prec))+','+ltrim(str(@scale))+')'

                                                                       when 'float'       then '('+ltrim(str(@len))+')'

                                                                       when 'char'       then '('+ltrim(str(@len))+')'

                                                                       when 'varchar' then '('+ltrim(str(@len))+')'

                                                                       when 'nchar'     then '('+ltrim(str(@len))+')'

                                                                       when 'nvarchar'            then '('+ltrim(str(@len))+')'

                                                                       when 'binary'    then '('+ltrim(str(@len))+')'

                                                                       when 'varbinary'           then '('+ltrim(str(@len))+')'

                                                                       else ''

                                               end


            return @ret

END

 

get_type_isnull . null 0.

 

CREATE FUNCTION get_type_isnull (@id_type int, @var_name varchar(50)) 

RETURNS varchar(50) AS 

BEGIN

            declare @ret as varchar(50)

            declare @tp as varchar(50)

 

            set @tp=(select top 1 name from systypes (nolock) where xtype=@id_type and not name='sysname')

 

            select @ret=                case @tp         when 'decimal' then 'isnull('+@var_name+',0)'

                                                                       when 'numeric'             then 'isnull('+@var_name+',0)'

                                                                       when 'float'       then 'isnull('+@var_name+',0)'

                                                                       when 'char'       then 'isnull('+@var_name+','''')'

                                                                       when 'varchar' then 'isnull('+@var_name+','''')'

                                                                       when 'nchar'     then 'isnull('+@var_name+','''')'

                                                                       when 'nvarchar'            then 'isnull('+@var_name+','''')'

                                                                       when 'bigint'     then 'isnull('+@var_name+',0)'

                                                                       when 'int'          then 'isnull('+@var_name+',0)'

                                                                       when 'smallint' then 'isnull('+@var_name+',0)'

                                                                       when 'tinyint'    then 'isnull('+@var_name+',0)'

                                                                       when 'bit'         then 'isnull('+@var_name+',0)'

                                                                       else @var_name

                                               end

 

            return @ret

END

 

-. -. – ; – -; – .

 

CREATE PROCEDURE sp_reload_cache @query as nvarchar(4000), @tbl nvarchar(50), @reload_cond nvarchar(4000)=''

AS

 

set nocount on

 

declare @ct nvarchar(4000), @et nvarchar(4000), @at nvarchar(4000)

declare @strsq as nvarchar(4000)

 

set @strsq='if exists (select * from tempdb.dbo.sysobjects (nolock) where name=''##depot_tmp_'+rtrim(ltrim(str(@@spid)))+''') drop table ##depot_tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

 

set @strsq='select * into ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' from('+@query+') a'

if (@reload_cond<>'') set @strsq=@strsq+' where ' + @reload_cond

exec (@strsq)

 

if (not exists (select * from sysobjects (nolock) where name=@tbl))

begin

            -- - , . ! !

            set @ct=''

            select  @ct=@ct+case when @ct='' then '' else ',' end +name+' '+dbo.get_type_definition(xtype,length,xprec,xscale)

                        from tempdb..syscolumns (nolock)

                        where id = (select id from tempdb..sysobjects (nolock) where name='##depot_tmp_'+rtrim(ltrim(str(@@spid))))

            set @ct='create table ' +@tbl+ '('+@ct+')'

            exec (@ct)

end

else

begin

            --, - ,

            DECLARE alter_cursor CURSOR FOR

            select case       when a.name is null then 'ALTER TABLE ' +@tbl+ ' ADD '+b.name+' '+dbo.get_type_definition(b.xtype,b.length,b.xprec,b.xscale)

                                   when b.name is null then 'ALTER TABLE ' +@tbl+ ' DROP COLUMN '+a.name

                                   when a.xtype=b.xtype and a.length<b.length then 'ALTER TABLE ' +@tbl+ ' ALTER COLUMN '+b.name+' '+dbo.get_type_definition(b.xtype,b.length,b.xprec,b.xscale)

                                   else 'ALTER TABLE ' +@tbl+ ' DROP COLUMN '+a.name+';ALTER TABLE ' +@tbl+ ' ADD '+b.name+' '+dbo.get_type_definition(b.xtype,b.length,b.xprec,b.xscale)

            end as alter_cmd from

            (select * from syscolumns (nolock) where id = (select id from sysobjects (nolock) where name=@tbl)) a

                        full join

            (select * from tempdb..syscolumns (nolock) where id = (select id from tempdb..sysobjects (nolock) where name='##depot_tmp_'+rtrim(ltrim(str(@@spid))))) b

                        on a.name=b.name

            where isnull(a.xtype,0)<>isnull(b.xtype,0) or isnull(a.length,0)<isnull(b.length,0)           --

 

            open alter_cursor

            fetch next from alter_cursor

            into @ct

            while @@FETCH_STATUS = 0

            begin

                        print ' : '+@ct

                        exec (@ct)       --,          

                        fetch next from alter_cursor

                        into @ct

            end

            close alter_cursor

            deallocate alter_cursor

            exec recompile_all

end


--

set @ct=''

set @et=''

set @at=''

select  @ct=@ct + case when @ct='' then '' else ',' end + name,                                                       --

            @at=@at + case when @at='' then '' else ',' end + dbo.get_type_isnull(xtype, 'a.'+name),      -- isnull'

            @et=@et + case when @et='' then '' else ' and ' end +

                        dbo.get_type_isnull(xtype, 'a.'+name)+'='+dbo.get_type_isnull(xtype, 'b.'+name)        -- isnull' where

from syscolumns (nolock) where id = (select id from sysobjects (nolock) where name=@tbl)

 

-- #tmp (, )

set @strsq='select top 0 * into ##tmp_'+rtrim(ltrim(str(@@spid)))+' from ##depot_tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

 

if (@reload_cond='')

            set @strsq='insert into ##tmp_'+rtrim(ltrim(str(@@spid)))+'('+@ct+') (select '+@at+' from '+@tbl+' a (nolock) inner join ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' b on '+@et+')'

else

            set @strsq='insert into ##tmp_'+rtrim(ltrim(str(@@spid)))+'('+@ct+') (select '+@at+' from (select * from '+@tbl+' (nolock) where '+@reload_cond+') a inner join ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' b on '+@et+')'

 

exec (@strsq)


-- , -

set @strsq='delete '+@tbl+' from '+@tbl+' a where not exists (select * from ##tmp_'+rtrim(ltrim(str(@@spid)))+' b where '+@et+')'

if (@reload_cond<>'')

            set @strsq=@strsq+' and '+@reload_cond

exec (@strsq)


-- -

set @strsq='insert into '+@tbl+'('+@ct+') (select '+@ct+' from ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' a where not exists (select * from ##tmp_'+rtrim(ltrim(str(@@spid)))+' b where '+@et+'))'

exec (@strsq)

set @strsq='drop table ##tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

 

set @strsq='drop table ##depot_tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

GO

 

, SPID . , . , -.

 

- 1, 2, 3, 4 :

 

sp_reload_cache ‘select * from view_analitica’, ‘table_analitica’, ‘ind in (1,2,3,4)’

 


 








Rambler's Top100 @Mail.ru

mdls.ru 2008-2013

03.04.2014
Проект "ЧПУ на Ардуино" перенесён на ecnc.ru
Открытый проект "Простой станок с ЧПУ на Ардуино" перенесён на http://ecnc.ru
25.01.2013
Опубликован сайт "Частный переводчик"
Частный переводчик поможет провести переговоры, осуществит последовательный, синхронный, письменный переводы. http://tran.mdls.ru
25.01.2013
Начата раработка открытого проекта "Станок с ЧПУ"
Как сделать простой станок с ЧПУ на базе Arduino стоимостью менее 100$ своими руками. http://cnc.mdls.ru.
25.10.2011
"Юристы помогают" перенесён на lawshelp.ru
Проведена смена домена urist.mdls.ru на lawshelp.ru. Теперь обсудить задачи из любых отраслей Права можно на сайте www.lawshelp.ru