You have not declared @sql outside the body of the stored procedure. In the SP All you are doing is setting up a variable and doing nothing with it.
Try moving the
END
to
after the
EXEC
Edit: Another thought - that looks like a very poor database design.
Firstly you appear to have your
DateTime
column as type
varchar
. Use the appropriate column type (i.e. either
date
or
datetime
).
Avoid using reserved words for column names but if you do remember to enclose them in square brackets e.g.
[DateTime]
Why not have a single table with an additional column that differentiates between the different types then the entire SP becomes
select [ID], COL_SPAU_EA FROM [DBRapBreaza].[dbo].[TblSPAU]
WHERE DateTime between @DataStart and @DataStop
AND SPAU_TYPE = @val;
EDIT:
Well, you claim nothing changes yet I get a completely different set of errors when I moved the END to the correct place. But my apologies for thinking the
datetime
column was a varchar - I misread the code setting up
@sql
.
However, I've just realised that you are using
sp_sqlexec
- change that to
sp_executesql
- Microsoft recommend the latter over the former.
Then you are going to get syntax errors in your SQL - you need to surround the dates with quotes and include some spaces after "between" and around "and" e.g.
set @sql='select [ID]'+@col2+' FROM [DBRapBreaza].[dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between '''+CONVERT(VARCHAR(25), @DataStart,121)+ ''' and '''+CONVERT(VARCHAR(25), @DataStop,121)+''';'
In response to your comment
Quote:
As I mentioned, I have 40 tables (TblSPAU1...TblSPAU40) which contains the columns mentioned above. DateTime is declared in DB as datetime. And who's SPAU_TYPE in your above code?
I was trying to give you an alternative database design... instead of 40 tables have just ONE table. SPAU_TYPE in my example would be a value between 1 and 40 … so the rows with SPAU_TYPE = 1 would be the rows you currently have in your TblSPAU1, those rows with SPAU_TYPE = 2 would be the rows you currently have in your TblSPAU2, etc. It's a much better design that is considerably easier to work with and maintain.
As an aside, it is a subjective view, but most experienced programmers avoid prefixing table names with Tbl or column names with Col_. It adds nothing to the documentation, causes extra typing and cause issues further down the line … for example, what if you need to replace what was originally a table with a View? You would have to do a lot of work to change TblSPAU to VwSPAU for no real benefit.
Edit 2 - the full code of the SP that I've been using to test this
ALTER PROCEDURE [dbo].[PS_SpauOPompa]
@DataStart datetime,
@DataStop datetime,
@val int
AS
BEGIN
SET NOCOUNT ON;
declare @sql NVARCHAR(max)
declare @col1 varchar
set @col1='ID'
declare @col2 varchar(25)
set @col2='COL_SPAU'+CONVERT(VARCHAR, @val)+'_EA'
declare @col3 varchar
set @col3='DateTime'
set @sql='select [ID],'+@col2+' FROM [dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between '''+CONVERT(VARCHAR(25), @DataStart,121)+ ''' and '''+CONVERT(VARCHAR(25), @DataStop,121)+''';'
PRINT @SQL
EXEC sp_executesql @sql, N'@DataStart datetime,@DataStop datetime,@val int', @DataStart, @DataStop, @val
END