如何使用substing来返回set变量

Question

当我运行它们时，它告诉我：关键字"SELECT"附近的语法不正确。但我需要把这个传给剩下的脚本--这些值用于搜索--这是唯一一个需要抛光的区块修改

 BEGIN
DECLARE @ResultsTable TABLE (ColumnName nvarchar(200), ColumnValue nvarchar(100))
DECLARE @ColumnName nvarchar(128), 
        @SearchStr2 nvarchar(110),
        @TableName nvarchar(256), 
        @TableName2 nvarchar(256)
DECLARE @PolicyPrefix nvarchar(5), 
        @PolicyID nvarchar(10), 
        @PolicyEffDate date, 
        @Policy nvarchar(10),  --needless ,
SET @TableName = ' '
SET @PolicyPrefix = SELECT SUBSTRING(Policy, 1, 5) 
                    FROM [dbo].[DWXP050] 
                WHERE intcov LIKE '%epl%' --'CMPMO' ------notices it only the policy letter part from the search result
SET @PolicyID = SELECT SUBSTRING(Policy,6,len(Policy)-5) 
                FROM [dbo].[DWXP050] 
             WHERE intcov LIKE '%epl%'  ---'08929' ------notices it only the policy number part from the search result
SET @PolicyEffDate = SELECT cast(cast(EFFDTE as varchar)as date) 
                     FROM [dbo].[DWXP050] 
                 WHERE intcov LIKE '%epl%' ----'2009-11-01' ------notices it has dashes from the search result

Answer 1

尝试：

SELECT @PolicyPrefix = SUBSTRING(Policy, 1, 5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%' --'CMPMO' ------notices it only the policy letter part from the search result
SELECT @PolicyID = SUBSTRING(Policy, 6) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'---'08929' ------notices it only the policy number part from the search result
SELECT @PolicyEffDate = cast(cast(EFFDTE as varchar)as date) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%' ----'2009-11-01' ------notices it has dashes from the search result

Answer 2

SET @PolicyID = SELECT SUBSTRING(Policy, 6) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'---'08929' ------notices it only the policy number part from the search result

您尚未在此行中为子字符串函数添加第三个参数。添加

Answer 3

将select语句放入（）中，如-

SET @PolicyPrefix = (SELECT SUBSTRING(Policy, 1, 5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%')

在@policy nvarchar（10）之后，您有了Unused（，）

Answer 4

'/这些是我所做的更改，这是对我有效的答案。谢谢大家。/'

  BEGIN
    DECLARE @ResultsTable TABLE ( ColumnName  nvarchar(200)
    ,                             ColumnValue nvarchar(100) )
    DECLARE @ColumnName nvarchar(128)
    ,       @SearchStr2 nvarchar(110)
    ,       @TableName  nvarchar(256)
    ,       @TableName2 nvarchar(256)
    DECLARE @PolicyPrefix  nvarchar(5)
    ,       @PolicyID      nvarchar(10)
    ,       @PolicyEffDate date
    ,       @Policy        nvarchar(13)
    --SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
    SET @TableName = ' '
    --These values are used to search
    SET @PolicyPrefix = (SELECT TOP 1 SUBSTRING(Policy, 1, 5)
    FROM [dbo].[DWXP050]
    WHERE intcov LIKE '%epl%') --'CMPMO' ------notices it only the policy letter part from the search result
    SET @PolicyID = (SELECT TOP 1 SUBSTRING(Policy,6,len(Policy)-5)
    FROM [dbo].[DWXP050]
    WHERE intcov LIKE '%epl%')---'08929' ------notices it only the policy number part from the search result
    SET @PolicyEffDate = (SELECT TOP 1 cast(cast(EFFDTE as varchar)as date)
    FROM [dbo].[DWXP050]
    WHERE intcov LIKE '%epl%') ----'2009-11-01' ------notices it has dashes from the search result
    SET @SearchStr2 = (SELECT [SystemAssignId]
    FROM dbo.[CoPolicyPointer]
    WHERE [PolicyPrefixCd] = @PolicyPrefix
        AND [PolicyId] = @PolicyID
        AND [PolicyEffDt] = @PolicyEffDate)
    WHILE (@TableName IS NOT NULL)
    BEGIN
        SET @ColumnName = QUOTENAME('SystemAssignId')
        SET @TableName = (
        SELECT MIN(QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_NAME) > @TableName
        )
        SET @TableName2 = 'dbo.' + @TableName
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @ResultsTable
            EXEC(
            'SELECT ''' + @TableName + ''', LEFT(' + @ColumnName + ', 100)
FROM ' + @TableName2 + ' (NOLOCK) ' +
            ' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + ''''
            )
        END
    END
    SELECT DISTINCT ColumnName
    ,               ColumnValue
    FROM @ResultsTable
END