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.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply