从 exec(@sql) 返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4130462/
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
Return value from exec(@sql)
提问by sreekanth
I want get the value from Exec(@sql)and assign to @Rowcount(int)
我想从中获取价值Exec(@sql)并分配给@Rowcount(int)
Here is my query:
这是我的查询：
'SET @RowCount = (select count(*)
FROM dbo.Comm_Services
WHERE CompanyId = '+cast(@CompanyId as char)+' and '+@condition+')'
回答by Manfred Sorg
On the one hand you could use sp_executesql:
一方面，您可以使用 sp_executesql：
exec sp_executesql N'select @rowcount=count(*) from anytable',
N'@rowcount int output', @rowcount output;
On the other hand you could use a temporary table:
另一方面，您可以使用临时表：
declare @result table ([rowcount] int);
insert into @result ([rowcount])
exec (N'select count(*) from anytable');
declare @rowcount int = (select top (1) [rowcount] from @result);
回答by bugstar
declare @nReturn int = 0 EXEC @nReturn = Stored Procedures
声明@nReturn int = 0 EXEC @nReturn = 存储过程
回答by kst
that's my procedure
这是我的程序
CREATE PROC sp_count
@CompanyId sysname,
@codition sysname
AS
SET NOCOUNT ON
CREATE TABLE #ctr
( NumRows int )
DECLARE @intCount int
, @vcSQL varchar(255)
SELECT @vcSQL = ' INSERT #ctr FROM dbo.Comm_Services
WHERE CompanyId = '+@CompanyId+' and '+@condition+')'
EXEC (@vcSQL)
IF @@ERROR = 0
BEGIN
SELECT @intCount = NumRows
FROM #ctr
DROP TABLE #ctr
RETURN @intCount
END
ELSE
BEGIN
DROP TABLE #ctr
RETURN -1
END
GO
回答by TylerM
Was playing with this today... I beleive you can also use @@ROWCOUNT, like this:
今天正在玩这个......我相信你也可以使用@@ROWCOUNT，像这样：
DECLARE @SQL VARCHAR(50)
DECLARE @Rowcount INT
SET @SQL = 'SELECT 1 UNION SELECT 2'
EXEC(@SQL)
SET @Rowcount = @@ROWCOUNT
SELECT @Rowcount
Then replace the 'SELECT 1 UNION SELECT 2' with your actual select without the count. I'd suggest just putting 1 in your select, like this:
然后将“SELECT 1 UNION SELECT 2”替换为不带计数的实际选择。我建议只在你的选择中放 1，就像这样：
SELECT 1
FROM dbo.Comm_Services
WHERE....
....
(as opposed to putting SELECT *)
（相对于放置 SELECT *）
Hope that helps.
希望有帮助。
回答by RPM1984
If i understand you correctly, (i probably don't)
如果我理解正确，（我可能没有）
'SELECT @RowCount = COUNT(*)
FROM dbo.Comm_Services
WHERE CompanyId = ' + CAST(@CompanyId AS CHAR) + '
AND ' + @condition
