Cursor issue - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Cursor issue

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-12 : 12:00:38

I am not getting the results I am looking for here. It is adding the values to the "tblTables" table, but not to the "tblFields" table.

Declare @MyDatabase as varchar(100)
set @MyDatabase = 'NJ_Toys_Claims'
Declare @TableNameHeader as varchar(100)
Declare @TableNameDetail as varchar(100)
set @TableNameHeader = 'Header5151'
set @TableNameDetail = 'Detail1313,Detail414'
DECLARE @MyTableVar TABLE
(
TableID INT,
TableName VARCHAR(100)
)

-- Add Tables Info for Header
Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Output Inserted.TableID, Inserted.TableName Into @MyTableVar
Select @TableNameHeader, 2, 0 WHERE @TableNameHeader
Not IN (Select Tablename From tblTables)

-- Add Tables Info for Detail
Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Output Inserted.TableID, Inserted.TableName Into @MyTableVar
Select Item, 2, 0 From DelimitedSplit8k(@TableNameDetail,',') as T WHERE T.Item
Not IN (Select Tablename From tblTables)

Declare @TID as Int
Declare @TName as Varchar(100)
Declare TC Cursor For
Select TableID, TableName From @MyTableVar
Open TC
While (@@FETCH_STATUS = 0)
BEGIN
Fetch Next From TC Into @TID, @TName
Insert Into tblFields(TableID, FieldName, FieldType)
Select @TID, ColumnName, ColumnType From fn_GetColumnInfo(@TName)
END
Close TC
Deallocate TC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 12:14:26

without knowing what fn_GetColumnInfo etc does its difficult to answer you. probably you could explain what you're trying to do with above query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-12 : 12:30:30

It returns columnName and ColumnType. The function goes out to SysColumns and SysTypes to get the info.

Select * from fn_GetColumnInfo('TRU_2010_PRICING_CLAIM_DETAIL2')

This is what it returns:
ColumnName ColumnType
Chkdt datetime
Chknbr varchar
clm_amt decimal
DEPT decimal
Description varchar
DOMVNDNBR decimal
ImpVndnbr decimal
INV_COST decimal
INVDT datetime
INVNBR varchar
MasterClaimRec decimal
MFGNBR varchar
ORDTYPE varchar
PO_COST decimal
PODT datetime
PONBR decimal
Price_diff decimal
Price_Paid decimal
Price_SB decimal
Qty decimal
SKN decimal
VNDNAME varchar

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-12 : 12:34:00

I am inserting 3 table names into the tblTables table. Then I am using the TableID, fieldname, fieldtype for the 3 tables and inserting it into tblFields. The TableID has to coinside with the fieldnames and fieldtypes for the table it is returning records for.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-12 : 13:38:15

The problem is your @@fetch_status starts out != 0 because you haven't FETCH'd a row yet.

Open TC
Fetch Next From TC Into @TID, @TName
While (@@FETCH_STATUS = 0)
BEGIN
~~Fetch Next From TC Into @TID, @TName~~
Insert Into tblFields(TableID, FieldName, FieldType)
Select @TID, ColumnName, ColumnType From fn_GetColumnInfo(@TName)
Fetch Next From TC Into @TID, @TName
END
Close TC
Deallocate TC

But it looks to me like you don't need a cursor for this. why not insert all the column info for all your tables at once directly through information_schema views? And for that matter because information_schema views exist why not use those directly instead of your tables?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:04:40

seems like OP is trying to reinvent the wheel

i think information_schema.columns should be sufficient for his purpose

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-12 : 14:33:22

fn_GetColumnInfo is a function that uses information_schema.columns. I just put it in a function so i can feed it the table name and it returns me the info i need...not reinventing the wheel...just putting rubber on the wood.

Thanks for your help that Worked! If i could insert it at once i would but I need the TableID for Tables and it has multiple TableID's, 1 for each record it inserts into tblTables. Then I have to get the columns and Column types for each TableID or Tablename and then insert that into tblFields. If you know how to do this in 1 insert outside a cursor, please tell me...i could not figure that out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:47:33

do population of tblFields as below


INSERT INTO tblFields (TableID, FieldName, FieldType) 
SELECT t.TableID,c.COLUMN_NAME,c.DATA_TYPE
FROM tblTables t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_NAME = t.TableName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-12 : 15:34:52

I just got a flat with a splinter. : )

That worked as well. Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-12 : 15:36:06

>>just putting rubber on the wood
always a good idea in this day and age. What brand do you use?

Be One with the Optimizer
TG

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-12 : 16:33:48

I just thought of a possible issue. What if that same tablename exists in another db. It will populate the columnname and columntype of both the tables in both existing db's. Can i specify what db to pull this from on my server?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-12 : 17:25:29

information_schema views are database specific. Take a look yourself. Only objects from current DB are returned.

Be One with the Optimizer
TG

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-12 : 18:29:36

I'm using a variable to save the db name that I will be using in my script:
declare @MyDatabase as varchar(100)
set @MyDatabase = 'PEP_2010_ATLPROD'

I tried :
Use @MyDatabase
and
Use (@MyDatabase)

both did not work. What is the correct syntax?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 01:48:59

why you want to determine db at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-13 : 08:14:45

This is part of a long script that is ran and I have several parameters that the user will input to run and the db is one of them since this will run on many different db's.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 11:19:20

if you want to run it across dbs no need of parameter. just use like below and you will get results from all dbs inside server in same window


EXEC sp_Msforeachdb('SELECT c.Table_NAME,c.COLUMN_NAME,c.DATA_TYPE
FROM ?.INFORMATION_SCHEMA.COLUMNS c')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-13 : 13:17:10

thanks, but i am not looking to run it on all db's. The script will be ran and it is to run on 1 db, but if I hardcode it, then i have to change it in several different locations on the script instead of just 1 place. I am using up to 5 different db's which I want to declare in a variable instead of making those changes every time i need to use the script.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 01:00:58

even if its on 5 dbs you can use below code with if condition to check if you're in any one of those dbs to run query. Of course, all 5 dbs have to be in same server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources