CODEWITHSUNDEEP
sql-servert-sql
Justin
Justin

Reputation: 11

t-sql lost contexted

I not sure why I'm losing context at FROM @TableName at line 36. I got this code of stack and am trying to change it to output to the screen and not a table. SQL Server Management Studio does not know the variable but does knows that same variable from the line just above it.

DECLARE @TableName nvarchar(256), 
        @ColumnName nvarchar(128), 
        @SearchStr2 nvarchar(110)

SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%28217965%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
          AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped') = 0)

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
              AND TABLE_NAME = PARSENAME(@TableName, 1)
              AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
              AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            SELECT  @TableName + '.' + @ColumnName, LEFT( @ColumnName, 3630) 
            FROM @TableName
            WHERE @ColumnName + ' LIKE ' + @SearchStr2
        END
    END 
END

Upvotes: 0

Views: 44

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32145

        SELECT  @TableName + '.' + @ColumnName, LEFT( @ColumnName, 3630) 
        FROM @TableName
        WHERE @ColumnName + ' LIKE ' + @SearchStr2

This is not valid T-SQL. @TableName is a scalar variable, not a table variable. You can't use variable table names like this. You'd have to use dynamic SQL to accomplish what you're trying. Take a look at the EXEC() statement.

Try something like:

DECLARE @SQL VARCHAR(MAX);

....


IF @ColumnName IS NOT NULL
    BEGIN
        SELECT @SQL = 'SELECT ' + @TableName + '.' + @ColumnName + ', LEFT(' +
            @ColumnName + ', 3630) FROM ' + @TableName + ' WHERE ' + @ColumnName +
            ' LIKE ' + @SearchStr2;

        EXEC(@SQL);
    END
....

Upvotes: 1

Related Questions