dynamic convertion of variable data type

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

Transact-SQL (2005)

dynamic convertion of variable data type

Author

Topic

muvvasiva
Starting Member

14 Posts

Posted - 2009-06-01 : 05:47:05

I build a stored procedure to insert data into tables.

The functionality of SP is to insert data into any table based on the given table_name as input parameter.
I am passing table name and values(comma separated string) as input parameters to SP.
procedure generates field names and data types of fields.
it also splits comma separated string into values.And these splitted values will be stored in variables of sql_variant data type.

My problam starts here.
I need to convert the data types of variables(presently they are sql_variant types) .
From sql_variant to data types of fields(table columns).

THE FOLLOWING SP WORKS FINE FOR ONLY ONE TABLE i.e Employees1
IF PROBLAM SOLVED ,IT WORKS FOR ANY TABLE.

--HERE CONERTION OF DATA TYPES IS REQUIRED.
SET @query ='INSERT INTO '+@tableName+'('+substring(@string,0,len(@string)) + ') VALUES('+ CONVERT(NVARCHAR(255),@j)+','+@k+','+@l+')'

CREATE PROCEDURE [dbo].[Insert] --Employees1,"63,'test',1"
@tableName varchar(100),
@Values varchar(1000)
AS
BEGIN
--VARAIBLES TO STORE SPLITTED STRING VALUES
DECLARE @separator CHAR(1)
DECLARE @a SQL_VARIANT
DECLARE @b SQL_VARIANT
DECLARE @c SQL_VARIANT
DECLARE @d SQL_VARIANT
DECLARE @e SQL_VARIANT
DECLARE @f SQL_VARIANT
DECLARE @g SQL_VARIANT
DECLARE @h SQL_VARIANT
DECLARE @i SQL_VARIANT

DECLARE @j INT
DECLARE @k varchar(50)
DECLARE @l varchar(50)

DECLARE @sql varchar(1000)
DECLARE @lp INT --This is used as conditional check in if statement
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned

--SET @t_name=@tableName
SET @separator=','
SET @Values = @Values + @separator
SET @lp=1

-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @Values) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@Values)
SELECT @array_value = LEFT(@Values, @separator_position - 1)
-- This is where you process the values passed.

--Here i insert the @array_value into the particular @lp based on condition
IF @lp=1
SET @a=@array_value
IF @lp=2
SET @b=@array_value
IF @lp=3
SET @c=@array_value
IF @lp=4
SET @d=@array_value
IF @lp=5
SET @e=@array_value
IF @lp=6
SET @f=@array_value
IF @lp=7
SET @g=@array_value
IF @lp=8
SET @h=@array_value
IF @lp=9
SET @i=@array_value

-- This replaces what we just processed with and empty string
SELECT @Values = STUFF(@Values, 1, @separator_position, '')
set @lp=@lp+1 --increment @lp by 1
END
--TO FIND FIELDS AND DATA TYPES OF FIELDS

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string=''

DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
BEGIN
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
--print(@string)

WHILE @@FETCH_STATUS=0
BEGIN
SET @string=@string+@colName+','
print(@string)
print(@dataType)

FETCH NEXT FROM cursCol INTO @colName,@dataType
END
/*TO MAKE EXECUTE THIS SP ,I WRIITTEN THESE SET STATEMENTS BASED ON DATA TYPES OF 'Employee1' TABLE.BUT THIS CONVERTION SHOULD BE MADE AUTOMATICALLY BASED ON THE FIELDS DATA TYPES OF A TABLE*/
SET @j= convert(int,@a)
SET @k= convert(varchar(50),@b)
SET @l= convert(int,@c)

DECLARE @query nvarchar(4000)
--HERE CONERTION OF DATA TYPES IS REQUIRED.
SET @query ='INSERT INTO '+@tableName+'('+substring(@string,0,len(@string)) + ') VALUES('+ CONVERT(NVARCHAR(255),@j)+','+@k+','+@l+')'

PRINT(@query)
exec sp_executesql @query

CLOSE cursCol
DEALLOCATE cursCol

END

SQLRatankalwa
Starting Member

42 Posts

Posted - 2009-06-01 : 11:29:08

Hi muvvasiva

Can you please detail me about your requirement, I will be able to help you then, there are lot of issues with this stored procedure you have written.

You have written cursors and have also processed loops. If you are using this procedure on multiple rows, for each row your stored proc has to initialize a cursor, open, close and deallocate.

You could do it very easily with Bulk Insert using column mappings. Which is absolutely faster, safer and efficient.

Ratan Kalwa
SQL Server Professionals User Group

http://www.revalsys.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources