using System;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = @"Persist Security Info=False;Data Source=(Local)\SQLExpress,1660;Initial Catalog=Tempdb;Connect Timeout=15;Network Library=dbmssocn;Integrated Security=True;";

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);

            conn.Open();

            if (conn.State == System.Data.ConnectionState.Open) {

                foreach (string sqlStatement in GetAllCommands())
                {
                    try
                    {
                        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlStatement, conn);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }

                conn.Close();
                conn.Dispose();
                conn = null;
                                                
            }
            
        }

        static List<string> GetAllCommands ()
        { 
                List<string> commands = new List<string>();

                commands.Add(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_myProc]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_myProc];");

                commands.Add(@"SET ANSI_NULLS ON;");
                commands.Add(@"SET QUOTED_IDENTIFIER ON;");

                commands.Add(@"CREATE PROCEDURE [dbo].[usp_myProc] (
    @Id int
  ,  @Data varchar(10) NULL
  )
AS BEGIN
/*                    */
/*  Author:    Andrea Montanari    */
/*  Date:    2018-02-01        */
/*  Modified:  __/__/____        */
/*                    */
/*  Please report suggestions/comments/bugs/feedback to:      */
/*  pippo@pippo.com            */
/*                    */
/*      ----------------                */
/*  attivita' eseguite dalla procedura  */
/*                    */
/*  parametri:              */
/*    @Id int          - specifica la riga da selezionare  */
/*    @Data varchar(10)    - valore passato alla procedura    */
/*                    */
/*                    */

  DECLARE @msg varchar(1000);

  BEGIN TRY
    IF (@Data IS NULL) BEGIN
      SET @msg = CAST(OBJECT_NAME(@@PROCID) AS sysname)
      RAISERROR ('Parametro @Action valorizzato incorrettamente per l''esecuzione della procedura [%s].', 16, 1, @msg);
      END;

    SELECT @Id    AS [Id passato]
      ,  @Data  AS [Data]

      --FROM .....
      --WHERE ....
      ;

  END TRY
  
  BEGIN CATCH
    -- ritorna l'errore
    DECLARE @ErrorMSG varchar(2000);
    SET @ErrorMSG = ERROR_MESSAGE();
    RAISERROR (@ErrorMSG, 16, 1);
    RETURN -100;
  END CATCH

END;");

            return commands;
        }
    }
}