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 2012 Forums
 Transact-SQL (2012)
 Using Variables in Cursor

Author  Topic 

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-04-01 : 15:55:36
Good afternoon, I am trying to use a set of variables to declare a cursor, the error is:

Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@tmp_cursor'.

The code so far is:

DECLARE @TABLE_NAME varchar(50),
@TMP_CURSOR varchar(100)

SELECT @table_name='saleslogix.sysdba.c_acct_marketing_100'+')'
SELECT @TMP_CURSOR='CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('

-- DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('saleslogix.sysdba.c_acct_marketing_100')
PRINT @TMP_CURSOR + @TABLE_NAME

DECLARE crs @tmp_cursor + @TABLE_NAME

Any dieas, and thanks in advance

Bryan Holmstrom

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-01 : 16:22:32
you can't embed dynamic sql withing non-dynamic sql.
Can you explain (big picture) what you're trying to accomplish. I'm sure there is a better way.

Be One with the Optimizer
TG
Go to Top of Page

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-04-01 : 16:31:06
The goal here is to run thru a database and print the column names for each table to a flat file.

Bryan Holmstrom
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-01 : 16:58:08
Then perhaps this query with results directed to file:

select table_name, column_name from information_schema.columns order by table_name, ordinal_position

Be One with the Optimizer
TG
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-04-01 : 18:30:53
Just as an aside, the OBJECT_ID function does accept a variable name as a parameter. You could have code like:[CODE]declare @TABLE_NAME sysname = 'MyTable';
SELECT name FROM syscolumns WHERE id=OBJECT_ID(@TABLE_NAME);[/CODE]Not to be completely pedantic but the data type for a schema object, like a table, is "sysname". You'll be glad later when the length of your table names grows beyond 50 characters and your code still works.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

Prakash Machiraju
Starting Member

2 Posts

Posted - 2013-04-02 : 10:08:43
Please try the following script.

Script to get the column names for all the tables in a given database.

DECLARE @TableName VARCHAR(100)
DECLARE @ColNames VARCHAR(MAX)
DECLARE @DBName VARCHAR(100)

SET @DBName = '<YourDBName>'

DECLARE TableCursor CURSOR FOR
SELECT TABLE_Name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @DBName
AND TABLE_TYPE = 'BASE TABLE'

OPEN TableCursor

FETCH TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @ColNames = ''

SELECT @ColNames = COALESCE(@ColNames + ', ' + COLUMN_NAME, Column_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName

PRINT @ColNames

FETCH TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
GO

Prakash Machiraju
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-02 : 11:04:46
TG's is one line and doesn't use a cursor.
Go to Top of Page
   

- Advertisement -