none
вложенный Cursor RRS feed

  • Вопрос

  • Доброго времени суток. У меня возникла навязчивая идея немножко переделать базенку портированную на ms sql 2008 с foxpro 2 :) Начал писать скрипт, который бы перебирал все таблицы в базе, конвертируя 2 поля из varchar в int и делая его not null с целью дальнейшего установления их обоих как одного ключевого поля.
    Execute Query выполняется, но ничего не меняется. Дебаггер не запускается - пишет unable to start t-sql debugging. could not attach to sql-server process on 'Computer'.
    • Изменено kosmich74 2 ноября 2010 г. 12:16 scuko
    2 ноября 2010 г. 11:57

Ответы

  • declare @str varchar(max)
    set @str = '
    if exists ( select * from information_schema.columns where Table_Name = parsename( "?", 1 ) 
                  and Table_Schema = parsename( "?", 2 )
                  and Column_Name=''id1''
       )
    begin 
    print "?"   
    exec (''delete from ? where id1 is null'')
    exec (''alter table ? alter column id1 int not null'')
    end
    
    if exists ( select * from information_schema.columns where Table_Name = parsename( "?", 1 ) 
                  and Table_Schema = parsename( "?", 2 )
                  and Column_Name=''id2''
       )
    begin 
    print "?"   
    exec (''delete from ? where id2 is null'')
    exec (''alter table ? alter column id2 int not null'')
    end
    '
    
    exec sp_MSforeachtable @str
    

    http://www.t-sql.ru
    • Предложено в качестве ответа Yury Iwtschenko 5 ноября 2010 г. 20:31
    • Помечено в качестве ответа kosmich74 10 ноября 2010 г. 14:23
    2 ноября 2010 г. 13:18
    Модератор

