CREATE PROCEDURE Sp_Show_Presc_Num



@AAA_NUMBER VARCHAR(7) 



AS 



SELECT

<TABLE A>.Surname,

<TABLE A>.Initials,

<TABLE A>.AAA_NUMBER,

<TABLE B>.DOCID,

CONVERT(VARCHAR (10),<TABLE B>.ALLOCATIONDATE,103) AS ALLOCATIONDATE,

<TABLE A>.Sex_Code

FROM <TABLE A>

LEFT JOIN <TABLE B> ON <TABLE B>.BBBNUMBER = <TABLE A>.AAA_NUMBER

WHERE AAA_NUMBER=@AAA_NUMBER

SELECT     SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total

FROM         Marks INNER JOIN

                      Student ON Marks.student_id = Student.student_id INNER JOIN

                      Class ON Student.student_id = Class.student_id INNER JOIN

                      MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN

                      AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN

                      Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id

GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student.student_id, Class.student_id

ORDER BY Hurdle.hurdle_id

CREATE            view [dbo].[SII_vwYardsOnHandDueRpt]

as

SELECT  isnull(wwr.greige_item, (select top 1 item from item where family_code = 'GREIGE' and coi.item like item.item + '%')) greige_item, 

        isnull(wwr.greige_yds, 0) greige_yds, 

        isnull(wwr.finished_item, (select top 1 item from item where family_code = 'FINISHED' and coi.item like item.item + '%')) finished_item, 

        isnull(wwr.finished_yds, 0) finished_yds, 

        isnull(wwr.packed_item, coi.item) packed_item, 

        isnull(wwr.packed_desc, it.description) packed_desc, 

        isnull(wwr.packed_qty, isnull((select sum(qty_on_hand) from lot_loc where item = coi.item and loc like 'P%'), 0)) packed_qty, 

        isnull(wwr.pack_uom, it.u_m) pack_uom, 

        coi.co_num, 

        coi.co_line, 

        coi.co_release, 

        coi.due_date, 

        (coi.qty_ordered - coi.qty_shipped) * dbo.SIIItemSalesUOM2(coi.co_num, coi.co_line, coi.co_release) qty_due, 

        coi.u_m sales_uom, 

        ca.[name] customer_name,

        dbo._SIIQtyDueInYards(coi.co_num, coi.co_line, coi.co_release) qty_due_yds

FROM    coitem coi WITH(NOLOCK)

INNER JOIN item it WITH(NOLOCK)

ON      it.item = coi.item 

LEFT OUTER JOIN co WITH(NOLOCK)

ON      co.co_num = coi.co_num 

LEFT OUTER JOIN custaddr ca WITH(NOLOCK)

ON      ca.cust_num = co.cust_num 

    AND ca.cust_seq = co.cust_seq 

LEFT OUTER JOIN SII_vwWipWebReport wwr 

ON      wwr.packed_item = coi.item 

WHERE   co.stat  in ('O', 'S') 

    AND coi.stat in ('O', 'S')

 try

        {

            //Start the SSIS Here

            Application app = new Application();

            Package package = null;

            package = app.LoadPackage(@"D:\Program Files\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", null);

            //@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", null);

           

            Variables vars = package.Variables;



            vars["Time1"].Value = time;



            vars["Time2"].Value = time;



            vars["Time3"].Value = time;



            vars["TTBLTYPE"].Value = THREAD_DATA[1].ToString();



            //package.Connections["SQLNCLI10.1"].ConnectionString = obj.GetConnectionString(THREAD_DATA[0].ToString());

            //Excute Package

            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

          

            if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)

            {

                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)

                {

                   

                    Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());

                    Console.WriteLine();

                }

            }

           

        }

        catch (DtsException ex)

        {

            Exception = ex.Message;

        }

 Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

 if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)

CREATE TABLE abcd (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `path` varchar(500) NOT NULL,

  `tag` varchar(100) DEFAULT NULL,

  `name` varchar(45) NOT NULL,

  `type` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`),

  KEY `SRCHINDEX` (`TAG`,`TYP`) USING BTREE

)

Create Table New_Table

as (Select * from Old_Table)

where 1=2

USE [myDBName]

GO



DECLARE        @return_value int



EXEC        @return_value = [dbo].[sp_SMTPMail]

                @SenderName = N'John',

                @SenderAddress = N'johndoe@example.com',

                @RecipientName = N'Jane',

                @RecipientAddress = N'janedoe@example.com',

                @Subject = N'Test SP Execution',

                @Body = N'Testing 1-2-3',

                @MailServer = N'smtp.example.com'



SELECT        'Return Value' = @return_value



GO

SELECT * FROM Client

WHERE (ClientName LIKE 'X%') OR (ClientName LIKE '%[ ]X%')

SELECT * from table where Upper(name) LIKE "SID"

where Upper(name) LIKE "SID"

select case when (HbA1c_perc is NULL) or (HbA1c_perc = '') then 

        NULL 

else 

        cast(HbA1c_perc as decimal(8,2)) 

end as HbA1c_perc

from clinical