função que retorna um valor escalar no SQL Server

VB .NET – Executando uma função que retorna um valor escalar no SQL Server

Hoje vou mostrar como recuperar um valor de uma função definida pelo usuário no SQL Server que retorna um valor escalar usando ADO .NET. Para fazer isso, vamos criar a Function (Função) no SQL Server e tratá-la como se fosse um procedimento armazenado. A partir do SQL Server 2000 temos o recurso chamado Functions, que permite criar funções para auxiliar na consulta e obtenção de informação.
Uma função definida pelo usuário é uma rotina Transact-SQL ou CLR (Common Language Runtime) que aceita parâmetros, executa uma ação (como um cálculo complexo) e retorna o resultado dessa ação como um valor. O valor de retorno pode ser um valor escalar (único) ou uma tabela.
A criação de uma user function é similar à criação de uma stored procedure ou uma view. Atualmente, temos a possibilidade de criar funções dos seguintes tipos:

• Inline Function – São usadas para parametrizar views;
• Table-valued Function – Usada para lógicas complexas; retorna uma tabela;
• Scalar-valued Function – Retorna apenas um parâmetro; são semelhantes as funções pré-existentes no SQL Server(Ex: getDate())

Vamos, então, criar uma Scalar-valued Function e mostrar como podemos tratar o seu retorno usando ADO .NET. Os recursos necessários usados neste artigo foram:

• Visual Basic 2010 Express Edition;
• SQL Server 2008 Express;
• Banco de dados Northwind.mdf.

Abra o Visual Basic 2010 Express Edition e crie um novo projeto (File-> New Project) do tipo Windows Forms Application com o nome de SQLServer_FuncaoEscalar:

Agora vamos abrir a janela DataBase Explorer e exibir os objetos do banco de dados Northwind.mdf. Depois clique com o botão direito sobre o item Functions e selecione Add New -> Scalar-valued Function.

Será aberta uma janela com uma estrutura pronta para criarmos a nossa função:

Defina a função com o nome ValorEstoque, conforme mostra a figura a seguir:

Esta função calcula o valor do estoque para um produto multiplicando o seu preço unitário pelo sua quantidade em estoque. Agora vamos usar a instrução com um DataAdapter para preencher um DataTable com os cinco primeiros registros da tabela Products do banco de dados Northwind.mdf. Assim vamos obter o valor do estoque para cada registro usando a função criada com o valor escalar.
Na janela Solution Explorer selecione o formulário form1 e a partir da ToolBox vamos incluir os seguintes controles no formulário:

• DataGridView – name = gdvDados
• Button – btnExecutar

Conforme o leiaute abaixo:

No evento Click do botão Executar, vamos incluir o código abaixo:

Private Sub btnExecutar_Click(ByVal sender As System.Object, ByVal e As 

System.EventArgs) Handles btnExecutar.Click

        Try
            Dim sqlConnectString As String = "Data Source=.\SQLEXPRESS;Integrated 

security=SSPI;Initial Catalog=Northwind;"

            'Dim sql1 As String = "SELECT TOP 5 *, " + "dbo.ValorProdutoEstoque

(UnitPrice, UnitsinStock) as " + "ValorEstoque FROM Products"
            Dim sql2 As String = "SELECT ProductID, ProductName, ValorEstoque=dbo.

ValorEstoque(UnitPrice, UnitsInStock) FROM Products"

            Dim da As New SqlDataAdapter(sql2, sqlConnectString)
            Dim dt As New DataTable()
            da.Fill(dt)

            gdvDados.DataSource = dt
            formatagrid()

        Catch ex As Exception
            MessageBox.Show("Erro : " + ex.Message)
        End Try

    End Sub

Estamos chamando a função na instrução SQL:

Dim sql2 As String = "SELECT ProductID, ProductName, ValorEstoque=dbo.ValorEstoque

(UnitPrice, UnitsInStock) FROM Products"

Note que temos que informar o ower da function ao chamá-la, por isso usamos dbo.ValorEstoque(UnitPrice, UnitsInStock). Se você chamar ValorEstoque(UnitPrice, UnitsInStock) não vai funcionar.
A rotina formatagrid() tem o seguinte código e é usada para formatar o controle datagridview:

Private Sub formatagrid()
        With gdvDados
            '.AutoGenerateColumns = True
            .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.

DisplayedCellsExceptHeaders
            .ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
            'altera a cor das linhas alternadas no grid
            .RowsDefaultCellStyle.BackColor = Color.White
            .AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine
            'altera o nome das colunas
            .Columns(0).HeaderText = "Código"
            .Columns(1).HeaderText = "Produto"
            .Columns(2).HeaderText = "Valor do Estoque"
            .Columns(0).Width = 50
            .Columns(1).Width = 300
            .Columns(2).Width = 200
            'esconde a coluna
            .Columns("ProductID").Visible = False
            'formata as colunas valor, vencimento e pagamento
            .Columns(2).DefaultCellStyle.Format = "c"
            'seleciona a linha inteira
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            'não permite seleção de multiplas linhas
            .MultiSelect = False
            ' exibe nulos formatados
            .DefaultCellStyle.NullValue = " - "
            'permite que o texto maior que célula não seja truncado
            .DefaultCellStyle.WrapMode = DataGridViewTriState.True
            'define o alinhamamento 
            .Columns("ValorEstoque").DefaultCellStyle.Alignment = 

DataGridViewContentAlignment.MiddleRight
        End With
    End Sub

Executando o projeto iremos obter:

Percebeu como é fácil criar e utilizar uma função definida pelo usuário (FUNCTION) no SQL Server? Você pode criar Functions para obter informações e realizar operações.
Mas qual a vantagem das Functions sobre os Procedimentos armazenados? Uma das vantagens de funções definidas pelo usuário sobre os procedimentos armazenados, é o fato de que uma UDF pode ser usada em instruções Select, Where ou em uma declaração Case.
Elas também podem ser usadas para criar associações (Joins). Além disso, funções definidas pelo usuário são mais simples para invocar do que procedimentos armazenados de dentro de outra instrução SQL.