日期:2014-05-18 浏览次数:21198 次
ALTER PROCEDURE [dbo].[p_addUser] @UserName varchar(50), @DisplayName varchar(50), @Address varchar(50), @PassWord varchar(50), @Email varchar(50), @Phone varchar(50), @Description varchar(100), @CityId int, @groupId int, @UserID int, @ReturnID int output AS BEGIN select * from T_userinfo where username=@UserName if(@@ROWCOUNT > 0) SELECT @ReturnID=0 else begin INSERT INTO t_UserInfo(UserName,DisplayName,Address,PassWord,Email,Phone,Description,CityId) VALUES(@UserName,@DisplayName,@Address,@PassWord,@Email,@Phone,@Description,@CityId) IF(@@ERROR=0) BEGIN IF(@groupId<>'') BEGIN SELECT @ReturnID = SCOPE_IDENTITY() insert into t_Relation_User_Group(groupId,userId) values (@groupId,@ReturnID) end end ELSE SELECT @ReturnID=-1 exec sp_xxx end end
sp_depends 'p_test' 在当前数据库中,指定的对象引用了以下内容: name type updated selected column ---------------------------------------------------------------------------------------------------------------- ---------------- ------- -------- -------------------------------------------------------- dbo.tc user table no yes KHDM dbo.tc user table no yes JE
------解决方案--------------------
-- 介绍部分 还是用文本文件 加 正则表达式处理的好. sys.sql_dependencies会漏,不是很清楚它的具体实现. 如下的SQL代码 可以做到查找一个存储过程调用的所有存储过程.当然你会发现它的效率极低. 特别是一个库里的存储过程总数很多时.所以如下的代码仅供参考.其中一些内容注释了, 功能也仅仅是查找存储过程和调用存储过程的关系. --代码(存储过程) DROP PROCEDURE Find_RelatedTabUsp_INUsp GO CREATE PROCEDURE Find_RelatedTabUsp_Inusp @Find_Usp varchar(200) AS SET NoCOUNT ON DECLARE @usp_name varchar(200) --SET @Find_Usp = 'usp_testIn' --EXEC usp_testOut EXEC usp_testIn if exists (select * from dbo.sysobjects where id = object_id(N'#ContentUsp') and OBJECTPROPERTY(id, N'IsTable') = 1) DROP TABLE #ContentUsp CREATE TABLE #ContentUsp (usp_name varchar(200),Reprinted_name varchar(200)) DECLARE mycursor CURSOR FOR SELECT NAME FROM sys.procedures OPEN mycursor FETCH NEXT FROM mycursor INTO @usp_name WHILE @@FETCH_STATUS=0 BEGIN -- PRINT @usp_name DECLARE @object_name varchar(MAX) SET @object_name = @usp_name DECLARE @SQL nvarchar(max) SET @SQL = 'INSERT INTO #ContentUsp SELECT DISTINCT OBJECT_NAME(object_id),@object_name FROM SYS.SQL_MODULES WHERE definition LIKE ''%''+ @object_name+''%'' AND OBJECT_NAME(object_id) <> @object_name' EXEC sp_executesql @SQL,N'@object_name nvarchar(200)',@object_name