title: Need help on SQL Server 2008 programming
body: <p>I am very new to SQL Server 2008 programming and trying to create a procedure.</p>
  
  <p>Well, the requirement is <em>'The procedure returns data either based on  the input parameter OR if no input data is given-it should do a default select and return all data qualifying'</em></p>
  
  <p>I tried out with something like this-</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Proc_sampletestproc]
     (@testid int)
  AS
  BEGIN         
     SET NOCOUNT OFF;  ----I worked with Oracle PL/SQL and so do we need for mandatory this declarations
  </code></pre>
  
  <p>Here, I need to put a check that if the input <code>testid</code> has a value or not. If it exists, we have one case with a select or we do a default select.</p>
  
  <p>Also, I am putting a direct <code>SELECT</code> with no joins on tables. How would I <code>JOIN</code> the tables along with <code>OUTER JOINS</code> because a <code>testid</code> may or may not have an insurance? I mean syntactically - syntax is quite different in SQL Server.</p>
  
  <pre><code>SELECT 
      T.TESTID, T.NAME, TI.INSURENAME 
  FROM 
      testinsured ti, test t, testinsuredHistory tih
  WHERE
      t.testid = ti.testid  -----This entry may be there or not IN THE testinsured TABLE
      AND tih.testinsuredid = ti.testinsuredid --A testid might have 2 Insurers whose history is stored here.
      AND TIH.STARTDATE IS NOT NULL
      AND TIH.ENDDATE IS NOT NULL     --TO CHECK ACTIVE DATES FOR COVERAGE
  </code></pre>
  
  <p>Also, I want to do a group by on <code>testid</code> so that the name comes once but the <code>InsuredPlanname</code> comes accordingly once, twice as many as each <code>Testid</code> has.</p>