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

General SQL Server Forums

New to SQL Server Programming

Help with Stored Procedures / Dynamic Queries

Author

Topic

transcom_bcn
Starting Member

16 Posts

Posted - 2006-06-12 : 10:48:10

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:50:02

Duplicate post
http://sqlteam.com/forums/topic.asp?TOPIC_ID=67655

Madhivanan

Failing to plan is Planning to fail

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-06-12 : 10:57:14

Try this:

DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
DECLARE @tablename varchar(500)
SET @tablename = 'AccessNumbers_dim'

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)

Sanjeev Shrestha
12/17/1963

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-12 : 11:04:20

You dont need to use Cursor

Madhivanan

Failing to plan is Planning to fail

transcom_bcn
Starting Member

16 Posts

Posted - 2006-06-12 : 11:12:53

quote:
Originally posted by sanjnep

Try this:

DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
DECLARE @tablename varchar(500)
SET @tablename = 'AccessNumbers_dim'

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)

Sanjeev Shrestha
12/17/1963

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