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