title: Create SQL Server stored procedure with stock data
body: <p>I am trying to turn some code I have from SQL Server into a stored procedure with parameters that I can pass through but I am not sure how to do this. I want the 4 letter stock symbol of the URL to be a variable so that I can pass through different symbols, I also need this code to work as a stored procedure.</p>
  
  <p><code>GOOG</code> is where I need the variable.</p>
  
  <p><a href="http://finance.yahoo.com/webservice/v1/symbols/GOOG/quote" rel="nofollow">http://finance.yahoo.com/webservice/v1/symbols/GOOG/quote</a>'</p>
  
  <pre><code>--RSS FEED
  DECLARE @docHandle   INT;
  DECLARE @xmlData     XML;
  DECLARE @URL         NVARCHAR(255);
  DECLARE @file        NVARCHAR(255);
  DECLARE @cmd         NVARCHAR(255);
  DECLARE @sql         NVARCHAR(255);
  DECLARE @tXML        TABLE(data XML);
  
  SET @URL  = 'http://finance.yahoo.com/webservice/v1/symbols/GLUU/quote';
  SET @file = 'c:\temp\quotes.xml';
  
  -- Downloading the data
  SET @cmd = 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell (new-object System.Net.WebClient).DownloadFile( ''' + @URL + ''',''' + @file + '''  )'
  EXEC master.dbo.xp_cmdshell @cmd, no_output
  
  -- Loading the Downloaded File into the XML variable
  SET @sql = 'SELECT BulkColumn FROM OPENROWSET( BULK ''' + @file + ''', SINGLE_BLOB ) AS a'
  INSERT @tXML EXEC(@sql);
  SELECT @xmlData = data from @tXML 
  
  -- Preparing the Relational Table from the XML variable
  EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlData;
  
  INSERT INTO tblstockdata ([Name], [Price], [Symbol], [TS], [Type], [Volume])
  SELECT * FROM OPENXML(@docHandle, N'//list/resources/resource')  
    WITH ( Name    VARCHAR(10) 'field[@name="name"]',
           Price   VARCHAR(10) 'field[@name="price"]',
           Symbol  VARCHAR(10) 'field[@name="symbol"]',
           TS      VARCHAR(10) 'field[@name="ts"]',
           Type    VARCHAR(10) 'field[@name="type"]',
           Volume  VARCHAR(10) 'field[@name="volume"]');
  
  EXEC sp_xml_removedocument @docHandle;
  </code></pre>
  
  <p>Thanks!</p>
  
  <p>:)</p>