December 12, 2017 at 7:46 am
Hello comunity,
I try to solve this SP but i always have the same error maybe due to numeric fields ?
DECLARE @obrano numeric(10, 0)
DECLARE @ndos numeric(3, 0) = 1
DECLARE @sql NVARCHAR(MAX)
DECLARE @boano AS NUMERIC(4,0)
DECLARE @dbname AS VARCHAR(MAX) = 'mydatabase'
SET @SQL = N'SELECT [obrano] , [boano], [ndos], [bostamp], [nmdos], [dataobra] , [nome] , [totaldeb], [etotaldeb]
FROM ' + @dbname + '[.bo]
WHERE [obrano] = ' + CAST(@obrano AS VARCHAR) +'
AND [boano] = '+ cast(@boano AS VARCHAR) +'
AND [ndos] = ' + CAST( @ndos AS VARCHAR) +''
EXEC SP_EXECUTESQL @SQL , 'mydatabase', 1025, 2017 , 1
Could someone give me help !?
Many thanks,
Luis
December 12, 2017 at 8:00 am
From first glance there is a [ in the wrong place
FROM ' + @dbname + '[.bo]
should be
FROM ' + @dbname + '.[bo]
But as Thom says what is the full and complete error message
December 12, 2017 at 8:06 am
Also, you're naming convention seems wrong. You use:FROM ' + @dbname + '[.bo]
If you're including a database name, then you'll need to use 3 part naming convention:[database].[schema].[object]
You have database and schema? I also, assume, that your schema doesn't have a period (.) character, and that that should be outside the brackets?
The FROM clause is clearly incomplete here, so I'm used the Crystal ball for a bit of a guess. Firstly, you'll notice I get the database name from sys.databases; that helps against injection. Secondly, you don't concatenate your variables into the dynamic sql, with sp_executesql, you pass them as parameters.
This is a start, but it won't work as is. as the objects name is incomplete (and thus this is untested, so any typos, syntax errors that I made will be missed):DECLARE @obrano decimal(10, 0)
DECLARE @ndos decimal(3, 0) = 1
DECLARE @sql nvarchar(MAX)
DECLARE @boano decimal(4,0)
DECLARE @dbname varchar(MAX) = 'mydatabase'
/*
SET @obrano = ;
SET @boano = ;
*/
SELECT @sql = N'
SELECT [obrano] , [boano], [ndos], [bostamp], [nmdos], [dataobra] , [nome] , [totaldeb], [etotaldeb]
FROM ' + QUOTENAME([name]) + N'.[bo] --Need your object name to go here!
WHERE [obrano] = @sobrano
AND [boano] = @sboano
AND [ndos] = @sndos;'
FROM sys.databases
WHERE [name] = @dbname;
EXEC sp_executesql @sql, N'@sobrano decimal(10,0), @sboano decimal(4,0), @sndos decimal(3,0)', @sobrano = @obrano, @sboano = @boano, @sndos = @ndos;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 8:11 am
hello again,
Thanks but i solve the problem assigning values to my DECLARE variables:
DECLARE @obrano numeric(10, 0) = 1025 «« declare here the value
DECLARE @ndos numeric(3, 0) = 1 «« declare here the value
DECLARE @sql NVARCHAR(MAX)
DECLARE @boano AS NUMERIC(4,0) = 2017 «« declare here the value
DECLARE @dbname AS NVARCHAR(60) = 'sgate2008'
SET @SQL = N'SELECT [obrano] , [boano], [ndos], [bostamp], [nmdos], [dataobra] , [nome] , [totaldeb], [etotaldeb]
FROM ' + @dbname +'..[bo]
WHERE [obrano] = ' + CAST(@obrano AS nVARCHAR(10)) + '
AND [boano] = '+ cast(@boano AS nVARCHAR(4)) + '
AND [ndos] = ' + CAST( @ndos AS nVARCHAR(4)) +''
EXEC SP_EXECUTESQL @SQL
Best regards,
Luis
December 12, 2017 at 8:21 am
luissantos - Tuesday, December 12, 2017 8:11 AMhello again,Thanks but i solve the problem assigning values to my DECLARE variables:
DECLARE @obrano numeric(10, 0) = 1025 «« declare here the value
DECLARE @ndos numeric(3, 0) = 1 «« declare here the value
DECLARE @sql NVARCHAR(MAX)
DECLARE @boano AS NUMERIC(4,0) = 2017 «« declare here the valueDECLARE @dbname AS NVARCHAR(60) = 'sgate2008'
SET @SQL = N'SELECT [obrano] , [boano], [ndos], [bostamp], [nmdos], [dataobra] , [nome] , [totaldeb], [etotaldeb]
FROM ' + @dbname +'..[bo]
WHERE [obrano] = ' + CAST(@obrano AS nVARCHAR(10)) + '
AND [boano] = '+ cast(@boano AS nVARCHAR(4)) + '
AND [ndos] = ' + CAST( @ndos AS nVARCHAR(4)) +''
EXEC SP_EXECUTESQL @SQLBest regards,
Luis
I'd still strongly suggest you look at my answer.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 8:21 am
These sorts of problems are usually solved easily by PRINTing out the generated SQL and then analysing it, including attempting to run it, to see what's wrong.
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply