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
ENDselect dbo.SUMOFNUMBERS(5,6)CREATE PROCEDURE SUMOFNUMBERS
@FirstNumber INT,
@SecondNumber INT,
AS
BEGIN
SET @Answer = @FirstNumber + @SecondNumber;
RETURN @Answer;
END
EXEC @Answer = SUMOFNUMBERS 5, 6ALTER 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
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
ENDALTER 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
ENDselect
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=1IF 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.spacSET @par1 = ( select [поле] from [таблица1] WHERE .... )select @par1 =[поле] from [таблица1] WHERE .... ;
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;
}
}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) -- тут мы показываем с какого раздела можно начинать брать данные.