CLR Functions

You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the .NET Framework common language runtime (CLR). 

Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

1. Create the .Net function

Define the function as a static method of a class in a language supported by the .NET Framework.

2. Compile CLR Code

In order to use this code, the code has to be compiled first to create a DLL.

Run from the CMD:

For vb file: 

vbc /target:library C:\Tmp\ClrExample.vb

For C# file: 

Csc /target:library C:\Tmp\ClrExample.Cs

3. Enabling CLR Integration:

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

http://msdn.microsoft.com/en-us/library/ms131048.aspx

4. Create assembly in SQL Server

CREATE ASSEMBLY [ClrExampleAssembly] FROM 'C:\Tmp\ClrExample.dll'

GO

5. Create a Function or Stored-Procedure using the CLR function

CREATE FUNCTION [dbo].[ClrExampleUserDefinedFunc](<parameters of the function>)

RETURNS <TYPE>

WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME ClrExampleAssembly.<Class name in the code>.<Function name in the code>

<TYPE> examples:

TABLE ([StringCol] [nvarchar](max) NULL) 

RETURNS [nvarchar](255)

CREATE  PROCEDURE dbo.ClrExampleSP

<parameters of the function>

With Execute As Caller 

As 

External Name ClrExampleAssembly.<Class name in the code>.<Function name in the code>

Go

6. Use it!

-- FUNCTION:

SELECT * FROM dbo.ClrExampleUserDefinedFunc(<parameters of the function>)

--SP:

Execute ClrExampleSP <parameters of the function>

http://www.mssqltips.com/tip.asp?tip=1665

http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx

Passing a datatable to a Stored Procedure in SQL Server 2008

Table-valued parameters are a new parameter type in SQL Server 2008. 

Table-valued parameters are declared by using user-defined table types.

You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Of course you can use table-valued parameters in the SQL code.

----------------------------------------

-- create table type

----------------------------------------

CREATE TYPE dbo.TableVariableTest AS TABLE

(

ID int

)

GO

--Note: You can declare a table-valued parameter also with PKs etc.

----------------------------------------

-- create SP that uses the table type

----------------------------------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.SelectTableVariableTestItai

(

    @TableVar dbo.TableVariableTest READONLY

)

AS

BEGIN

    SET NOCOUNT ON;

    

SELECT * 

FROM dbo.Calendar c

JOIN @TableVar v ON v.ID = c.CalID

END

GO

----------------------------------------

-- test:

----------------------------------------

DECLARE @MyTable TableVariableTest

INSERT INTO @MyTable(ID)

VALUES (392),

(477),

(506)

EXEC SelectTableVariableTestItai @TableVar = @MyTable

go

----------------------------------------

-- in the trace it's look like:

----------------------------------------

declare @p1 dbo.TableVariableTest

insert into @p1 values(392)

insert into @p1 values(477)

exec SelectTableVariableTestItai @TableVar=@p1

----------------------------------------

-- the .net code:

----------------------------------------

public DataTable SelectTableVariableTestItai()

{

    DataTable dt = new DataTable();

    dt.Columns.Add("ID", typeof(int));

    DataRow row1 = dt.NewRow();

    DataRow row2 = dt.NewRow();

    row1["ID"] = 392;

    row2["ID"] = 477;

    dt.Rows.Add(row1);

    dt.Rows.Add(row2);

    db.CreateCommand("SelectTableVariableTestItai");

    db.AddParameter("TableVar", dt, SqlDbType.Structured);

    return db.ExecuteDataTable();

}

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Select Case vb.net

Select Case ResString

 Case "ActiveFeedingOrders"

         .....

 Case "0"

         .....

 Case else

  .....

End Select 'Case 

Try Parse

Integer.TryParse(aaaStr, aaaNumber)

TryParse return a boolean value - if the text (aaaStr) cab be converted to an int number. If the answer is TRUE - the int value will be set to aaaNumber.

VB.Net Logical Operators: AndAlso and OrElse

If (Not myObj Is Nothing) And (myObj.PropertyValue = "1") Then – Here we will FAIL when myObj is Nothing

If (Not myObj Is Nothing) AndAlso (myObj.PropertyValue = "1") Then – Here we will not get to the second condition when myObj is Nothing

Same logic apply for Or and OrElse