USE [tam]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_FldType] (@tabName nvarchar(50), @fldName nvarchar(50))
RETURNS nvarchar(50)
AS
BEGIN
	DECLARE @fldType nvarchar(50)

	SELECT @fldType = 
		CONVERT(nvarchar,(SELECT TYPE_NAME(c.user_type_id) AS TypeName	
		FROM sys.objects AS o JOIN sys.columns AS c  ON o.object_id = c.object_id
		WHERE o.name=@tabName and c.name=@fldName))	

	RETURN @fldType
END

USE [tam]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Данные в строку
ALTER PROCEDURE [dbo].[hp_SQLstr] 
	@SQL nvarchar(3000), @tabName nvarchar(50), @fldName nvarchar(50), @spr nvarchar(5), @ResultVar nvarchar(3000) OUTPUT
AS
DECLARE @fldType nvarchar(50), @fldName_tmp nvarchar(50), @SQL_tmp nvarchar(3000)
 
BEGIN
	SET NOCOUNT ON;

	SELECT @fldType = (SELECT dbo.fn_FldType(@tabName, @fldName))
	IF @fldType = 'int' OR @fldType = 'decimal'
		SET @fldName_tmp = 'CONVERT(nvarchar,' + @fldName + ')'
	IF @fldType = 'smalldatetime' OR @fldType = 'datetime'
		SET @fldName_tmp = 'CONVERT(nvarchar,' + @fldName + ', 104)'
	
	If CHARINDEX('ORDER BY', @SQL) > 0 And CHARINDEX('TOP', @SQL) = 0 
		SET @SQL = REPLACE(@SQL, 'SELECT ', 'SELECT TOP 100 PERCENT ')

	SET @SQL_tmp =
		'SELECT @ResultVar = (SELECT (SELECT DISTINCT ' + @fldName_tmp + '+''' + @spr + '''' + 
		' FROM (' + REPLACE(@SQL, '''', '''''') + ') AS tmp FOR XML PATH('''')) collate Cyrillic_General_100_CI_AS)'
	EXEC sp_executesql @SQL_tmp, N'@ResultVar nvarchar(3000) OUTPUT', @ResultVar = @ResultVar OUTPUT
	IF @ResultVar <> '' 
		SET @ResultVar = SUBSTRING(@ResultVar, 1, Len(@ResultVar) - 1)
END

USE tam;
DECLARE @ResultVar nvarchar(1000);
EXECUTE hp_SQLstr 'SELECT Zak_ID FROM tab_Zakaz', 'tab_Zakaz', 'Zak_ID', ',', @ResultVar OUTPUT;
SELECT @ResultVar;

Dim sSQL As String, ResultVar As String

sSQL = _
    "USE tam;" & _
    "DECLARE @ResultVar nvarchar(1000);" & _
    "EXECUTE hp_SQLstr 'SELECT Zak_ID FROM tab_Zakaz', 'tab_Zakaz', 'Zak_ID', ',', @ResultVar OUTPUT;" & _
    "SELECT @ResultVar AS RecVar;"
ResultVar = Nz(con.Execute(sSQL).NextRecordset.Fields(0), "")

Dim ResultVar As String
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "hp_SQLstr"

cmd.Parameters.Append cmd.CreateParameter("@SQL", adVarWChar, adParamInput, 3000, "select zak_id from tab_zakaz")
cmd.Parameters.Append cmd.CreateParameter("@tabName", adVarWChar, adParamInput, 50, "tab_Zakaz")
cmd.Parameters.Append cmd.CreateParameter("@fldName", adVarWChar, adParamInput, 50, "Zak_ID")
cmd.Parameters.Append cmd.CreateParameter("@spr", adVarWChar, adParamInput, 5, ",")
cmd.Parameters.Append cmd.CreateParameter("@ResultVar", adVarWChar, adParamOutput, 3000)
cmd.Execute , , adExecuteNoRecords
ResultVar = cmd.Parameters("@ResultVar").Value

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "hp_SQLstr"
cmd.Parameters.Refresh
cmd.Execute , Array("select zak_id from tab_zakaz", "tab_Zakaz", "Zak_ID", ",", ""), adExecuteNoRecords
fun = cmd.Parameters("@ResultVar").Value

USE [tam]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Вставка цен ТМЦ по разрешительным документам
ALTER PROCEDURE [dbo].[hp_InsertWrDoc] 
	@MatCenaID INT, @KartID INT, @RecCnt INT OUTPUT
AS
DECLARE @FROM nvarchar(1000), @SQL nvarchar(3000)
 