Все ответы

  • DECLARE @tablename NVARCHAR(100);
    DECLARE AllTables CURSOR FOR
    SELECT TABLE_NAME FROM [baby].[INFORMATION_SCHEMA].[TABLES]
    OPEN AllTables;
    FETCH NEXT FROM AllTables INTO @tablename;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    
    	DECLARE @columnname NVARCHAR(100);
    	DECLARE AllCollumns CURSOR FOR
    	SELECT COLUMN_NAME FROM [baby].[INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME = QUOTENAME(@tablename) 
    	OPEN AllCollumns;
    	FETCH NEXT FROM AllCollumns INTO @columnname;
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    
    

    2 ноября 2010 г. 12:03
  • 		DECLARE @query NVARCHAR(100);
    		IF @columnname = 'id1'
    		BEGIN		
    		SET @query = N'DELETE FROM [baby].[dbo].' + QUOTENAME(@tablename) + ' WHERE ' + QUOTENAME(@columnname) + 'IS NULL'
    		EXEC sp_executesql @query;
    		SET @query = N'ALTER TABLE [baby].[dbo].' + QUOTENAME(@tablename) + ' ALTER COLUMN ' + QUOTENAME(@columnname) + ' INT NOT NULL'
    		EXEC sp_executesql @query;
    		END
    
    		IF @columnname = 'id2'
    		BEGIN	
    		SET @query = N'DELETE FROM [baby].[dbo].' + QUOTENAME(@tablename) + ' WHERE ' + QUOTENAME(@columnname) + 'IS NULL'
    		EXEC sp_executesql @query;
    		SET @query = N'ALTER TABLE [baby].[dbo].' + QUOTENAME(@tablename) + ' ALTER COLUMN ' + QUOTENAME(@columnname) + ' INT NOT NULL'
    		EXEC sp_executesql @query;
    		END

    • Изменено kosmich74 2 ноября 2010 г. 12:05 !
    2 ноября 2010 г. 12:04
  • 		IF @columnname = 'id3'
    		BEGIN	
    		SET @query = N'DELETE FROM [baby].[dbo].' + QUOTENAME(@tablename) + ' WHERE ' + QUOTENAME(@columnname) + 'IS NULL'
    		EXEC sp_executesql @query;
    		SET @query = N'ALTER TABLE [baby].[dbo].' + QUOTENAME(@tablename) + ' ALTER COLUMN ' + QUOTENAME(@columnname) + ' INT NOT NULL'
    		EXEC sp_executesql @query;
    		END
    		
    	FETCH NEXT FROM AllCollumns INTO @columnname;
    	END;
    	CLOSE AllCollumns;
    	DEALLOCATE AllCollumns;
    
    FETCH NEXT FROM AllTables INTO @tablename;
    END;
    CLOSE AllTables;
    DEALLOCATE AllTables;
    GO
    
    2 ноября 2010 г. 12:06
  • declare @str varchar(max)
    set @str = '
    if exists ( select * from information_schema.columns where Table_Name = parsename( "?", 1 ) 
                  and Table_Schema = parsename( "?", 2 )
                  and Column_Name=''id1''
       )
    begin 
    print "?"   
    exec (''delete from ? where id1 is null'')
    exec (''alter table ? alter column id1 int not null'')
    end
    
    if exists ( select * from information_schema.columns where Table_Name = parsename( "?", 1 ) 
                  and Table_Schema = parsename( "?", 2 )
                  and Column_Name=''id2''
       )
    begin 
    print "?"   
    exec (''delete from ? where id2 is null'')
    exec (''alter table ? alter column id2 int not null'')
    end
    '
    
    exec sp_MSforeachtable @str
    

    http://www.t-sql.ru
    • Предложено в качестве ответа Yury Iwtschenko 5 ноября 2010 г. 20:31
    • Помечено в качестве ответа kosmich74 10 ноября 2010 г. 14:23
    2 ноября 2010 г. 13:18
    Модератор

  • Спасибо за sp_MSforeachtable - нужная вещь. Но так сходу не конвертит - ругается что Conversion failed when converting the varchar value 'L' to data type int. Что удивительно там вообще символов кроме пробелов не видел О_О - че за чертевня...

    Написал апдейт с кастом - не помогает. Как сие лечится кроме лоботомии?)

    print "id1 in ?"
    exec (''UPDATE ? SET id1 = CAST(RTRIM(LTRIM(id1)) as int) WHERE ISNUMERIC(id1)<> 1'')
    exec (''DELETE FROM ? WHERE id1 IS NULL'')
    exec (''ALTER TABLE ? ALTER COLUMN id1 INT NOT NULL'')
    end

    P.S. бесячая непредвиденная ошибка js! 2 дня с работы невозможно reply сделать!
    8 ноября 2010 г. 13:58
  • ?????

    exec (''UPDATE ? SET id1 = CAST(RTRIM(LTRIM(id1)) as int) WHERE ISNUMERIC(id1)<> 1'')  - ???

    Dolzhno byt'

    exec (''UPDATE ? SET id1 = CAST(RTRIM(LTRIM(id1)) as int) WHERE ISNUMERIC(id1)= 1'')  

    -----------------

    select cast('L' as int) -- vydadet etu zhe oshibku.

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value 'L' to data type int.



    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    8 ноября 2010 г. 14:22
  • Извиняюсь чет затроил - решил что if isnumeric(true) return 1 по привычке:) в принципе вообще убрал условие - пусть всех обработает! И все равно пишет Conversion failed when converting the varchar value 'L' to data type int. !
    use base1
    declare @t varchar(max),@p varchar(max)
    set @t = Cast(parsename("?",1) as varchar(max))
    set @p = ''ALTER TABLE ? ADD CONSTRAINT [DF_'' + @t + ''_id1] DEFAULT ((0)) FOR [id1]''
    if exists ( SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE Table_Name = parsename( "?", 1 ) 
           and Table_Schema = parsename( "?", 2 )
           and Column_Name=''id1''
      )
    begin 
    exec (''DELETE FROM ? WHERE id1 IS NULL or CAST(RTRIM(LTRIM(id1)) as int) > 2147483645 or CAST(RTRIM(LTRIM(id1)) as int) < 1'') 
    exec (''UPDATE ? SET id1 = CAST(RTRIM(LTRIM(id1)) as int)'') 
    exec (''ALTER TABLE ? ALTER COLUMN id1 INT NOT NULL'')
    exec (@p)
    end
    
    9 ноября 2010 г. 17:13
  •  

    Не будет так работать, если есть character дата в колонке. Надо вначале отфильтровать данные, которые можно привести к int, а потом использовать cast.

    В чем вообше проблема, и что этот странный код должен делать?

    Посмотрите этот блог 

    How to get information about all databases without a loop

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    9 ноября 2010 г. 17:46
  • то что в блоге мне не надо совсем. просто хотелось в сам запрос все уложить, но t-sql ** не умеет вкладывать try-catch и case\if куда так хочется и при экспшене при приведении типов сразу все вываливается. обошёлся так:

    CREATE FUNCTION dbo.VarcharToInt(@text varchar(max))
    RETURNS int
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    DECLARE @chislo int, @i int, @temp varchar(max);
    
    SELECT @i = patindex('%[!@#$%^&*()"a-zA-Z]%', @text)
    WHILE @i > 0
    BEGIN
    	SELECT @text = replace(@text, substring(@text, @i, 1), '')
    	SELECT @i = patindex('%[!@#$%^&*()"a-zA-Z]%', @text)
    END
    
    SET @temp = substring(RTRIM(LTRIM(@text)),0,11)
    
    SET @chislo =
       CASE 
    	 WHEN @text IS NULL THEN 0
         WHEN CAST(@temp as bigint) > 2147483645 THEN 0
         WHEN CAST(@temp as int) < 0 THEN 0
         ELSE CONVERT(int,@text)
       END
    
    RETURN(@chislo);
    END;
    
    declare @id1 varchar(max)
    set @id1 = '
    use base1
    declare @t varchar(max),@p varchar(max)
    set @t = Cast(parsename("?",1) as varchar(max))
    set @p = ''ALTER TABLE ? ADD CONSTRAINT [DF_'' + @t + ''_id1] DEFAULT ((0)) FOR [id1]''
    if exists ( SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE Table_Name = parsename( "?", 1 ) 
           and Table_Schema = parsename( "?", 2 )
           and Column_Name=''id1''
      )
    begin 
    exec (''UPDATE ? SET id1 = dbo.VarcharToInt(id1)'') 
    exec (''DELETE FROM ? WHERE id1 = 0'') 
    exec (''ALTER TABLE ? ALTER COLUMN id1 INT NOT NULL'')
    exec (@p)
    end
    '
    exec sp_MSforeachtable @id1
    

    10 ноября 2010 г. 14:21
  • unable to start t-sql debugging. could not attach to sql-server process on 'Computer' вылечил так:

    sp_addsrvrolemember 'DomainName\UserName', 'sysadmin'

    10 ноября 2010 г. 14:23
  • VarcharToInt mozhno proshe, esli xotim tol'ko pervoe chislo vzjat'.

    aaa9875dddd345zzz

    Esli xotim vzjat

    9875,

    to posmotrite

    Extracting numbers with SQL Server

    V ljubom sluchae, esli chislo > max(int), to etot kod v funkcii privedet k oshibke.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    10 ноября 2010 г. 14:45
  • ну я в case надеялся что bigint > max(int) сработает палюбому раньше чем CONVERT, как в дебаггере. больше ни на что фантазии не хватило - try-catch не поставишь :(
    10 ноября 2010 г. 15:02
  • Vmesto CASE ispol'zujte IF ELSE IF - togda budet rabotat'. I vnachale proverjate

    IF @Number > '2147483647' 
     set @ReturnInt = 0
    ELSE
      set @ReturnInt = cast(@Number as int)
    
    return @ReturnInt -- we only use positive numbers, right?
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    10 ноября 2010 г. 15:34
  • не знал в mssql <> вкатывают при сравнении 2 текстов. както по старинке входной параметр varchar ассоциировался только с like, patindex и substring
    10 ноября 2010 г. 16:23