October 14, 2003 at 10:09 am
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
October 14, 2003 at 11:58 am
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
October 15, 2003 at 1:29 am
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
October 15, 2003 at 1:43 pm
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.
October 15, 2003 at 2:07 pm
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
October 16, 2003 at 12:58 am
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