Displaying Query Data Using Variable

  • I'm trying to integrate data from multiple table names with exactly the same structure. When I create the procedure I get an error asking that I declare the variable @Table. I've verified the @Table variable contains the correct data. Any help would be appreciated.

    ALTER PROCEDURE dbo.TestTableName

    (

    @TableName nvarchar(32)

    )

    AS

    DECLARE @Table nvarchar(32)

    SET @Table = '[dbo].[' + @TableName + ']'

    SELECT * FROM @Table

    RETURN

  • You need to piece together the sql statement and then execute it as shown below:

    HTH

    Billy

    ALTER PROCEDURE dbo.TestTableName

    (

    @TableName nvarchar(32)

    )

    AS

    begin

    declare @SQL_STMT VARCHAR(4000)

    SET @SQL_STMT = 'SELECT * FROM ' + @TableName + ';'

    exec (@SQL_STMT)

    RETURN

    end

  • It depends on the purpose of your SQL, but, why don't you consider writing a View that unions all the data from the similarly structured tables - you can then use a normal SP to get out the data that you want - I can't imagine that you really want "select * "

    e.g

    Create View dbo.AllTables

    as

    Select ColumnA, ColumnB, ColumnC..

    From TableA

    UNION ALL

    Select ColumnA, ColumnB, ColumnC..

    From TableB

    GO

    create Procedure dbo.GetData

    @ColumnBValue int

    as

    Select * from dbo.AllTables

    where ColumnB=@ColumnBValue

    GO

  • Thanks! Creating a union view is a good idea but these tables are imported Access tables provided by the government for integration. Their sources are various OEM vendors and their are differences between tha types of tables. I'm writing the various T-SQL procedures to integrate the data into a common structure.

    I'll create the SQL statement and execute as suggested.

  • There is a slight twist to this because I need to use it in a cursor. I'm not sure how to use the @SQL_TEXT which was developed in the specification of a cursor. When I use EXEC (@SQL) the query selects the data and returns rows without data. How do I integrate the selection with the cursor specification? None of the following work:

    DECLARE crs_pn CURSOR

    FOR @sql

    OPEN crs_pn

    DECLARE crs_pn CURSOR

    FOR EXEC(@SQL)

    OPEN crs_pn

    DECLARE crs_pn CURSOR

    FOR 'SELECT * FROM [dbo].[' + @TableName + ']'

    OPEN crs_pn

    SET @sql = 'SELECT * FROM [dbo].[' + @TableName + ']'

    DECLARE crs_pn CURSOR

    FOR @sql

    OPEN crs_pn

  • Why don't you compile the Sql and then exec it into a temp table - do your cursor select against the temp table - run the cursor. Then truncate the temp table before you start the cursor process over again.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply