December 7, 2017 at 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
December 7, 2017 at 6:23 am
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.
December 7, 2017 at 7:06 am
luissantos - Thursday, December 7, 2017 6:19 AMHello 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 ,
uis
L
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
December 7, 2017 at 7:10 am
anthony.green - Thursday, December 7, 2017 7:06 AMTo 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.
December 7, 2017 at 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
December 7, 2017 at 7:43 am
luissantos - Thursday, December 7, 2017 7:34 AMHello AntonyMany 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)
December 7, 2017 at 8:00 am
Hello Steve,
Thanks for your reply, but sql server return this error:
Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.
Many thanks,
Luis
December 7, 2017 at 9:24 am
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;
December 7, 2017 at 9:33 am
Hello Lynn
Thanks for your reply and advise.
Best regards,
Luis
December 7, 2017 at 9:40 am
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