SQL“如果存在...”动态查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27710260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL "if exists..." dynamic query
提问by mayabelle
Suppose I have a query stored in a variable like this (it's actually dynamically populated and more complex, but this is for demonstration purposes):
假设我有一个查询存储在这样的变量中（它实际上是动态填充的并且更复杂，但这是出于演示目的）：
DECLARE @Query VARCHAR(1000) = 'SELECT * FROM dbo.MyTable'
Is there a way to check if the query would return any results? Something like this, but this doesn't work:
有没有办法检查查询是否会返回任何结果？像这样的东西，但这不起作用：
IF EXISTS (@Query)
BEGIN
-- do something
END
The only way that I can think of to do this is to put the results in a temp table and then query from that, but that is not ideal because the columns in the dynamic query can vary and I really don't need the temp table at all for any reason other than checking whether some rows would be returned. Is there a better way?
我能想到的唯一方法是将结果放在临时表中，然后从中查询，但这并不理想，因为动态查询中的列可能会有所不同，我真的不需要临时表除了检查是否会返回某些行以外的任何原因。有没有更好的办法？
回答by P?????
Try Executing the Dynamic queryand use @@RowCountto find the existence of rows.
尝试执行Dynamic query和使用@@RowCount来查找行的存在。
DECLARE @Query NVARCHAR(1000) = 'SELECT * FROM [dbo].[Mytable]',
@rowcnt INT
EXEC Sp_executesql @query
SELECT @rowcnt = @@ROWCOUNT
IF @rowcnt > 0
BEGIN
PRINT 'row present'
END
回答by dario
Try this:
尝试这个：
DECLARE @Query NVARCHAR(1000) = 'SELECT @C = COUNT(*) FROM dbo.MyTable'
DECLARE @Count AS INT
EXEC sp_executesql @Query, N'@C INT OUTPUT', @C=@Count OUTPUT
IF (@Count > 0)
BEGIN
END
回答by Sam
I know this answer is too late. but, I'm leaving this here to help someone else to use IF EXISTSwith a dynamic query.
我知道这个答案为时已晚。但是，我将它留在这里是为了帮助其他人使用IF EXISTS动态查询。
This is how you should do it with dynamic queries.
这就是您应该如何使用动态查询执行此操作。
DECLARE @Query VARCHAR(MAX)
SET @Query = 'SELECT * FROM [dbo].[MyTable]'
SET @Query = 'IF EXISTS (' + @Query + ')
BEGIN
-- do something
print ''1''
END
ELSE
BEGIN
-- do something else
print ''0''
END
'
exec (@Query)
Hope this helped someone. Vote if it did :)
希望这对某人有所帮助。投票如果有:)
回答by Ondrej Liptak
Hi I think that only way is to put IF EXISTS part into code of execution. My case is to stop execution in point when select affects at least one row, that is goal of IF EXISTS.
嗨，我认为唯一的方法是将 IF EXISTS 部分放入执行代码中。我的情况是在 select 影响至少一行时停止执行，这是 IF EXISTS 的目标。
Little example that saves reading all records covering by condition to first occurence:
保存按条件读取所有记录到第一次出现的小例子：
set nocount off;
drop table if exists #temp
go
create table #temp (idCol int identity(1,1),someText nvarchar(1))
go
insert into #temp values ('a')
go 25000
declare @query nvarchar(max)
,@resultFork bit
set @query = 'if exists (select * from #temp where idCol % 3 = 0)
set @resultFork=1
else
set @resultFork=0'
print @query
exec sp_executeSQL @query, N'@resultFork int output', @resultFork=@resultFork output
print @resultFork
/*Now U can use @resultFork in simple if condition...
if @resultFork = 1
begin
--
end
else
begin
--
end
*/
回答by HaveNoDisplayName
You can use EXEC to execute sql statement, then call @@ROWCOUNTwhich Returns the number of rows affected by the last statement, to check row exists in sql select stetement.
您可以使用 EXEC 执行 sql 语句，然后调用@@ROWCOUNTwhich 返回受最后一条语句影响的行数，以检查 sql select 语句中是否存在该行。
DECLARE @Query VARCHAR(1000) = 'SELECT * FROM dbo.MyTable',@hasRow int
EXEC (@Query)
SELECT @hasRow =@@ROWCOUNT // Returns the number of rows affected by the last statement
PRINT @hasRow
IF @hasRow > 0
BEGIN
Print 1
END
BEGIN
Print 2
END
