Ответы пользователя по тегу SQL Server
  • Почему mssql выдает ошибку?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    DELETE e
    FROM #employees e
    INNER JOIN (
    	SELECT id
    		,RANK() OVER (PARTITION BY pass_num ORDER BY ID) RNK
    	FROM #employees
    	) X ON X.id = e.id
    WHERE x.RNK > 1
    Ответ написан
    Комментировать
  • Как правильно создать процедуру в Ms Sql?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    В своём ответе вы уже написали как правильно использовать процедуру.

    Если вам нужно вызывать по типу "dbo.SUMOFNUMBERS(5,6)". Используйте функцию.
    CREATE FUNCTION SUMOFNUMBERS 
    (
    	@FirstNumber INT,
    	@SecondNumber INT
    )
    RETURNS int
    AS
    BEGIN
    	-- Declare the return variable here
    	DECLARE @Answer int
    
    	-- Add the T-SQL statements to compute the return value here
    	SET @Answer = @FirstNumber + @SecondNumber;
    
    	-- Return the result of the function
    	RETURN @Answer
    END

    Используем: select dbo.SUMOFNUMBERS(5,6)

    Но если душа просит процедуру, могу предложить такой вариант:
    CREATE PROCEDURE SUMOFNUMBERS
      @FirstNumber INT,
      @SecondNumber INT,
    AS
    BEGIN
      SET @Answer = @FirstNumber + @SecondNumber;
      RETURN @Answer;
    END
    
    
    
    EXEC     @Answer =  SUMOFNUMBERS 5, 6
    Ответ написан
    1 комментарий
  • Как при помощи триггеров удалить записи в связанных таблицах в ms sql server?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Вот любим мы всякие извращения...
    Вы, про каскадное удаление слышали?
    Пример:
    ALTER TABLE [dbo].[OrderGoods]  WITH CHECK ADD  CONSTRAINT [FK_OrderGoods_OrderHeader] FOREIGN KEY([OrderHeaderGUID])
    REFERENCES [dbo].[OrderHeader] ([GUID])
    ON DELETE CASCADE
    GO
    
    ALTER TABLE [dbo].[OrderGoods] CHECK CONSTRAINT [FK_OrderGoods_OrderHeader]
    GO


    В коде нужно обратить внимание на ON DELETE CASCADE

    То есть при удалении заголовка, удалятся и данные тушки.
    Если совсем начинающий:
    Правый клик - отношения.
    И дальше как на картинке.
    019719c935be42fab97234421085cf15.png

    И не советую использовать триггеры - они сложно отлаживаются и если вдруг в нем будет ошибка вы просто потеряете часть данных при работе с таблицей.
    Ответ написан
    Комментировать
  • Как получить значения времени в Select from values?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Это для дат, переделать ее в вид нужный вам совсем не трудно.

    CREATE FUNCTION [dbo].[GetDateRangeShort] (@StartDate datetime, @EndDate datetime)
    RETURNS @Res TABLE (Date datetime) AS
    BEGIN
    		;WITH MyEmp (Date, Level)
    		AS(
    			SELECT @StartDate, 1
    			UNION ALL
    			SELECT DATEADD(dd, 1, Date), Level + 1
    			FROM MyEmp
    			WHERE (Date < @EndDate)
    		)
    		INSERT INTO @Res (Date) SELECT Date FROM MyEmp OPTION (MAXRECURSION 0);
    
    	RETURN
    END
    Ответ написан
    Комментировать
  • Как реализовать блокировки в базе данных MSSQL?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Может многие меня не поддержат. Но у меня стояла задача разблокировать редактирование, при этом оставив просмотр для остальных. Оставлять одну только проверку версии казалось мало, а реализовывать через транзацию побоялся: неизвестно как будет себя вести изоляция, если ею раньше не пользовался.
    Реализация была через три дополнительных поля и пару процедур. Три на самом деле избыточно. Хватило бы и два. В общем этакий велосипед.
    Поля в заголовке:
    • Идентификатор пользователя UserEditGUID uniqueidentifier
    • Дата когда пользователь начал редактировать BeginEdit datetime
    • Не особо нужное поле isEdit bit


    И собственно процедура блокировки:
    ALTER PROCEDURE [dbo].[LockUnLockOrder]
    	@HeadGUID uniqueidentifier, @UserGUID  uniqueidentifier, @Lock bit
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	if @HeadGUID is null or @UserGUID is null or @Lock is null 
    	begin
    	raiserror('Процедура LockUnLockOrder: Неинициализированный параметр',16,1)
    	return -1
    	end
    if V_ORDER.dbo.CheckEditStatus(@HeadGUID,@UserGUID) = 1
    begin
    	if 	@Lock = 1			
    	UPDATE [V_ORDER].[dbo].[OrderHeader]
    	SET isEdit = 1
    		,BeginEdit = getdate()
    		,UserEdit = @UserGUID
    	WHERE GUID = @HeadGUID
    	else 
    	if 	@Lock = 0			
    	UPDATE [V_ORDER].[dbo].[OrderHeader]
    	SET isEdit = 0
    		,BeginEdit = null
    		,UserEdit = null 
    	WHERE GUID = @HeadGUID
    	return 1
    end
    else raiserror('Невозможно изменить статус заказа',16,1)
    end


    И функция проверки возможности редактирования. Если пользователь открыл час назад, и не закрыл, то разрешение на редактирование выдается.
    -- =============================================
    -- Author:		k1lex
    -- Create date: 20160818
    -- Description:	функция возвращает разрешение редактирования заказа. 1 - можно редактировать. 0 - нельзя
    -- =============================================
    ALTER FUNCTION [dbo].[CheckEditStatus]
    (
    	-- Add the parameters for the function here
    	@HeadGUID uniqueidentifier, @UserGUID  uniqueidentifier
    )
    RETURNS bit
    AS
    BEGIN
    DECLARE @bit BIT
    
    IF (
    		SELECT COUNT(*)
    		FROM [V_ORDER].[dbo].[OrderHeader] O WITH (NOLOCK)
    		WHERE O.GUID = @HeadGUID
    			AND (ISNULL(O.IsReadOnly, 0) = 1 OR (O.userEdit != @UserGUID AND O.IsEdit = 1 AND DATEDIFF(HOUR, BeginEdit, GETDATE()) < 1)	
    			and [Status]=1
    			)
    		) = 0
    	SET @bit = 1
    ELSE
    	SET @bit = 0
    
    RETURN @bit
    END


    И поддержу Петр , что нужна проверка даты последнего изменения. На тот случай, если кто-то откроет, и вспомнит через три часа что открыл и отредактирует уже отредактированное.
    Ответ написан
    1 комментарий
  • Как увеличить производительность СУБД? Возможно ли хранить БД в оперативной памяти?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Индексы. И, пожалуй, самое важное - правильно написанные запросы в базу данных.
    Ответ написан
    Комментировать
  • Как сделать в MSSQL поиск дубликатов в одной из колонок, при условии совпадения по другой колонке и отсутствия дубликатов в третьей?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    select 
    t1.id ,  t1.project_id ,  t1.sum_1 ,  t1.sum_2
    from table t1
    inner join 
    ( select project_id , COUNT(sum_1) ac CT from  table group by project_id ) t2 ON t1.project_id=t2.project_id  and t2.CT>1
    inner join 
    ( select project_id , COUNT(sum_2) ac CT from  table group by project_id ) t3 ON t1.project_id=t3.project_id  and t3.CT=1
    Ответ написан
    Комментировать
  • Как выбрать из БД участки, у которых увеличилась площадь?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    IF OBJECT_ID('tempdb..#testtable') IS NOT NULL	DROP TABLE #testtable
    
    create table #testtable (number nvarchar(255), spac money, dateR date)
    insert into #testtable
    (number, spac, dateR)
    values
    (1,10,'20140505'),
    (1,11,'20141212'),
    (2,50,'20160505'),
    (2,40,'20160508')
    
    IF OBJECT_ID('tempdb..#testtableRNK') IS NOT NULL	DROP TABLE #testtableRNK
    
    select * ,
    rank() over (partition by Number order by dateR desc) as RNK
    into #testtableRNK
    from #testtable
    
    select * from #testtableRNK  r1
    inner join #testtableRNK  r2 ON r1.number= r2.number and r2.RNK=(r1.RNK+1)  and r1.RNK=1 and r2.spac< r1.spac
    Ответ написан
    Комментировать
  • В чем разница между LINQ to SQL и Entity Framework и когда, что лучше использовать?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Когда-то потратил очень много времени на поиск того что же лучше и чем они вообще отличаются.
    Поищите в интернете, там всё по полочкам разложено.
    Но если коротко: Используйте Entity Framework. LINQ to SQL уже давно устарел и проект фактически закрыт. EF же развивается, хоть и с черепашей скоростью
    Ответ написан
    Комментировать
  • Почему не записывается кириллица в поле типа text MS-SQL?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Наверное не хватает ковычек.
    INSERT INTO [Orders] [Address]) VALUES ('0xc8ecff20d4e0ece8ebe8ff20cef2f7e5f1f2e2ee')

    Но я бы на вашем месте не использовал тип TEXT - в новых версиях от него откажутся. Используйте nvarchar(max)
    Ответ написан
    Комментировать
  • Какой индекс выбрать ASC или DESC?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Я не очень пойму чего вы ходите добиться.
    ASC и DESC всего лишь направления для сортировки.

    SELECT TOP 50 * FROM table ORDER BY id DESC - даст вам 50 последних записей
    SELECT TOP 50 * FROM table ORDER BY id ASC - даст вам 50 первых записей.

    Поясните, может это я еще дзен не постиг?
    Ответ написан
    1 комментарий
  • Где ошибка в назначении переменных в хранимой процедуре sql?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Вы неправильно написали получение значения переменной. Обратите внимание на скобки
    SET @par1 = (  select [поле] from [таблица1] WHERE ....  )

    ну или можно написать
    select @par1 =[поле] from [таблица1] WHERE .... ;
    Ответ написан
  • Kак добавить заполняющееся поле в готовую таблицу sql?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    alter table marki add id int not null identity(1,1) primary key
    Ответ написан
    1 комментарий
  • Как конвертировать ObservableCollection в DataTable?

    k1lex
    @k1lex Автор вопроса
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    В итоге решил так (код взят из интернета).
    Относительно универсальный конвертер LIST в DataTable
    public DataTable ToDataTable<T>(List<T> items)
            {
                var tb = new DataTable(typeof(T).Name);
    
                PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    
                foreach (PropertyInfo prop in props)
                {
                    Type t = GetCoreType(prop.PropertyType);
                    tb.Columns.Add(prop.Name, t);
                }
    
    
                foreach (T item in items)
                {
                    var values = new object[props.Length];
    
                    for (int i = 0; i < props.Length; i++)
                    {
                        values[i] = props[i].GetValue(item, null);
                    }
    
                    tb.Rows.Add(values);
                }    return tb;
            }
    
     public static bool IsNullable(Type t)
            {
                return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
            }
    
     public static Type GetCoreType(Type t)
            {
                if (t != null && IsNullable(t))
                {
                    if (!t.IsValueType)
                    {
                        return t;
                    }
                    else
                    {
                        return Nullable.GetUnderlyingType(t);
                    }
                }
                else
                {
                    return t;
                }
            }
    Ответ написан
  • Как правильно написать sql-запрос?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    select 
     M.имя_маршрута
    ,G.имя_города 
    ,G2.имя_города
    from Маршруты M 
    inner join Города G ON  M.id_пункта_отправления=G.ID
    inner join Города G2  ON  M.id_пункта_назначения=G2.ID
    Ответ написан
    Комментировать
  • Как быстро отсортировать и выбрать данные из 10000+ строк таблицы в C#?

    k1lex
    @k1lex
    Программист торг. сети. C# (WPF, WinForms), T-SQL
    Еще вариант на уровне MS SQL распарсить файл, сделать необходимы отбор и передать данные в приложение.
    Ниже даю пример парсинга xml-файла в таблицу. Попробуйте переделать под себя
    Взял кусок из рабочего примера, где использовались пространства имен в файле. Если у вас их нет - удалите
    declare @xdat xml 
    
    -- получаем xml-файл
    SET @xdat  = (SELECT [DataXml]  FROM [TaskXml]  where TaskDocGUID='1A91AED3-B5DF-4526-81E9-D54C16BB82A7')
    
    if object_id('TempDb..#AllDataTable') > 0 drop table #AllDataTable
    SELECT Product.ref.value('declare namespace pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:Type/text())[1]', 'nvarchar(50)') AS [Type]
    	,Product.ref.value('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:FullName/text())[1]', 'nvarchar(255)') AS FullName
    	,Product.ref.value('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";  (pref:ShortName/text())[1]', 'nvarchar(64)') AS ShortName
    	,Product.ref.query('declare namespace   pref="http://fsrar.ru/WEGAIS/ProductRef";   pref:Producer[1]') AS Producer
    	,Producer.ref.value('declare namespace  oref="http://fsrar.ru/WEGAIS/ClientRef";   (oref:ClientRegId/text())[1]', 'nvarchar(50)') AS ClientRegIdProducer
    	,Producer.ref.value('declare namespace  oref="http://fsrar.ru/WEGAIS/ClientRef";   (oref:INN/text())[1]', 'nvarchar(255)') AS INNProducer
    INTO #AllDataTable
    FROM @xdat.nodes('declare namespace rap="http://fsrar.ru/WEGAIS/ReplyAP";
    				  declare namespace ns="http://fsrar.ru/WEGAIS/WB_DOC_SINGLE_01";
    				  /ns:Documents/ns:Document/ns:ReplyAP/rap:Products/rap:Product') AS Product(ref)
    CROSS APPLY Product.ref.nodes('declare namespace  pref="http://fsrar.ru/WEGAIS/ProductRef";  pref:Producer') AS Producer(ref)
    
     SELECT * FROM #AllDataTable


    Код выше трудно читаем и понимаем. Попробую дать пример попроще

    -- формируем докумет
    DECLARE @xdat XML = (
    		SELECT TOP 5 GUID
    			,StatusDoc
    		FROM V_EGAIS.dbo.ActChargeOnHeader
    		FOR XML path('List')
    			,ROOT('Document')
    			,ELEMENTS
    			,type
    		)
    
    -- ниже пример того что получилось
    --  <Document>
    --  <List>
    --    <GUID>7F705894-441F-4B59-B6C4-0003579AE9BA</GUID>
    --    <LDM>2016-02-04T15:58:01.650</LDM>
    --  </List>
    --  <List>
    --    <GUID>D6EADAEB-AD45-4EB9-962D-0003B82CB431</GUID>
    --    <LDM>2016-02-04T15:59:51.723</LDM>
    --  </List>
    --  <List>
    --    <GUID>84BA1332-7C1F-4BAB-9923-0003D4B2CD7A</GUID>
    --    <LDM>2016-02-04T15:58:57.443</LDM>
    --  </List>
    --  <List>
    --    <GUID>A56EF787-3549-43DD-99E8-00043ED70C11</GUID>
    --    <LDM>2016-02-04T15:56:59.223</LDM>
    --  </List>
    --  <List>
    --    <GUID>16939553-7927-4221-B260-00044EBC225F</GUID>
    --    <LDM>2016-02-04T15:57:29.297</LDM>
    --  </List>
    --</Document>
    -- теперь производим выборку
    SELECT Document.ref.value('(GUID/text())[1]', 'nvarchar(255)') AS [GUID]
    	     ,Document.ref.value('(StatusDoc/text())[1]', 'int') AS StatusDoc
    FROM @xdat.nodes('/Document/List') AS Document(ref) -- тут мы показываем с какого раздела можно начинать брать данные.
    Ответ написан
    3 комментария