日期:2014-05-18  浏览次数:20588 次

SELECT'Return Value' = @return_value
SQL code
create procedure [dbo].[PROC_ZM_INSERT]
as
begin
    
    declare insert2_cursor cursor 
        for select 
            (c.firstName+' '+isnull(c.middleName,'')+' '+c.lastName), a.addressLine1 
            from [HumanResources].[Employeeaddress] as ea 
            join [Person].[address] as a on ea.addressid = a.addressid 
            join [HumanResources].[Employee] as e on ea.employeeid = e.employeeid 
            join [Person].[contact] as c on e.contactid = c.contactid
            
    declare @name varchar(60), @address varchar(60)
    
    open insert2_cursor
        fetch insert2_cursor into @name, @address
        while @@fetch_status = 0
        begin
            begin try
                --print @address
                declare @num varchar(60)
                set @num = substring(@address,1,patindex('% %', @address))
                if isnumeric(@num) = 1
                    begin
                        if convert(int,@num)> 5000
                        begin
                            --print @name + '-----' + @address + '-insert into'
                            insert into tsql_test ([name],[addr],[lastupdatetime],[lastupdateuser],[delflg]) 
                            values (@name, substring(@address, patindex('% %', @address), len(@address)), getdate(), 'ZM', 0)
                        end
                    end
                fetch insert2_cursor into @name, @address
            end try
            begin catch
                fetch insert2_cursor into @name, @address
            end catch
        end
    close insert2_cursor                
    
    deallocate insert2_cursor            
end

--执行后

DECLARE    @return_value int

EXEC    @return_value = [dbo].[PROC_ZM_INSERT]

SELECT    'Return Value' = @return_value

GO

'Return Value'的值是-6 为什么,我看其他人的代码'Return Value'都=0 为什么我的事-6?

------解决方案--------------------
喝醉了 看得模模糊糊的 帮顶先
------解决方案--------------------
你单独执行这个存储过程看是什么结果呢?
SQL code
create procedure [dbo].[PROC_ZM_INSERT]
as
begin
    
    declare insert2_cursor cursor 
        for select 
            (c.firstName+' '+isnull(c.middleName,'')+' '+c.lastName), a.addressLine1 
            from [HumanResources].[Employeeaddress] as ea 
            join [Person].[address] as a on ea.addressid = a.addressid 
            join [HumanResources].[Employee] as e on ea.employeeid = e.employeeid 
            join [Person].[contact] as c on e.contactid = c.contactid
            
    declare @name varchar(60), @address varchar(60)
    
    open insert2_cursor
        fetch insert2_cursor into @name, @address
        while @@fetch_status = 0
        begin
            begin try
                --print @address
                declare @num varchar(60)
                set @num = substring(@address,1,patindex('% %', @address))
                if isnumeric(@num) = 1
                    begin
                        if convert(int,@num)> 5000
                        begin
                            --print @name + '-----' + @address + '-insert into'
                            insert into tsql_test ([name],[addr],[lastupdatetime],[lastupdateuser],[delflg]) 
                            values (@name, substring(@address, patindex('% %', @address), len(@address)), getdate(), 'ZM', 0)
                        end
                    end
                fetch insert2_cursor into @name, @address
            end try
            begin catch
                fetch insert2_cursor into @name, @address
            end catch
        end
    close insert2_cursor                
    
    deallocate insert2_cursor            
end