Help with Stored Procedures / Dynamic Queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Stored Procedures / Dynamic Queries

Author

Topic

transcom_bcn
Starting Member

16 Posts

Posted - 2006-06-12 : 10:44:35

Hello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE

In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.

Could you help me please, because my code isn't working:

CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)

AS

DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)

SET NOCOUNT ON

DECLARE c1 CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql=@columnname
END
ELSE
BEGIN
SET @strsql = @strsql + ',' + @columnname
END

FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1

SELECT @query = 'SELECT ' + @strsql + ' FROM ' + @tablename
EXEC @query

SET NOCOUNT OFF
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-12 : 10:49:39

Whay do you want to pass table name as parameter?
You need to use Dynamic Query

CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)

AS
EXEC('Select * from '+@tablename)

Read more about Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

transcom_bcn
Starting Member

16 Posts

Posted - 2006-06-12 : 11:13:29

quote:
Originally posted by madhivanan

Whay do you want to pass table name as parameter?
You need to use Dynamic Query

CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)

AS
EXEC('Select * from '+@tablename)

Read more about Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Thank you very much, but I have a new question:

My problem is that I have about 40 tables, and almost all of them has different number of columns. After the selection I have to extract that information into an Excel file (or at least in a comma or pipe separated format). I have to use a 2 DTS's (one who loops between the tables) and the other that makes the extraction for every table (using this Stored Procedure). And if I use the 'Select * from ...' it extracs me ok the first table, but when the second come (and has a different number of columns that the first), then the extraction fails because the mapping for the transformations is different.

So I supose that I need some kind of dynamic query in the stored procedure in order to avoid errors in the loop extraction.

How can I do this ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources