Create procedure test_p 
@ ipAddress1 varchar (20) output, 
@ ipAddress2 varchar (20) the output 
As 
The begin 
Declare @ a1 varchar (1000) 
Declare @ I int 
The set @ I=1 
While @ i<=2 
The begin 
Set @ a1='set @ ipAddress' + convert (varchar (30), @ I) + '=' "+ left (dbo. GetSplitOfIndex (' 162.168.0.1, 192.168.0.2 ', ', '@ I), 20) +"' "' 
Print @ a1 
The exec (@ a1) 
The set @ I=@ I + 1 
End 
End 

Declare @ ip1 varchar (20) 
Declare @ ip2 varchar (20) 
The exec test_p @ ip1 output, @ ip2 output 

The function of the stored procedure: loop will string 162.168.0.1 and 162.168.0.2 were assigned to two output variables @ ipAddress1, @ ipAddress2. But after the operation is prompt did not declare a variable, as shown:

CodePudding user response:

You define variables of the statement also spliced into, such as the following, one question before you not have any similar operations

 
The set @ a1='DECLARE @ IPADDRESS VARCHAR (100) 
The set @ ipAddress '+ convert (varchar (30), @ I) +'=' ' '+ left (dbo. GetSplitOfIndex (' 162.168.0.1, 192.168.0.2', ', '@ I), 20) +' "' 

CodePudding user response:

reference 1st floor RINK_1 response:

you define variables of the statement also spliced into, such as the following, one question before you not have any similar operations

 
The set @ a1='DECLARE @ IPADDRESS VARCHAR (100) 
The set @ ipAddress '+ convert (varchar (30), @ I) +'=' ' '+ left (dbo. GetSplitOfIndex (' 162.168.0.1, 192.168.0.2', ', '@ I), 20) +' "' 

That how to dynamically assigned to the output variables of the stored procedure?

CodePudding user response:

, that is, the string will get assigned to the two output parameters of the stored procedure:
Create procedure test_p
@ ipAddress1 varchar (20) output,
@ ipAddress2 varchar (20) the output
as

CodePudding user response:

refer to the second floor of Asiatic reply:

Quote: refer to 1st floor RINK_1 response:

You define variables of the statement also spliced into, such as the following, one question before you not have any similar operations

 
The set @ a1='DECLARE @ IPADDRESS VARCHAR (100) 
The set @ ipAddress '+ convert (varchar (30), @ I) +'=' ' '+ left (dbo. GetSplitOfIndex (' 162.168.0.1, 192.168.0.2', ', '@ I), 20) +' "' 

That how to dynamically assigned to the output variables of the stored procedure?

Your IP number is dead, anyway is fixed, the two output variable quantity is dead, then don't cycle, also don't use split function, with the breakup of the SUBSTRING to directly, assignment two variable respectively,

If IP address data is not fixed, it through the split function to generate a result set, the stored procedure directly output the result set,

CodePudding user response:

Static better processing, want to learn how to deal with dynamic in this code, the stored procedure output parameter have defined, why tip is not defined in the cyclic code? Would like to know the reason?

CodePudding user response:

You put below the exec (@ a1) to SELECT @ a1 give it a try,


While @ i<=2
The begin
Set @ a1='set @ ipAddress' + convert (varchar (30), @ I) + '=' "+ left (dbo. GetSplitOfIndex (' 162.168.0.1, 192.168.0.2 ', ', '@ I), 20) +"' "'
Print @ a1
Exec (@ a1) "" ""========to SELECT @ a1
The set @ I=@ I + 1
end

CodePudding user response:

refer to 6th floor Seagull16 response:

you put below the exec (@ a1) to SELECT @ a1 give it a try,


While @ i<=2
The begin
Set @ a1='set @ ipAddress' + convert (varchar (30), @ I) + '=' "+ left (dbo. GetSplitOfIndex (' 162.168.0.1, 192.168.0.2 ', ', '@ I), 20) +"' "'
Print @ a1
Exec (@ a1) "" ""========to SELECT @ a1
The set @ I=@ I + 1
End

The select function is showed string: set @ ipAddress1='162.168.0.1', the real problem is not execute the statement;

CodePudding user response:

Sp_executesql can output parameter