convert MsSql StoredPorcedure to MySql

Posted by karthik on Stack Overflow See other posts from Stack Overflow or by karthik
Published on 2010-05-04T08:38:32Z Indexed on 2010/05/04 8:58 UTC
Read the original article Hit count: 320

Filed under:

mysql

|

mysql-query

|

mssql

I need to covert the following SP of MsSql To MySql. I am new to MySql.. Help needed.

CREATE PROC InsertGenerator

(@tableName varchar(100)) as

--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 @stringData nvarchar(3000)

--for storing the data

--(VALUES) related statement

DECLARE @dataType nvarchar(1000) --data types returned

--for respective columns

SET @string='INSERT '+@tableName+'(' SET @stringData=''

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

WHILE @@FETCH_STATUS=0

BEGIN

IF @dataType in ('varchar','char','nchar','nvarchar')

BEGIN

SET @stringData=@stringData+'''''''''+
        isnull('+@colName+','''')+'''''',''+'

END

ELSE

if @dataType in ('text','ntext')

--if the datatype

--is text or something else

BEGIN

SET @stringData=@stringData+'''''''''+
      isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'

END

ELSE

IF @dataType = 'money' --because money doesn't get converted

--from varchar implicitly

BEGIN

SET @stringData=@stringData+'''convert(money,''''''+
    isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'

END

ELSE

IF @dataType='datetime'

BEGIN

SET @stringData=@stringData+'''convert(datetime,''''''+
    isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'

END

ELSE

IF @dataType='image'

BEGIN

SET @stringData=@stringData+'''''''''+
   isnull(cast(convert(varbinary,'+@colName+') 
   as varchar(6)),''0'')+'''''',''+'

END

ELSE

--presuming the data type is int,bit,numeric,decimal

BEGIN

SET @stringData=@stringData+'''''''''+
      isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'

END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType

END

Developer IT