获取影响行数和ID的存储过程
方法一:
1DECLARE @SQL NVARCHAR(256)
2DECLARE @ReturnID INT
3
4SET @SQL = 'SELECT [InfoID] FROM TM_TeachInfo WHERE [CourseID]=1 and [infosort]=1'
5
6EXEC(@SQL)
7
8SELECT @ReturnID = @@Rowcount
9
10SELECT @ReturnID
2DECLARE @ReturnID INT
3
4SET @SQL = 'SELECT [InfoID] FROM TM_TeachInfo WHERE [CourseID]=1 and [infosort]=1'
5
6EXEC(@SQL)
7
8SELECT @ReturnID = @@Rowcount
9
10SELECT @ReturnID
方法二:
1ALTER PROCEDURE UP_TM_TeachInfo_Exists_ByStr
2@strWhere varchar(1000)
3AS
4 DECLARE @ReturnID int
5 DECLARE @Sql nvarchar(400)
6
7 set @sql = 'SELECT @ReturnID = count(1) FROM TM_TeachInfo WHERE 1=1 and '+@strWhere
8 exec sp_executesql @sql,N'@ReturnID int out',@ReturnID out
9
10 IF @ReturnID = 0
11 RETURN 0
12 ELSE
13 set @sql = 'SELECT @ReturnID = [InfoID] FROM TM_TeachInfo WHERE 1=1 and '+@strWhere
14 exec sp_executesql @sql,N'@ReturnID int out',@ReturnID out
15 RETURN @ReturnID
2@strWhere varchar(1000)
3AS
4 DECLARE @ReturnID int
5 DECLARE @Sql nvarchar(400)
6
7 set @sql = 'SELECT @ReturnID = count(1) FROM TM_TeachInfo WHERE 1=1 and '+@strWhere
8 exec sp_executesql @sql,N'@ReturnID int out',@ReturnID out
9
10 IF @ReturnID = 0
11 RETURN 0
12 ELSE
13 set @sql = 'SELECT @ReturnID = [InfoID] FROM TM_TeachInfo WHERE 1=1 and '+@strWhere
14 exec sp_executesql @sql,N'@ReturnID int out',@ReturnID out
15 RETURN @ReturnID