下面使我写的代码,除去CREAT PROCEDURE sp_xxx AS ...... GO的部分运行没问题,一写成stored procedure就不行了。我是第一次用SQL script,不太熟悉,请大家帮忙看看有没有错误。谢谢!!!USE pubs
GOCREATE PROCEDURE sp_Test
ASSET ANSI_NULLS OFF
DECLARE @NameAIDID INT
DECLARE @NameA nvarchar(32)
DECLARE @NameBID INT
DECLARE @NameB nvarchar(32)
DECLARE @MyRowCount INTDECLARE my_cursor CURSOR FOR
SELECT a.NameAIDID,a.NameA,b.NameBID,b.NameB
FROM pubs.dbo.TestA a FULL OUTER JOIN pubs.dbo.TestB b
ON a.NameAIDID = b.NameBID
/*AND a.NameA = b.NameB*/
ORDER BY a.NameAIDID
OPEN my_cursorFETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
-- delete obsolete abandoned in tblNameAID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @NameA = NULL
BEGIN
DELETE FROM pubs.dbo.TestB
WHERE NameB = @NameB
END
FETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
END
CLOSE my_cursor-- get the current row count
SELECT Row_ID FROM pubs.dbo.TestB
SET @MyRowCount = @@ROWCOUNTSELECT a.NameAIDID,a.NameA,b.NameBID,b.NameB
FROM pubs.dbo.TestA a FULL OUTER JOIN pubs.dbo.TestB b
ON a.NameAIDID = b.NameBID
/*AND a.NameA = b.NameB*/
ORDER BY a.NameAIDID
OPEN my_cursorFETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
-- add new added in tblNameAID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @NameB = NULL
BEGIN
Insert pubs.dbo.TestB
Values (@NameAIDID,@NameA,'Primary Server','','',@MyRowCount + 1)
SET @MyRowCount = @MyRowCount + 1
END FETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
ENDCLOSE my_cursorDEALLOCATE my_cursor
GO
GOCREATE PROCEDURE sp_Test
ASSET ANSI_NULLS OFF
DECLARE @NameAIDID INT
DECLARE @NameA nvarchar(32)
DECLARE @NameBID INT
DECLARE @NameB nvarchar(32)
DECLARE @MyRowCount INTDECLARE my_cursor CURSOR FOR
SELECT a.NameAIDID,a.NameA,b.NameBID,b.NameB
FROM pubs.dbo.TestA a FULL OUTER JOIN pubs.dbo.TestB b
ON a.NameAIDID = b.NameBID
/*AND a.NameA = b.NameB*/
ORDER BY a.NameAIDID
OPEN my_cursorFETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
-- delete obsolete abandoned in tblNameAID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @NameA = NULL
BEGIN
DELETE FROM pubs.dbo.TestB
WHERE NameB = @NameB
END
FETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
END
CLOSE my_cursor-- get the current row count
SELECT Row_ID FROM pubs.dbo.TestB
SET @MyRowCount = @@ROWCOUNTSELECT a.NameAIDID,a.NameA,b.NameBID,b.NameB
FROM pubs.dbo.TestA a FULL OUTER JOIN pubs.dbo.TestB b
ON a.NameAIDID = b.NameBID
/*AND a.NameA = b.NameB*/
ORDER BY a.NameAIDID
OPEN my_cursorFETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
-- add new added in tblNameAID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @NameB = NULL
BEGIN
Insert pubs.dbo.TestB
Values (@NameAIDID,@NameA,'Primary Server','','',@MyRowCount + 1)
SET @MyRowCount = @MyRowCount + 1
END FETCH NEXT FROM my_cursor
INTO @NameAIDID,@NameA,@NameBID,@NameB
ENDCLOSE my_cursorDEALLOCATE my_cursor
GO
应该放在CREATE PROCEDURE sp_Test之前,否则IF @NameB = NULL可能不生效
或者
你改用
IF @NameB is NULL
-----------------FROM 联机帮助
开始我怀疑过是存储过程编译的问题,但是忽略了创建存储过程的环境设置,现在明白了。再次感谢!!!