Error converting data type varchar to bigint

  • Hi,

    I'm doing this query:

    DECLARE @TESTE BIGINT

    DECLARE @DB VARCHAR(100)

    SET @DB ='SGCTCENTRAL'

    SET @TESTE = ('SELECT MAX(CODIGO) FROM '+@DB+'.DBO.SINCRONISMO')

    SELECT @TESTE

    I get this message:

    Msg 8114, Level 16, State 5, Line 8

    Error converting data type varchar to bigint.

    Can someone help, please.

    Thank you

  • You're setting your variable to 'SELECT MAX(CODIGO) FROM SGCTCENTRAL.DBO.SINCRONISMO' and that is a string value. You need to use dynamic code by using sp_executesql (preferred method) or EXECUTE().

    If your query is as simple as this, you don't need to use the @TESTE variable. You could execute the SELECT directly.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • river1 (3/28/2014)


    Hi,

    I'm doing this query:

    DECLARE @TESTE BIGINT

    DECLARE @DB VARCHAR(100)

    SET @DB ='SGCTCENTRAL'

    SET @TESTE = ('SELECT MAX(CODIGO) FROM '+@DB+'.DBO.SINCRONISMO')

    SELECT @TESTE

    I get this message:

    Msg 8114, Level 16, State 5, Line 8

    Error converting data type varchar to bigint.

    Can someone help, please.

    Thank you

    declare @SQLCmd nvarchar(max),

    @SQLParm nvarchar(max),

    @TESTE bigint,

    @DB nvarchar(128);

    SET @DB = N'SGCTCENTRAL';

    set @SQLCmd = N'SELECT @TESTOUT = MAX(CODIGO) FROM '+@DB+'.DBO.SINCRONISMO');

    set @SQLParm = N'@TESTOUT bigint';

    exec sp_executesql @SQLCmd, @SQLParm, N'@TESTOUT = @TESTE OUTPUT';

    select @TESTE

    Not sure if I did all of that correctly as I did it from memory and I have not tested in my sandbox database. At least it gives you an idea of how to proceed if it works.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply