returning numeric value

Question

returning numeric value

luissantos

SSCertifiable

Points: 7391
More actions
December 7, 2017 at 6:19 am

#325007

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What is the datatype of bo.obrano?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

luissantos SSCertifiable Points: 7391 More actions · Answer 4

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

sgmunson SSC Guru Points: 110640 More actions · Answer 5

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)

luissantos SSCertifiable Points: 7391 More actions · Answer 6

Hello Steve,

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

Many thanks,
Luis

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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;

luissantos SSCertifiable Points: 7391 More actions · Answer 8

Hello Lynn

Thanks for your reply and advise.
Best regards,
Luis

sgmunson SSC Guru Points: 110640 More actions · Answer 9

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)