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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Using Variables in Cursor
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 04/01/2013 :  15:55:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 04/01/2013 :  16:22:32  Show Profile  Reply with Quote
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

USA
75 Posts

Posted - 04/01/2013 :  16:31:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 04/01/2013 :  16:58:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 04/01/2013 :  18:30:53  Show Profile  Reply with Quote
Just as an aside, the OBJECT_ID function does accept a variable name as a parameter. You could have code like:
declare @TABLE_NAME sysname = 'MyTable';
SELECT name FROM syscolumns WHERE id=OBJECT_ID(@TABLE_NAME);
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

United Kingdom
2 Posts

Posted - 04/02/2013 :  10:08:43  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/02/2013 :  11:04:46  Show Profile  Visit russell's Homepage  Reply with Quote
TG's is one line and doesn't use a cursor.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000