local variable & conversion issue

[btacy]

hi,
i had a problem with the code below

OPEN @hCursOne
FETCH NEXT FROM @hCursOne INTO @sSurname, @sMemDet, @nIndNo, @sCont
WHILE @@FETCH_STATUS = 0
BEGIN
SET @regno = @regno + 17
INSERT INTO newiuser
(registration_no, password, prompt_question, prompt_answer, individual_ref, status, valid_from, create_timestamp, update_timestamp, created_by, updated_by)
VALUES
(@regno, @sSurname, 'What's his/her name?', @sCont, @nIndNo, @olsstatus, GETDATE(), GETDATE(), GETDATE(), suser_sname(), suser_sname())

FETCH NEXT FROM @hSqlOne INTO @sSurname, @sMemDet, @nIndNo, @sCont
END
CLOSE @hCursOne

it was giving truncating errors and it was not an issue of the column size, so i attempted to print out the values of the variables with

print @nIndNo +' '+@sSurname +' '+@sMemDet+' '+@sCont

and got this error

'Server: Msg 245, Level 16, State 1, Line 39
Syntax error converting the varchar value 'vurata tol' to a column of data type int.

when i tried to print values of each variable independent of each other, there were no errors, and the same for when i attempt printing values of
@sSurname +' '+@sMemDet+' '+@sCont

when i include @nIndNo
the same error comes up.

I didn't write this script from scratch, i modified it to suit the present purpose.
does anyone have an idea why this happens?

 

[ca8msm]

Because @nIndNo is probably an int. So, by appending another value to it (+@sSurname) sql server thinks this will also be an int.

You will need to cast the values to a varchar.

Also, why are you using a cursor? Do you really need to? My guess would be no...


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[chrissie1]

The code that you showed here makes me think you don't need a cursor. of course I don't know what's in @hcursone.


Christiaan Baes
Belgium

My Blog

 

[btacy]

i'm selecting 4 linked values per record from different tables,for an unknown number of records, that's why i used a cursor to store them.

could you give me an idea how else to work around it without using a cursor?

 

[ca8msm]

You would need to provide the full code for an answer to that.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[btacy]

here is the full code apart from declarations

Set @hSqlOne = CURSOR SCROLL DYNAMIC FOR
SELECT EMPLOYEE.SURNAME, ORGANISATION.MEM_REF, EMPLOYEE.EMP_NO, MISC.CONT
FROM EMPLOYEE INNER JOIN
ORGANISATION ON EMPLOYEE.EMP_NO = ORGANISATION.EMP_NO
INNER JOIN INDSTA ON EMPLOYEE.CODE = INDSTA.ICODE INNER JOIN MEMSTA ON ORGANISATION.MEM_CODE = MEMSTA.MCODE INNER JOIN
(SELECT L.LDDA_REF, L.DISP, L.BRAIL_CODE, L.BRAIL, L.ADDR, L.TXXP,
L.POSTINGS, L.CONT, L.COMMENTS, L.CONT_REF,
L.REG1, L.REG, EL.EMP_NO, EL.RANGE, LOAN.LOAN_DESC
FROM LDDA AS L INNER JOIN
EMP_AC07 AS EL ON EL.LDDA_REF = L.LDDA_REF INNER JOIN
LOAN ON EL.RANGE = LOAN.RANGE_REF
WHERE (EL.MAIN_TAG = 'Y') AND (EL.VALID_FROM <= GETDATE() OR
EL.VALID_FROM IS NULL) AND (EL.VALID_TILL >= GETDATE() OR
EL.VALID_TILL IS NULL)) AS MISC ON EMPLOYEE.EMP_NO = MISC.EMP_NO INNER JOIN
DTR_ALL ON EMPLOYEE.EMP_NO = DTR_ALL.EMP_NO
WHERE (DTR_ALL.ALL_REF IN(1145,1190,1157)) AND (INDSTA.STATUS LIKE 'ACT%') AND (MEMSTA.STATUS LIKE 'ACT%') AND
(NOT (EMPLOYEE.EMP_NO IN
(SELECT EMP_NO
FROM NEWIUSER)))

 

[ca8msm]

It would look something like this:

    INSERT INTO newiuser
        (password)
SELECT     EMPLOYEE.SURNAME
FROM         EMPLOYEE INNER JOIN
             ORGANISATION ON EMPLOYEE.EMP_NO = ORGANISATION.EMP_NO
             INNER JOIN INDSTA ON EMPLOYEE.CODE = INDSTA.ICODE INNER JOIN MEMSTA ON ORGANISATION.MEM_CODE = MEMSTA.MCODE INNER JOIN
                          (SELECT     L.LDDA_REF, L.DISP, L.BRAIL_CODE, L.BRAIL, L.ADDR, L.TXXP,
                                                     L.POSTINGS, L.CONT, L.COMMENTS, L.CONT_REF,
                                                   L.REG1, L.REG, EL.EMP_NO, EL.RANGE, LOAN.LOAN_DESC
                            FROM          LDDA AS L INNER JOIN
                                                   EMP_AC07 AS EL ON EL.LDDA_REF = L.LDDA_REF INNER JOIN
                                                   LOAN ON EL.RANGE = LOAN.RANGE_REF
                            WHERE      (EL.MAIN_TAG = 'Y') AND (EL.VALID_FROM <= GETDATE() OR
                                                   EL.VALID_FROM IS NULL) AND (EL.VALID_TILL >= GETDATE() OR
                                                   EL.VALID_TILL IS NULL)) AS MISC ON EMPLOYEE.EMP_NO = MISC.EMP_NO INNER JOIN
                      DTR_ALL ON EMPLOYEE.EMP_NO = DTR_ALL.EMP_NO
WHERE     (DTR_ALL.ALL_REF IN(1145,1190,1157)) AND (INDSTA.STATUS LIKE 'ACT%') AND (MEMSTA.STATUS LIKE 'ACT%') AND
                      (NOT (EMPLOYEE.EMP_NO IN
                          (SELECT     EMP_NO
                            FROM          NEWIUSER)))

However, as you can see, I've only included one field as part of the insert (the password field) as there were several fields that didn't make sense to me or were variables that weren't actually used. You will have to take this logic and extend it to suit your insert statement.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[btacy]

thanks