BEGIN
	SET NOCOUNT ON;

	--вставка в карточку ТМЦ
	IF @MatCenaID = 0
		BEGIN
			SET @FROM = 
				'FROM	tab_WRDoc INNER JOIN 
						tab_UKT_ZED_WRDoc ON tab_WRDoc.WRD_ID = tab_UKT_ZED_WRDoc.WRD_ID INNER JOIN 
						tab_MKart ON tab_UKT_ZED_WRDoc.UKT_ID = tab_MKart.UKT_ID 
				 WHERE  tab_MKart.Kart_ID=' + CONVERT(nvarchar,@KartID) + ' AND tab_WRDoc.WRD_ID NOT IN
						(SELECT WRD_ID 
						FROM	tab_MCena_WRDoc 
						WHERE	Kart_ID=' +  CONVERT(nvarchar,@KartID) + ')'
			SET @SQL = 'SELECT @RecCnt = COUNT(*) ' + @FROM 
			EXEC sp_executesql @SQL, N'@RecCnt INT OUTPUT', @RecCnt = @RecCnt OUTPUT
			IF  @RecCnt > 0
				BEGIN
					SET @SQL = 
						'INSERT INTO tab_MCena_WRDoc (Kart_ID, WRD_ID)
						SELECT ' + CONVERT(nvarchar,@KartID) + ',tab_WRDoc.WRD_ID ' + @FROM
					EXEC sp_executesql @SQL
				END
		END
	ELSE --вставка в цены ТМЦ
		BEGIN
	        SET @FROM = 
            'FROM   tab_MCena_WRDoc 
			WHERE	Kart_ID=' +  CONVERT(nvarchar,@KartID) + ' AND ISNULL(NumDoc,N'''')='''' AND WRD_ID NOT IN
					(SELECT WRD_ID 
					FROM	tab_MCena_WRDoc 
					WHERE	MatCena_ID=' + CONVERT(nvarchar,@MatCenaID) + ')'
			SET @SQL = 'SELECT @RecCnt = COUNT(*) ' + @FROM
			EXEC sp_executesql @SQL, N'@RecCnt INT OUTPUT', @RecCnt = @RecCnt OUTPUT
			IF  @RecCnt > 0
				BEGIN
					SET @SQL = 
						'INSERT INTO tab_MCena_WRDoc(MatCena_ID, WRD_ID)
						SELECT ' + CONVERT(nvarchar,@MatCenaID) + ',tab_MCena_WRDoc.WRD_ID ' + @FROM
					EXEC sp_executesql @SQL
				END
		END
END

Function funSQLStr(ByVal sSQL As String, ByVal sTabFieldName As String, Optional ByVal sSeparator As String) As String
Dim cmd As ADODB.Command

On Error GoTo Err_
    If sSeparator = "" Then sSeparator = ","

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CollCon(ConName)
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "hp_SQLstr"
    
    cmd.Parameters.Append cmd.CreateParameter("@SQL", adVarWChar, adParamInput, 3000, sSQL)
    cmd.Parameters.Append cmd.CreateParameter("@tabName", adVarWChar, adParamInput, 50, Split(sTabFieldName, ".")(0))
    cmd.Parameters.Append cmd.CreateParameter("@fldName", adVarWChar, adParamInput, 50, Split(sTabFieldName, ".")(1))
    cmd.Parameters.Append cmd.CreateParameter("@spr", adVarWChar, adParamInput, 5, sSeparator)
    cmd.Parameters.Append cmd.CreateParameter("@ResultVar", adVarWChar, adParamOutput, 3000)
    cmd.Execute , , adExecuteNoRecords
    funSQLStr = cmd.Parameters("@ResultVar").Value
Exit Function

Err_:
    Call ErrorBases(Err, "mdl_Tools", "funSQLStr")
End Function

ResultVar = Nz(con.Execute("SELECT dbo.fn_FldType('tab_Zakaz','Zak_ID')"), "")

USE [tam]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Вставка разрешительных документов
ALTER PROCEDURE [dbo].[hp_InsertMCenaWrDocZak] 
	@ZakID INT, @sInsertRecords varchar(3000), @RecCount INT OUTPUT
AS
DECLARE @MatCenaID INT

BEGIN
	SET NOCOUNT ON;

	IF (SELECT Kart_ID FROM tab_MCena WHERE Zak_ID = @ZakID) = 0
		BEGIN
			DECLARE Cur1 CURSOR LOCAL FAST_FORWARD FOR
			SELECT MatCena_ID FROM tab_MCena Where MatCena_ID IN(REPLACE(@sInsertRecords, '|', ','))
		END
	ELSE
		BEGIN
			DECLARE Cur1 CURSOR LOCAL FAST_FORWARD FOR
			SELECT MatCena_ID FROM tab_MCena WHERE MatCena_ID IN(REPLACE(@sInsertRecords, '|', ',')) AND Kart_ID NOT IN(SELECT Kart_ID FROM tab_MCena WHERE Zak_ID = @ZakID)  
		END

	SET @RecCount = 0
	OPEN Cur1
	FETCH NEXT FROM cur1 INTO @MatCenaID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC dbo.hp_InsertMCenaWrDocZak_sub @ZakID, @MatCenaID
		FETCH NEXT FROM Cur1 INTO @MatCenaID
		SET @RecCount = @RecCount + 1
	END

	CLOSE Cur1
	DEALLOCATE Cur1
END

select zak_id from tab_zakaz

select convert(varchar, zak_id) as Rec from tab_zakaz

select convert(varchar, zak_id) + ' - ' convert(varchar, zak_id) AS ...

' ... FROM (' + @SQL + ')...

CREATE VIEW v_tmp
AS 
SELECT ... AS Rec
FROM ....
WHERE ...

USE [tam]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Источники данных форм и контролов
ALTER PROCEDURE [dbo].[hp_ObjSource] 
	@ObjName varchar(50), @RecKey INT = 0, @RecKeyList nvarchar(50) = ''
AS
DECLARE @DopWhere nvarchar(50), @SQL nvarchar(1000)
 
BEGIN
	SET NOCOUNT ON;

	--Списки
	IF @ObjName = 'UP_ID'
		SELECT UP_ID, UPKod + ' - ' + UPName AS UKod FROM tab_UsPostawki WHERE Sost=0 ORDER BY UPKod
	IF @ObjName = 'TR_ID'
		SELECT TR_ID, NameYur FROM tab_Customs_Region WHERE Sost=0 ORDER BY NameYur
	IF @ObjName = 'TTD_ID'
		SELECT TTD_ID, TTName FROM tab_Decl_Type WHERE Sost=0 ORDER BY TTName
	IF @ObjName = 'WName'
		SELECT WName FROM tab_WType WHERE Sost=0 ORDER BY WName
	IF @ObjName = 'CT_ID'
		SELECT CT_ID, Alfa2 + ' - ' + Country_Ukr FROM tab_Country WHERE Sost=0 ORDER BY Country_Ukr
	IF @ObjName = 'BR_ID'
		SELECT tab_Border.BR_ID, tab_Border.Name_In + ' - ' + tab_Border.Name_Out + ' (' + tab_Country.Country_Ukr + ')' AS RecName
        FROM   tab_Border INNER JOIN
               tab_Country ON tab_Border.CT_ID = tab_Country.CT_ID
        WHERE  tab_Border.Sost=0
        ORDER BY tab_Border.Favor DESC, RecName
	IF @ObjName = 'TR_Key'
		SELECT TR_Key, NameYur FROM tab_Customs_Region WHERE Sost=0 ORDER BY NameYur
	IF @ObjName = 'OrgKey'
		BEGIN
			IF (SELECT COUNT(*) FROM sys.objects AS O WHERE O.NAME='v_tmp') > 0
				DROP VIEW v_tmp
			ELSE
				EXEC ('CREATE VIEW v_tmp AS
					SELECT	OrgType, OrgKey, CASE WHEN ISNULL(NameSokr, '''')='''' THEN NameYur ELSE NameSokr END AS RecFld
					FROM	tab_Org
					WHERE	Sost=0')
			IF @RecKeyList = ''
				SELECT	OrgKey, RecFld
				FROM	v_tmp
				WHERE	OrgType=@RecKey
			ELSE
				BEGIN
					IF CHARINDEX('NOTIN', @RecKeyList) > 0
						BEGIN
							SET @DopWhere = SUBSTRING(@RecKeyList, 7, Len(@RecKeyList));
							SELECT	OrgKey, RecFld
							FROM	v_tmp
							WHERE	OrgType NOT IN(SELECT number FROM dbo.iter_intlist_to_table(@DopWhere))
						END					
					ELSE
						IF CHARINDEX('IN', @RecKeyList) > 0
							BEGIN
								SET @DopWhere = SUBSTRING(@RecKeyList, 3, Len(@RecKeyList));
								SELECT	OrgKey, RecFld
								FROM	v_tmp
								WHERE	OrgType IN(SELECT number FROM dbo.iter_intlist_to_table(@DopWhere))
							END									
				END
			DROP VIEW v_tmp
		END
END

Set Me.OrgKey.Recordset = con.Execute("EXEC hp_ObjSource 'OrgKey', @RecKeyList='IN 1 3'")
Set Me.OrgKey1.Recordset = Me.OrgKey.Recordset
Set Me.OrgKey2.Recordset = con.Execute("EXEC hp_ObjSource 'OrgKey', @RecKeyList='NOTIN 1 3'")

IF @ObjName = 'CT_ID'
    SELECT CT_ID, Alfa2 + ' - ' + Country_Ukr FROM tab_Country WHERE Sost=0 ORDER BY Country_Ukr
IF @ObjName = 'CTyp_ID'
   SELECT CTyp_ID, CTypName FROM tab_MCenaType WHERE Sost=0 ORDER BY CTypName
IF @ObjName = 'DC_ID'
   SELECT DC_ID, DocType FROM tab_Document_Type WHERE Sost=0 ORDER BY DocType
...

IF @ObjName = 'OrgKey'
	BEGIN
		IF @RecKeyList = ''
			BEGIN
				IF @RecKey <> 0 AND @RecKey1 = 0
					SELECT	OrgKey, RecFld
					FROM	qry_hp_ObjSource_sub
					WHERE	OrgType=@RecKey
					ORDER BY NameYur
				IF @RecKey = 0 AND @RecKey1 <> 0
					SELECT	OrgKey, RecFld
					FROM	qry_hp_ObjSource_sub
					WHERE	OrgKey=@RecKey
					ORDER BY NameYur
				IF @RecKey <> 0 AND @RecKey1 <> 0
					SELECT	OrgKey, RecFld
					FROM	qry_hp_ObjSource_sub
					WHERE	OrgType=@RecKey AND OrgKey = @RecKey1
					ORDER BY NameYur								
			END
		ELSE
			BEGIN
				IF CHARINDEX(N'NOTIN', @RecKeyList) > 0
					BEGIN
						SET @DopWhere = SUBSTRING(@RecKeyList, 7, Len(@RecKeyList));
						SELECT	OrgKey, RecFld
						FROM	qry_hp_ObjSource_sub
						WHERE	OrgType NOT IN(SELECT number FROM dbo.iter_intlist_to_table(@DopWhere))
						ORDER BY NameYur
					END					
				ELSE
					IF CHARINDEX(N'IN', @RecKeyList) > 0
						BEGIN
							SET @DopWhere = SUBSTRING(@RecKeyList, 3, Len(@RecKeyList));
							SELECT	OrgKey, RecFld
							FROM	qry_hp_ObjSource_sub
							WHERE	OrgType IN(SELECT number FROM dbo.iter_intlist_to_table(@DopWhere))
							ORDER BY NameYur
						END									
			END
	END

If sLookup("SELECT COUNT(OrgKey2) AS ReCnt FROM tab_Org WHERE OrgKey=" & Me.OrgKey1) > 0 Then
    If MsgBox("бла бла бла?", vbExclamation + vbYesNo + vbDefaultButton2, NomWers) = vbNo Then
        Exit Sub
    Else
        con.Execute "hp_InsertZakazIsp " & Me.TTD_ID & "," & Me.OrgKey1 & "," & Me.Zak_ID
    End If
End If

Function funInsertDopKod(ByVal Kart_ID As Long, ByVal UKT_ID As Long) As Boolean
Dim flg_Trans As Boolean

On Error GoTo Err_
    If Nz(sLookup("SELECT Count(*) AS RecCnt FROM tab_UKT_ZED_DopKodInvois WHERE UKT_ID=" & UKT_ID), 0) = 0 Then
        If Nz(sLookup("SELECT Count(*) AS RecCnt FROM tab_MKart_DopKodInvois WHERE Kart_ID=" & Kart_ID), 0) > 0 Then
            If MsgBox("Для обраного коду УКТ ЗЕД відсутні дані по додатковим кодам в Електронний Інвойс, 
але додаткові коди з іншого коду УКТ ЗЕД вже присутні в картці товару. Видалити їх?", 
vbExclamation + vbYesNo + vbDefaultButton2, NomWers) = vbNo Then Exit Function
            con.Execute "Delete From tab_MKart_DopKodInvois Where Kart_ID=" & Kart_ID
            funInsertDopKod = True
        End If
    Else
        If MsgBox("Присутні дані по додатковим кодам в Електронний Інвойс. Додати їх в картку товару? Попередні дані будуть видалені.",
 vbExclamation + vbYesNo + vbDefaultButton2, NomWers) = vbNo Then Exit Function
'***************<Начало транзакции>********************
        con.BeginTrans
        flg_Trans = True
        
        con.Execute "Delete From tab_MKart_DopKodInvois Where Kart_ID=" & Kart_ID
        con.Execute _
            "Insert Into tab_MKart_DopKodInvois(Kart_ID, DK_ID) " & _
            "SELECT " & Kart_ID & ",DK_ID " & _
            "FROM   tab_UKT_ZED_DopKodInvois " & _
            "WHERE  UKT_ID=" & UKT_ID

        con.CommitTrans
        flg_Trans = False
'***************<Конец/ транзакции>********************
        funInsertDopKod = True
    End If
Exit Function

Err_:
    If flg_Trans = True Then con.RollbackTrans
    Call ErrorBases(Err, "mdl_FormFun", "InsertDopKod")
End Function

Function funInsertDopKod(ByVal Kart_ID As Long, ByVal UKT_ID As Long) As Boolean
Dim Result As Integer
Dim rs As ADODB.Recordset
Dim sErrMsg As String, sConfirms As String

On Error GoTo Err_
    Set rs = New ADODB.Recordset
lab:
    Set rs = con.Execute( _
            "USE tam;" & _
            "DECLARE @ErrMsg nvarchar(1000)='" & sErrMsg & "',@Confirms nvarchar(1000)='" & sConfirms & "',@Result INT;" & _
            "EXECUTE hp_InsertDopKod " & Kart_ID & "," & UKT_ID & ",@ErrMsg OUTPUT,@Confirms OUTPUT,@Result OUTPUT;" & _
            "SELECT @ErrMsg AS Cnt;SELECT @Confirms AS Cnt;SELECT @Result AS Cnt")
    sErrMsg = Nz(rs.NextRecordset.Fields(0).Value, "")
    sConfirms = Nz(rs.NextRecordset.Fields(0).Value, "")
    Result = Nz(rs.NextRecordset.Fields(0).Value, 0)
    
    Select Case Result
      Case -1
        MsgBox sErrMsg, vbCritical, NomWers
        Exit Function
      Case -2
        If MsgBox(sErrMsg, vbExclamation + vbYesNo, NomWers) = vbYes Then GoTo lab
    End Select
    funInsertDopKod = True
Exit Function

Err_:
    Call ErrorBases(Err, "mdl_FormFun", "InsertDopKod")
End Function

ALTER PROCEDURE [dbo].[hp_InsertDopKod]
	@Kart_ID INT,
	@UKT_ID INT,
	@ErrMsg nvarchar(1000)=N'' OUTPUT,
	@Confirms nvarchar(1000)=N'' OUTPUT,
	@Result INT OUTPUT
AS
 
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS(SELECT * FROM tab_UKT_ZED_DopKodInvois WHERE UKT_ID=@UKT_ID)
		IF EXISTS(SELECT * FROM tab_MKart_DopKodInvois WHERE Kart_ID=@Kart_ID) 
			IF CHARINDeX(N'check1', @Confirms) = 0 
				BEGIN
					SET @ErrMsg = 'Для обраного коду УКТ ЗЕД відсутні дані по додатковим кодам в Електронний Інвойс,
 але додаткові коди з іншого коду УКТ ЗЕД вже присутні в картці товару. Видалити їх?'
					SET @Confirms = @Confirms + N'check1'
					SET @Result = -2
				END
			ELSE
				DELETE FROM tab_MKart_DopKodInvois WHERE Kart_ID=@Kart_ID
	ELSE
		IF CHARINDeX(N'check2', @Confirms) = 0
			BEGIN			
				SET @ErrMsg = 'Присутні дані по додатковим кодам в Електронний Інвойс. 
Додати їх в картку товару? Попередні дані будуть видалені.'
				SET @Confirms = @Confirms + N'check2'
				SET @Result = -2
			END
		ELSE
			BEGIN
				BEGIN TRY
					BEGIN TRANSACTION
						DELETE FROM tab_MKart_DopKodInvois WHERE Kart_ID=@Kart_ID;
						INSERT INTO tab_MKart_DopKodInvois(Kart_ID, DK_ID)
						SELECT @Kart_ID, DK_ID
						FROM   tab_UKT_ZED_DopKodInvois
						WHERE  UKT_ID=@UKT_ID;
					COMMIT TRANSACTION
				END TRY
				BEGIN CATCH
					ROLLBACK
					SET @ErrMsg = 'Произошел откат транзакции';
					THROW
					SET @Result = -1
				END CATCH
			END
END

If (Условие)=0 Then Exit Sub