Glimpse Of Technologies

GoTo in SQL Store Procedures

2021-07-19T01:17:00.003-07:00

GoTo in Microsoft SQL Server is a keyword in the control flow of a SQL stored procedure. It allows logic to "go to" another point in the code. This effectively allows for jumping around the code and skipping/exiting conditions in an easy way.

Syntax:

DECLARE 
    @condition1 varchar='true';
    @condition2 varchar='true';
IF @condition1='true'
    BEGIN
        GOTO DevB;
    END
    
DevA:
    <SQL statements>
    GOTO DevEnd;
    
DevB:
    <SQL statements>
    IF @condition2='true'
        BEGIN
            GOTO DevA;
        END
DevEnd:
    return;

Read more in the official Microsoft doc here.

Taking a look at CROSS APPLY

2012-04-10T09:52:00.001-07:00

I think the easiest way to use of CROSS APPLY is that it is like a CROSS JOIN with a correlated sub-query instead of a derived table. Let's see if I can explain the bit of cross apply.... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible. For example, consider:

Select A.*, b.X
from A
cross join
(select B.X from B where B.Val=A.Val) b

That is not legal because A.Val is out of scope within the derived table; this is because the derived table is evaluated independently of the other tables in the SELECT. To limit the rows in table B so that B.Val = A.Val, we must do that outside of the derived table via a join or in the criteria:

select A.*, b.X
from A
cross join (select * from B) b
where A.Val = b.Val

(The above example is equivalent to doing an INNER JOIN to the derived table)
Also want you keep updated that, the scope-of-derived-tables rule isn't just for CROSS JOINS, it's for all JOINS -- CROSS, INNER, OUTER and even UNION; they all use "self-contained" derived tables.
This is an easy way to think of the difference between CROSS JOIN and CROSS APPLY. CROSS JOIN, as we saw, joins to a derived table; however, CROSS APPLY, despite looking like a JOIN, actually is applying a correlated sub-query. This imposes both the advantages of a correlated sub-query but also the performance implications.
So, we can simply rewrite our first example using CROSS APPLY like this:

Select A.*, b.X
from A
cross apply (select B.X from B where B.Val=A.Val) b

Since we are performing an APPLY and not a JOIN, A.Val is in scope and it works just fine. Please comment if having any query or want to know some more more topics related
SQL..... Thanks DevB......

HL7(Health Level 7) Intro.....

2012-03-06T06:37:00.006-08:00

HL7 isn’t a language per say but rather a set of standards that dictate the format in whicelectronic information is exchanged between otherwise disassociated healthcare systems.

HL7 is standard for the interchange of healthcare data.
A set of message-based transactions between healthcare applications.
HL7 message protocol supports unsolicited messages as well as solicited messages.
HL7 encoding rules produce human-readable variable length delimited ASCII messages.
A domain-specific, common protocol for the exchange of health care information.

The name "Health Level-7" is a reference to the seventh layer of the ISO OSI Reference model also known as the application layer. The name indicates that HL7 focuses on application layer protocols for the health care domain, independent of lower layers. HL7 effectively considers all lower layers merely as tools.