execute stored procedure and pass results to variable
-
I would like some help in the following where the execution results of a stored procedure could be stored in a variable. example
declare @ip varchar(30)
declare @command varchar(100)
declare @data varchar(100)
set @ip = '12.23.200.79'
declare @switch varchar(30)
set @switch = ' |find "Name"'
set @command = 'nslookup ' + @ip + @switch
exec master..xp_cmdshell @command
--I want to pass the results of above exec into say @data to use in an insert like CREATE TABLE #ip
(127.0.0.1 char (100) NULL, ([name] char (100) NULL,)
insert #ip (ip,name) select @ip, @data
Please help
-
You'll need to insert the results from xp_cmdshell into a temp table.
EG:
Create table #xp ( xp_out varchar(255) )
INSERT INTO #XP
EXEC Xp_cmdshell etc...
--------------------
Colt 45 - the original point and click interface
-
Thanks for the reply i was aware of the insert you suggested but my req. is to pass both the filelds in the same insert statement.
insert #ip (ip,name) select @ip, @data
any suggestions please.
-
In your initial example you just executed the xp_cmdshell procedure but didn't store it anywhere. If you replace that with my suggestion then the results are stored in the temp table. Then it's an easy step to insert data from that temp table and your variable into another temp table.
insert #ip (ip,name) select @ip, xp_out from #xp
If you just want to return the ip and name to the client, then leave out the insert.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply