如何在SQL Server中使用动态查询执行sql
我有动态SQL的问题。 当我使用Print (@sql)
和manualy复制粘贴&,它完美的作品,但使用exec (@sql)
或exec sp_executesql @sql
如何在SQL Server中使用动态查询执行sql
在这个例子中我使用的系统表sys.types
你有什么想法我做错了吗?
CREATE TABLE [dbo].pomocnicza
(okres VARCHAR(5) PRIMARY KEY
, idWiersza INT
, cnt INT
)
INSERT INTO [dbo].pomocnicza(okres, idWiersza, cnt)
SELECT okres, idWiersza, cnt FROM(SELECT '07_03'okres, 2 idWiersza, 1 cnt
UNION
SELECT '07_04', 3, 2
UNION
SELECT '07_07', 6, 3
UNION
SELECT '07_10', 9, 4
UNION
SELECT '07_14', 13, 5) t
和动态SQL:
DECLARE @sql VARCHAR(max)
, @sqlSub VARCHAR(max)
, @cnt INT = 0
, @cntSub INT = 2
, @cnt_total INT = 0
, @okres VARCHAR(5)
, @idWiersza INT;
SELECT @cnt_total = COUNT(1) FROM [dbo].pomocnicza
WHILE @cnt <= @cnt_total
BEGIN
SELECT @okres = okres, @idWiersza = idWiersza FROM [dbo].pomocnicza WHERE cnt = @cnt
SET @sql = 'select distinct name, schema_id, ''' + @okres
+ ''' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
'
WHILE @cntSub <= @idWiersza
BEGIN
SET @sqlSub = @sqlSub + ' or isnull(lead(max_length,' + CAST(@cntSub AS VARCHAR) + ') over (partition by scale,precision order by precision),0)=0'
SET @cntSub = @cntSub + 1;
END
SET @sql = @sql + @sqlSub + ' then 0 else 1 end) all_period_available FROM sys.types'
if @cnt+1 <= @cnt_total
begin
SET @sql = @sql + '
union all
';
end
SET @cnt = @cnt + 1;
SET @sqlSub = ''
SET @cntSub = 2
print (@sql)
exec (@sql)
END;
根据什么我执行我有不同的错误
1)只有exec (@sql)
消息:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
(34 row(s) affected)
个
结果:
select distinct name, schema_id, '07_03' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_04' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_07' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_10' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_14' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,10) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,11) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,12) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,13) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
(34 row(s) affected)
结果::34行只从最后工会
3)只从最后工会
2)
print (@sql)
exec (@sql)
消息34级的行 只有print (@sql)
我有完美的工作SQL:
select distinct name, schema_id, '07_03' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
select distinct name, schema_id, '07_04' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
select distinct name, schema_id, '07_07' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
select distinct name, schema_id, '07_10' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
union all
select distinct name, schema_id, '07_14' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,10) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,11) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,12) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,13) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
你需要移动EXEC (@sql)
外While循环,即 - 你的最后三行..
print (@sql)
exec (@sql)
END;
..need更改为。 。
END;
print (@sql)
exec (@sql)
而且,你的第一个@sql =
声明必须@sql = @sql +
像你在其他地方。由于您的陈述中有UNION
,因此您只需要执行@sql
一次。为了实现这个功能,您需要在脚本的开始部分输入set @sql = ''
。
这一修正的整个脚本将看起来像这样:
DECLARE @sql VARCHAR(max) = ''
, @sqlSub VARCHAR(max) = ''
, @cnt INT = 1
, @cntSub INT = 2
, @cnt_total INT = 0
, @okres VARCHAR(5)
, @idWiersza INT;
SELECT @cnt_total = COUNT(1) FROM [dbo].pomocnicza
WHILE @cnt <= @cnt_total
BEGIN
SELECT @okres = okres, @idWiersza = idWiersza FROM [dbo].pomocnicza WHERE cnt = @cnt
SET @sql = @sql + 'select distinct name, schema_id, ''' + @okres
+ ''' as okres, getdate() as czas
, (case when scale<>0 then 100 else 0 end) scale
, (case when precision>=4
'
WHILE @cntSub <= @idWiersza
BEGIN
SET @sqlSub = @sqlSub + ' or isnull(lead(max_length,' + CAST(@cntSub AS VARCHAR) + ') over (partition by scale,precision order by precision),0)=0'
SET @cntSub = @cntSub + 1;
END
SET @sql = @sql + @sqlSub + ' then 0 else 1 end) all_period_available FROM sys.types'
if @cnt+1 <= @cnt_total
begin
SET @sql = @sql + '
union all
';
end
SET @cnt = @cnt + 1;
SET @sqlSub = ''
SET @cntSub = 2
END;
print (@sql)
exec (@sql)
当我用你的更正执行代码时,我只看到空的消息部分和“查询成功执行”。你有同样的结果吗? –
我只是略微调整了脚本。 “@sqlsub”需要由“@sqlsub”=“'初始化”,就像“@sql”一样。另外“@cnt”应该从1开始,因为“cnt”从不为0. –
现在它完美地工作。非常感谢你:) –
有什么错误讯息?打印@sql时获得的文字是什么? –
“不起作用”是什么意思?抛出一个错误?,错误的结果?,什么? – Lamak
那个内在'while'看起来不太好... –