SQLTeam.com | Weblogs | Forums

Trying to set the variables to numeric if from a column in the database

I am reading the tables to get the columns and then setting a variable i.e DECLARE @VEND_ID nvarchar(max)

This works, but i am hard coding in nvarchar(max).

I am trying to determine how i get the value of the table i.e INT or DATETIMESTAMP and set the value instead of all being set to NVARCHAR
Example: DECLARE @ONEOFCOLUMNS datetime or @ONEOFCOLUMNS INT

Any ideas?

DECLARE @VEND_ID nvarchar(max), @FOUND_VEND_ID nvarchar(max)
		SELECT @VEND_ID = ''
		SELECT @FOUND_VEND_ID = ''

declare
@Variableskeycounter int,
@VariablesKeyMaxID int,
@SQLKeyString1B NVARCHAR(max),
@SQLVariablesString NVARCHAR(max),
@column_name as varchar(max)

SELECT @VariablesKeyCounter = 1
SELECT @VariablesKeyMaxID = count(*) FROM #TEMP_GET_KEYS
---Get All the Columns the Foreign Keys
DROP TABLE #TEMP_GET_KEYS

select ROW_NUMBER() OVER (
ORDER BY col.column_id)
row_num,
schema_name(tab.schema_id) + '.' + tab.name as [table],
col.column_id,
col.name as column_name,
case when fk.object_id is not null then '>-' else null end as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
pk_col.name as pk_column_name,
fk_cols.constraint_column_id as no,
fk.name as fk_constraint_name,
col.is_nullable AS 'FK_Table_IsNullable_Fl'
INTO #TEMP_GET_KEYS
from sys.tables tab
inner join sys.columns col
on col.object_id = tab.object_id
left outer join sys.foreign_key_columns fk_cols
on fk_cols.parent_object_id = tab.object_id
and fk_cols.parent_column_id = col.column_id
left outer join sys.foreign_keys fk
on fk.object_id = fk_cols.constraint_object_id
left outer join sys.tables pk_tab
on pk_tab.object_id = fk_cols.referenced_object_id
left outer join sys.columns pk_col
on pk_col.column_id = fk_cols.referenced_column_id
and pk_col.object_id = fk_cols.referenced_object_id
where schema_name(tab.schema_id) + '.' + tab.name = 'SCHEMA.YOURTABLE'
-- and schema_name(pk_tab.schema_id) + '.' + pk_tab.name is not null
order by schema_name(tab.schema_id) + '.' + tab.name, col.column_id

SELECT * FROM #TEMP_GET_KEYS

WHILE(@VariablesKeyCounter IS NOT NULL AND @VariablesKeyCounter <= @VariablesKeyMaxID)
BEGIN
SELECT @column_name = column_name FROM #TEMP_GET_KEYS WHERE row_num = @VariablesKeyCounter
SELECT @SQLVariablesString = char(9)+ char(9) + char(9) + char(9) + 'DECLARE @' + @column_name + ' nvarchar(max)' + ',' + ' @FOUND_' + @column_name + ' nvarchar(max)' + char(13) +
char(9)+ char(9) + char(9) + char(9) + 'SELECT @' + @column_name + ' = ' + ''''''

   Print @SQLVariablesString

   SELECT @SQLKeyString1B = char(9)+ char(9) + char(9) + char(9) + 'SELECT @FOUND_' + @column_name + ' = ' + ''''''  + char(13) 
   Print @SQLKeyString1B

SET @VariablesKeyCounter = @VariablesKeyCounter + 1 

END

hi

i am not sure of what you are doing !!!

put table name and column name as you want !!

here is something .. if it helps great !! :slight_smile:

SELECT
    DATA_TYPE
        + CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','binary','varbinary')
                    AND CHARACTER_MAXIMUM_LENGTH > 0 THEN
                 COALESCE('('+CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH)+')','')
            ELSE '' END
        + CASE WHEN DATA_TYPE IN ('decimal','numeric') THEN
                COALESCE('('+CONVERT(varchar,NUMERIC_PRECISION)+','+CONVERT(varchar,NUMERIC_SCALE)+')','')
            ELSE '' END
        AS Declaration_Type    
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '' and COLUMN_NAME = ''

That is what i needed, excellent thank you so much.