returning numeric value

  • Hello comunity
    I need help for returning numeric values from dynamic sql. My script is

       DECLARE @bd VARCHAR(40)
        DECLARE @myobrano INT
        
        SET @bd = 'databasename.dbo'
        PRINT @bd
        SET @myobrano = CAST(('SELECT Isnull(MAX(' + @bd +'.bo.obrano),0)' + ' FROM ' + @bd +'.bo' ) AS INT)
        PRINT CAST(@myobrano AS VARCHAR)
    error: Conversion failed when converting the varchar value 'SELECT Isnull(MAX(databasename.dbo.bo.obrano),0) FROM databasename.dbo.bo' to data type int.

    Thanks ,
    Luis

  • What is the datatype of bo.obrano?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • luissantos - Thursday, December 7, 2017 6:19 AM

    Hello comunity
    I need help for returning numeric values from dynamic sql. My script is

       DECLARE @bd VARCHAR(40)
        DECLARE @myobrano INT
        
        SET @bd = 'databasename.dbo'
        PRINT @bd
        SET @myobrano = CAST(('SELECT Isnull(MAX(' + @bd +'.bo.obrano),0)' + ' FROM ' + @bd +'.bo' ) AS INT)
        PRINT CAST(@myobrano AS VARCHAR)
    error: Conversion failed when converting the varchar value 'SELECT Isnull(MAX(databasename.dbo.bo.obrano),0) FROM databasename.dbo.bo' to data type int.

    Thanks ,
    Luis

    To me it looks like the SET @myobrano line is the problem, your not actually executing the statement inside to return the INT value, so you actually setting @myobrano to a VARCHAR string of "'SELECT Isnull(MAX(databasename.dbo.bo.obrano),0) FROM databasename.dbo.bo" instead of the actual return value.

    You would need to do something similar but use sp_executesql or build a truly dynamic piece of code to do what you need to do.

    Also the naming seems a little off here, that 4 part name seems like it should be a 3 part name and the use of square brackets around [bo.obrano] as its generally done SERVERNAME.DATABASENAME.SCHEMANAME.OBJECT

    My guess, is you want something like this

    DECLARE @bd VARCHAR(40), @SQL NVARCHAR(MAX)
    SET @bd = 'databasename.dbo'
    PRINT @bd
    SET @SQL = 'SELECT @_myobrano = ISNULL(MAX(obrano),0) FROM '+@BD+'.bo'
    EXEC sp_executesql @SQL, '@_myobrano INT OUTPUT'
    SELECT @_myobrano

  • anthony.green - Thursday, December 7, 2017 7:06 AM

    To me it looks like the SET @myobrano line is the problem, your not actually executing the statement inside to return the INT value, so you actually setting @myobrano to a VARCHAR string of "'SELECT Isnull(MAX(databasename.dbo.bo.obrano),0) FROM databasename.dbo.bo" instead of the actual return value.

    Absolutely right. Should've had my coffee before posting.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hello Antony

    Many thanks for your help.
    I try your script, but is returned this error:
    Must declare the scalar variable "@_myobrano".

    Best regards,
    Luis

  • luissantos - Thursday, December 7, 2017 7:34 AM

    Hello Antony

    Many thanks for your help.
    I try your script, but is returned this error:
    Must declare the scalar variable "@_myobrano".

    Best regards,
    Luis

    Try this:DECLARE @bd AS varchar(40);
    DECLARE @myobrano AS int;
    DECLARE @SQL AS nvarchar(max);

    SELECT @bd = 'databasename.dbo';
    PRINT @bd;

    SELECT @SQL = N'DECLARE @myobrano AS int = (SELECT ISNULL(MAX(obrano), 0)' + ' FROM ' + @bd + N'.bo);'
    EXEC sp_executesql @SQL, '@myobrano int OUTPUT';

    PRINT CAST(@myobrano AS varchar(10));

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Hello Steve,

    Thanks for your reply, but sql server return this error:
    Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

    Many thanks,
    Luis

  • First, try reading up on things in books online, here is the entry for sp_executesql: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql.
    Then, try this, no promises as I have nothing with which to test:
    DECLARE @bd AS varchar(40);
    DECLARE @myobrano AS int;
    DECLARE @SQL AS nvarchar(max);
    DECLARE @SQLParm nvarchar(max) = N'@pmyobrano int OUTPUT';

    SELECT @bd = 'databasename.dbo';
    PRINT @bd;

    SELECT @SQL = N'SELECT @pmyobrano = (SELECT ISNULL(MAX(obrano), 0) FROM ' + @bd + N'.bo);'
    EXEC sp_executesql @SQL, @SQLParm, @pmyobrano = @myobrano OUTPUT;

    select @myobrano;

  • Hello Lynn

    Thanks for  your  reply and advise.
    Best regards,
    Luis

  • Oops!   Messed up the syntax.   Here's the corrected version:
    DECLARE @bd AS varchar(40);
    DECLARE @myobrano AS int = NULL;
    DECLARE @SQL AS nvarchar(max);

    SELECT @bd = 'databasename.dbo';
    PRINT @bd;

    SELECT @SQL = N'SELECT @myobrano = ISNULL(MAX(obrano), 0)' + ' FROM ' + @bd + N'.bo);';
    EXEC sp_executesql @SQL, @params = N'@myobrano int OUTPUT', @myobrano = @myobrano OUTPUT;

    PRINT CAST(@myobrano AS varchar(10));

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 10 posts - 1 through 9 (of 9 total)

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