ntext problem in declaration block

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)

ntext problem in declaration block

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-16 : 05:35:13

[code]
Create Procedure MigrateHpiData AS
Declare
@FAVORITES_ID NUMERIC(20,0),
@FAVORITES_NAME VARCHAR(30),
@USER_LOGIN VARCHAR(50),
@STATUS NUMERIC(1,0),
@FAVORITES_GENDER NUMERIC(5,0),
@CONSULTANT_ID VARCHAR(50),
@CC_DATA NTEXT,
@HPI_DATA NTEXT,
@HPI_CHECKED_DATA VARCHAR(8000),
@CODING_HPI_COUNT VARCHAR(3),
@IS_HPI_ALTERED NUMERIC(1),
@Genid NUMERIC(20,0);
BEGIN
--For inserting the table EMRHPIFAVORITES
SELECT @Genid = EZEMRXID FROM EMRIDS WHERE PROPERTY_NAME = 'HPI_FAVORITES_ID'
SET @Genid = @Genid + 1
DECLARE HPI_Cursor CURSOR FAST_FORWARD FOR
SELECT EF.FAVORITES_ID,EF.STATUS,EF.FAVORITES_NAME,EF.FAVORITES_GENDER,EF.USER_LOGIN,EF.CONSULTANT_ID,ENF.CC_DATA,ENF.HPI_DATA,ENF.HPI_CHECKED_DATA,ENF.CODING_HPI_COUNT,ENF.IS_HPI_ALTERED
FROM EMRFAVORITES EF INNER JOIN ENCOUNTERFAVORITES ENF ON EF.FAVORITES_ID=ENF.FAVORITES_ID
WHERE EF.FAVORITES_TYPE='encounter' AND EF.STATUS=1;
OPEN HPI_Cursor
FETCH NEXT FROM HPI_Cursor INTO @FAVORITES_ID,@STATUS,@FAVORITES_NAME,@FAVORITES_GENDER,@USER_LOGIN,@CONSULTANT_ID,@CC_DATA,@HPI_DATA,@HPI_CHECKED_DATA,@CODING_HPI_COUNT,@IS_HPI_ALTERED;
WHILE @@FETCH_Status = 0
BEGIN
INSERT INTO EMRHPIFAVORITES(HPI_FAVORITES_ID,FAVORITES_NAME,FAVORITES_GENDER,CC_DATA,HPI_DATA,HPI_CHECKED_DATA,CODING_HPI_COUNT,STATUS,IS_HPI_ALTERED,USER_LOGIN,CONSULTANT_ID)
VALUES (@Genid ,@FAVORITES_NAME,@FAVORITES_GENDER,@CC_DATA,@HPI_DATA,@HPI_CHECKED_DATA,@CODING_HPI_COUNT,@STATUS,@IS_HPI_ALTERED,@USER_LOGIN,@CONSULTANT_ID);
UPDATE EMRIDS Set EZEMRXID = @Genid Where PROPERTY_NAME = 'HPI_FAVORITES_ID'
SET @Genid = @Genid + 1
FETCH NEXT FROM HPI_Cursor INTO @FAVORITES_ID,@STATUS,@FAVORITES_NAME,@FAVORITES_GENDER,@USER_LOGIN,@CONSULTANT_ID,@CC_DATA,@HPI_DATA,@HPI_CHECKED_DATA,@CODING_HPI_COUNT,@IS_HPI_ALTERED;
END
CLOSE HPI_Cursor
DEALLOCATE HPI_Cursor
END
GO
[/code]

eceptions like

Msg 2739, Level 16, State 1, Procedure MigrateRosHpiPeData, Line 2
The text, ntext, and image data types are invalid for local variables.
Msg 2739, Level 16, State 1, Procedure MigrateRosHpiPeData, Line 2
The text, ntext, and image data types are invalid for local variables.

how to solve this as i have to insert the next data into ntext xolumn in insert query.any suggestions will help me

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-16 : 06:13:11

hi i am using sql server 2000 database if it is sql server 2005 we can use nvarchar(max).how to do it in sql server 2000

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-16 : 06:16:30

Why are you using a cursor at all? From what I can see, this entire thing could be converted into a single insert and update pair, no cursor, no local variable declarations at all. So why the cursor?

--
Gail Shaw
SQL Server MVP

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-16 : 06:25:02

how i can do that?help me out

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-16 : 07:42:15

Before looking at that, why is HPI_FAVORITES_ID not an identity column? Any specific reason?
From what I can see that's about the only thing hardish to move into a single statement (since SQL 2000 doesn't have Row_Number)

--
Gail Shaw
SQL Server MVP

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-16 : 08:13:46

hi help me in sql server 2000 how to use the ntext in declaration block in stored procedure or any alternative ways in doing so

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 08:46:43

[code]CREATE PROCEDURE dbo.uspMigrateHpiData
AS

SET NOCOUNT ON

DECLARE @GenID NUMERIC(20 ,0)

SELECT @GenID = EZEMRXID
FROM EMRIDS
WHERE PROPERTY_NAME = 'HPI_FAVORITES_ID'

CREATE TABLE #Temp
(
FAVORITES_ID NUMERIC(20, 0),
FAVORITES_NAME VARCHAR(30),
USER_LOGIN VARCHAR(50),
STATUS NUMERIC(1, 0),
FAVORITES_GENDER NUMERIC(5, 0),
CONSULTANT_ID VARCHAR(50),
CC_DATA NTEXT,
HPI_DATA NTEXT,
HPI_CHECKED_DATA VARCHAR(8000),
CODING_HPI_COUNT VARCHAR(3),
IS_HPI_ALTERED NUMERIC(1),
GenID NUMERIC(20, 0) IDENTITY(1, 1)
)

INSERT #Temp
(
HPI_FAVORITES_ID,
FAVORITES_NAME,
FAVORITES_GENDER,
CC_DATA,
HPI_DATA,
HPI_CHECKED_DATA,
CODING_HPI_COUNT,
STATUS,
IS_HPI_ALTERED,
USER_LOGIN,
CONSULTANT_ID
)
SELECT @GenID,
EF.FAVORITES_NAME,
EF.FAVORITES_GENDER,
ENF.CC_DATA,
ENF.HPI_DATA,
ENF.HPI_CHECKED_DATA,
ENF.CODING_HPI_COUNT,
EF.STATUS,
ENF.IS_HPI_ALTERED,
EF.USER_LOGIN,
EF.CONSULTANT_ID
FROM EMRFAVORITES AS EF
INNER JOIN ENCOUNTERFAVORITES AS ENF ON ENF.FAVORITES_ID = EF.FAVORITES_ID
WHERE EF.FAVORITES_TYPE = 'encounter'
AND EF.STATUS = 1

IF @@ERROR <> 0
BEGIN
RAISERROR('Could not stage intermediate data.', 18, 1)
RETURN -1000
END

INSERT EMRHPIFAVORITES
(
HPI_FAVORITES_ID,
FAVORITES_NAME,
FAVORITES_GENDER,
CC_DATA,
HPI_DATA,
HPI_CHECKED_DATA,
CODING_HPI_COUNT,
STATUS,
IS_HPI_ALTERED,
USER_LOGIN,
CONSULTANT_ID
)
SELECT HPI_FAVORITES_ID + GenID,
FAVORITES_NAME,
FAVORITES_GENDER,
CC_DATA,
HPI_DATA,
HPI_CHECKED_DATA,
CODING_HPI_COUNT,
STATUS,
IS_HPI_ALTERED,
USER_LOGIN,
CONSULTANT_ID
FROM #Temp

IF @@ERROR <> 0
BEGIN
RAISERROR('Could not insert data into table.', 18, 1)
RETURN -1010
END

SELECT @GenID = @GenID + MAX(GenID)
FROM #Temp

UPDATE EMRIDS
SET EZEMRXID = @Genid
WHERE PROPERTY_NAME = 'HPI_FAVORITES_ID'[/code]

N 56°04'39.26"
E 12°55'05.63"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-16 : 11:10:13

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources