创建SQL语句以根据特定月份创建列

I'm trying to create a SQL code that allows me to create columns according to a specific month, now I have this syntax, but when I run it, it shows me errors. Could you tell me what I'm doing wrong ...?

我正在尝试创建一个允许我根据特定月份创建列的SQL代码,现在我有了这种语法,但是当我运行它时,它会显示错误。你能告诉我我做错了什么吗?

alter proc N50_Actualizar_PA
@fecha varchar(6)
as
begin

declare @fin int
declare @cont int
declare @query varchar(5000)
declare @query1 varchar(900)
declare @query2 varchar(900)
declare @query3 varchar(900)

set @fin = (SELECT day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@fecha+'01')+1, 0))))
set @cont = 1
set @query3 = 'while '+@cont+' <= '+@fin+'
begin
set @query = ''S''+cast('+@cont+' as varchar(2))+'' float,E''+cast('+@cont+' as varchar(2))+'' float,''
exec (@query)
set '+@cont+' = '+@cont+' + 1
end'

set @query1 = 'if not exists (select * from sysobjects where name = ''PLANTA_REP_PA_'+@fecha+''')
begin
create table PLANTA_REP_PA_'+@fecha+'(
ITEM int,
UBIGEO varchar(255),
CENTRO_POBLADO varchar(255),
DISTRITO varchar(255),
PROVINCIA varchar(255),
DEPARTAMENTO varchar(255),
TELEFONO varchar(255),
TIPO varchar(255),
OBSERVACION varchar(255),
TECNOLOGIA varchar(255),
ID_VSAT_BTS varchar(255),
UBICACION_VSAT_BTS varchar(255),
NIVEL_SENAL varchar(255),
TIPO_ENERGIA varchar(255),
COMENTARIOS varchar(255),
CANT_LINEAS_B_ARRENDADOR varchar(255),
'+ @query3 +'
TOTAL_SALIENTE float,
TOTAL_ENTRANTE float,
SALIENTE_MOVILES float,
ENTRANTE_MOVILES float)
end
else
begin
truncate table PLANTA_REP_PA_' + @fecha + '
end'

set @query2 = 'INSERT INTO PLANTA_REP_PA_' + @fecha + '
Select * from Temp_Planta_Rep_PA'

exec (@query3)
print (@query1)
print (@query2)

end

1 个解决方案

#1

General Problem #1: this is a poor question. You got a lot of downvotes here, and they're likely all because your problem and goals weren't clearly laid out, and you didn't provide sufficient information. If that wasn't bad enough, though, posting "fix my code" problems is hugely frowned on by the community. Read this before posting next time.

一般问题#1:这是一个很糟糕的问题。你在这里得到了很多的支持,而且他们可能都是因为你的问题和目标没有明确规定,而且你没有提供足够的信息。但是,如果这还不够糟糕的话,发布“修复我的代码”问题是社区非常不满意的。在下次发布之前阅读此内容。

General Problem #2: this is a horrible idea. You're creating a table for specific date ranges, you're truncating blindly ... yeah. Not a good design. New table every month is poor practice, but that's your business.

一般问题#2:这是一个可怕的想法。你正在为特定的日期范围创建一个表,你是盲目地截断...是的。不是一个好的设计。每个月的新表都是不好的做法,但那是你的事。

SQL Problem #1: you're trying to concatenate strings and integers (set @query3 = 'while '+@cont+' <= '+@fin+' ...). You would have to convert those integers to strings before doing that. That doesn't matter, though, because

SQL问题#1:你正在尝试连接字符串和整数(设置@ query3 ='while'+ @ cont +'<='+ @ fin +'...)。在执行此操作之前,您必须将这些整数转换为字符串。但这并不重要,因为

SQL Problem #2: the entire WHILE statement is wrong. The purpose of the statement is, I assume, to stuff a variable with the names of your columns. But it isn't doing that.

SQL问题#2:整个WHILE语句错误。我假设该语句的目的是用一个列的名称填充变量。但它并没有这样做。

SQL Problem #2a - the WHILE loop isn't concatenating a column list. Read it again: you're telling SQL to execute @query, which in the best-case scenario will be S1 float, E1 float,. That's not a SQL command. What you need to do instead is set @query = @query + 'S1 .... That way, it will build up the string.

SQL问题#2a - WHILE循环不连接列列表。再读一遍:你告诉SQL执行@query,在最好的情况下,它将是S1 float,E1 float。那不是SQL命令。你需要做的是设置@query = @query +'S1 ....这样,它将构建字符串。

SQL Problem #2b - the lengths of your variables are wrong. Trying to stuff a varchar(5000) query into a varchar(900) query isn't going to work.

SQL问题#2b - 变量的长度是错误的。尝试将varchar(5000)查询填充到varchar(900)查询中是行不通的。

SQL Problem #3: you never attach your column list into the table definition. Instead, by writing

SQL问题#3:您永远不会将列列表附加到表定义中。相反,通过写作

CANT_LINEAS_B_ARRENDADOR varchar(255),
'+ @query3 +'
TOTAL_SALIENTE float,

you're telling SQL to set the value to

你告诉SQL将值设置为

CANT_LINEAS_B_ARRENDADOR varchar(255),
while 1 <= 28 
begin
set ...
TOTAL_SALIENTE float,

This will never work. What you're trying to do is add in the concatenated string theoretically housed in @query, in which case you should write

这永远不会奏效。你要做的是添加理论上位于@query中的连接字符串,在这种情况下你应该写

CANT_LINEAS_B_ARRENDADOR varchar(255),
'+ @query +'
TOTAL_SALIENTE float,

That still won't solve it, because

那仍然无法解决,因为

SQL Problem #4: you haven't executed @query3 yet. You have to build and set the values for @query before you try to concatenate those values with your table definition. Instead, you need to execute that WHILE loop and populate the values for @query before you concatenate it with @query1.

SQL问题#4:你还没有执行@ query3。在尝试将这些值与表定义连接之前,必须构建和设置@query的值。相反,您需要执行WHILE循环并填充@query的值,然后再将其与@ query1连接。

There are more issues with your methodology and practices, (such as including the TRUNCATE TABLE command in the same block) but I think that gives you the general idea, and, to be frank, I'm not a member of Stack Overflow so I can do your job for you. I'm here because I like SQL, and figuring out a puzzle is fun ... and I'm here because someone took the time to explain things to me, and if I can help someone out that's my way of paying it forward.

您的方法和实践存在更多问题(例如在同一个块中包含TRUNCATE TABLE命令)但我认为这给了您一般的想法,坦率地说,我不是Stack Overflow的成员所以我能为你做好自己的工作。我在这里是因为我喜欢SQL,想出一个谜题很有趣......而我在这里是因为有人花时间向我解释事情,如果我可以帮助别人,那就是我向前付钱的方式。

I'm going to post a query below that should get you a lot closer to your goal. That might be a bad idea, because giving it to you won't force you to read my analysis of your code and make the changes yourself. Take the time to compare the two, understand the differences and why I made these changes, and go from there. Chances are, it still needs tweaking, but try to fix it yourself before posting more questions. If you get an error message, Google it. If it runs but doesn't do what you expect, pull out the printed code and go through it to figure out why. Read this post about asking "fix my code" questions. Good luck!

我将在下面发布一个查询,让您更接近目标。这可能是一个坏主意,因为将它提供给您不会强迫您阅读我对您的代码的分析并自己进行更改。花时间比较两者,了解差异以及我为什么要做出这些改变,并从那里开始。机会是,它仍然需要调整,但在发布更多问题之前尝试自己修复它。如果您收到错误消息,请将其发送给Google。如果它运行但没有达到您的预期,请拉出打印的代码并通过它来找出原因。阅读这篇关于询问“修复我的代码”问题的帖子。祝好运!

declare @fecha varchar(6)
set @fecha = '201402'

declare @fin int
declare @cont int
declare @query varchar(5000)
declare @query1 varchar(5000)
declare @query2 varchar(900)
declare @query3 varchar(900)

set @fin = (SELECT day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@fecha+'01')+1, 0))))
print @fin


set @cont = 1
set @query = ''
while @cont <= @fin
  begin
    set @query = 
        @query + '
        S' + cast(@cont as varchar(2))+' float, 
        E' + cast(@cont as varchar(2))+' float, '
    set @cont = @cont+ 1
  end

SET @query1 = '
IF NOT EXISTS (select * from sysobjects where name = ''PLANTA_REP_PA_'+@fecha+''')
BEGIN
CREATE TABLE PLANTA_REP_PA_'+@fecha+'
  (
    ITEM int,
    UBIGEO varchar(255),
    CENTRO_POBLADO varchar(255),
    DISTRITO varchar(255),
    PROVINCIA varchar(255),
    DEPARTAMENTO varchar(255),
    TELEFONO varchar(255),
    TIPO varchar(255),
    OBSERVACION varchar(255),
    TECNOLOGIA varchar(255),
    ID_VSAT_BTS varchar(255),
    UBICACION_VSAT_BTS varchar(255),
    NIVEL_SENAL varchar(255),
    TIPO_ENERGIA varchar(255),
    COMENTARIOS varchar(255),
    CANT_LINEAS_B_ARRENDADOR varchar(255),
    '+ @query +'
    TOTAL_SALIENTE float,
    TOTAL_ENTRANTE float,
    SALIENTE_MOVILES float,
    ENTRANTE_MOVILES float
  )
END


TRUNCATE TABLE PLANTA_REP_PA_' + @fecha + '
end'

set @query2 = 'INSERT INTO PLANTA_REP_PA_' + @fecha + '
SELECT * FROM Temp_Planta_Rep_PA'

--exec (@query3)
print @query3
print (@query1)
print (@query2)

#1