Arreglos en Funciones

jejeje, por eso digo.. eso esta en el rango del 5%...



Fernando España

el 95% de las instrucciones que precisan de un
CURSOR se puede hacer sin ellos y mas
Eficientemente.--

BRIGADA AINTI-CURSORES
"Diego Uribe" wrote in message
news:

Ejem!!

Como es aquello de la variable tipo tabla?
No sera un cursor verdad? -jejeje-




"Fernando España" escribió en el

mensaje

news:%
> Pero puedes usar una variable tipo tabla
>
>
> Fernando España
>
> el 95% de las instrucciones que precisan de un
> CURSOR se puede hacer sin ellos y mas
> Eficientemente.--
>
> BRIGADA AINTI-CURSORES
> "Isaías" wrote in message
> news:0a1901c3821a$5bf02050$
> > No existen los ARRAYS en SQL.
> >
> >
>
>

copiado del bol.

DECLARE @local_variable
Variables are declared in the body of a batch or procedure with the DECLARE
statement and are assigned values with either a SET or SELECT statement.
Cursor variables can be declared with this statement and used with other
cursor-related statements. After declaration, all variables are initialized
as NULL.

Syntax
DECLARE
{{ @local_variable data_type }
| { @cursor_variable_name CURSOR }
| { table_type_definition }
} [ ,...n]

< table_type_definition > :: TABLE ( { < column_definition > | < table_constraint > } [ ,... ]
)

< column_definition > :: column_name scalar_data_type
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ ( seed, increment ) ] ]
[ ROWGUIDCOL ]
[ < column_constraint > ]

< column_constraint > :: { [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
}

< table_constraint > :: { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] )
| CHECK ( search_condition )
}

Arguments
@local_variable

Is the name of a variable. Variable names must begin with an at sign (@).
Local variable names must conform to the rules for identifiers. For more
information, see Using Identifiers.

data_type

Is any system-supplied or user-defined data type. A variable cannot be of
text, ntext, or image data type. For more information about system data
types, see Data Types. For more information about user-defined data types,
see sp_addtype.

@cursor_variable_name

Is the name of a cursor variable. Cursor variable names must begin with an
at sign (@) and conform to the rules for identifiers.

CURSOR

Specifies that the variable is a local, cursor variable.

table_type_definition

Defines the table data type. The table declaration includes column
definitions, names, data types, and constraints. The only constraint types
allowed are PRIMARY KEY, UNIQUE KEY, NULL, and CHECK.

table_type_definition is a subset of information used to define a table in
CREATE TABLE. Elements and essential definitions are included here; for more
information, see CREATE TABLE.

n

Is a placeholder indicating that multiple variables can be specified and
assigned values. When declaring table variables, the table variable must be
the only variable being declared in the DECLARE statement.

column_name

Is the name of the column in the table.

scalar_data_type

Specifies that the column is a scalar data type.

[COLLATE collation_name]

Specifies the collation for the column. collation_name can be either a
Windows collation name or an SQL collation name, and is applicable only for
columns of the char, varchar, text, nchar, nvarchar, and ntext data types.
If not specified, the column is assigned either the collation of the
user-defined data type (if the column is of a user-defined data type), or
the default collation of the database.

For more information about the Windows and SQL collation names, see COLLATE.

DEFAULT

Specifies the value provided for the column when a value is not explicitly
supplied during an insert. DEFAULT definitions can be applied to any columns
except those defined as timestamp, or those with the IDENTITY property.
DEFAULT definitions are removed when the table is dropped. Only a constant
value, such as a character string; a system function, such as a
SYSTEM_USER(); or NULL can be used as a default. To maintain compatibility
with earlier versions of SQL Server, a constraint name can be assigned to a
DEFAULT.

constant_expression

Is a constant, NULL, or a system function used as the default value for the
column.

IDENTITY

Indicates that the new column is an identity column. When a new row is added
to the table, SQL Server provides a unique, incremental value for the
column. Identity columns are commonly used in conjunction with PRIMARY KEY
constraints to serve as the unique row identifier for the table. The
IDENTITY property can be assigned to tinyint, smallint, int, decimal(p,0),
or numeric(p,0) columns. Only one identity column can be created per table.
Bound defaults and DEFAULT constraints cannot be used with an identity
column. You must specify both the seed and increment, or neither. If neither
is specified, the default is (1,1).

seed

Is the value used for the very first row loaded into the table.

increment

Is the incremental value added to the identity value of the previous row
that was loaded.

ROWGUIDCOL

Indicates that the new column is a row global unique identifier column. Only
one uniqueidentifier column per table can be designated as the ROWGUIDCOL
column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier
column.

NULL | NOT NULL

Are keywords that determine whether or not null values are allowed in the
column.

PRIMARY KEY

Is a constraint that enforces entity integrity for a given column or columns
through a unique index. Only one PRIMARY KEY constraint can be created per
table.

UNIQUE

Is a constraint that provides entity integrity for a given column or columns
through a unique index. A table can have multiple UNIQUE constraints.

CHECK

Is a constraint that enforces domain integrity by limiting the possible
values that can be entered into a column or columns.

logical_expression

Is a logical expression that returns TRUE or FALSE.

Remarks
Variables are often used in a batch or procedure as counters for WHILE,
LOOP, or for an IF...ELSE block.

Variables can be used only in expressions, not in place of object names or
keywords. To construct dynamic SQL statements, use EXECUTE.

The scope of a local variable is the batch, stored procedure, or statement
block in which it is declared. For more information about using local
variables in statement blocks, see Using BEGIN...END.

A cursor variable that currently has a cursor assigned to it can be
referenced as a source in a:

a.. CLOSE statement.


b.. DEALLOCATE statement.


c.. FETCH statement.


d.. OPEN statement.


e.. Positioned DELETE or UPDATE statement.


f.. SET CURSOR variable statement (on the right side).
In all these statements, Microsoft® SQL ServerT raises an error if a
referenced cursor variable exists but does not have a cursor currently
allocated to it. If a referenced cursor variable does not exist, SQL Server
raises the same error raised for an undeclared variable of another type.

A cursor variable:

a.. Can be the target of either a cursor type or another cursor variable.
For more information, see SET @local_variable.


b.. Can be referenced as the target of an output cursor parameter in an
EXECUTE statement if the cursor variable does not have a cursor currently
assigned to it.


c.. Should be regarded as a pointer to the cursor. For more information
about cursor variables, see Transact-SQL Cursors.
Examples
A. Use DECLARE
This example uses a local variable named @find to retrieve author
information for all authors with last names beginning with Ring.

USE pubs
DECLARE @find varchar(30)
SET @find = 'Ring%'
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE @find
Here is the result set:

au_lname au_fname phone
Ringer Anne 801 826-0752
Ringer Albert 801 826-0752

(2 row(s) affected)
B. Use DECLARE with two variables
This example retrieves employee names from employees of Binnet & Hardley
(pub_id = 0877) who were hired on or after January 1, 1993.

USE pubs
SET NOCOUNT ON
GO
DECLARE @pub_id char(4), @hire_date datetime
SET @pub_id = '0877'
SET @hire_date = '1/01/93'
SET NOCOUNT OFF
SELECT fname, lname
FROM employee
WHERE pub_id = @pub_id and hire_date >= @hire_date
Here is the result set:

fname lname
Anabela Domingues
Paul Henriot

(2 row(s) affected)

See Also

EXECUTE

Functions

SELECT

table

©1988-2000 Microsoft Corporation. All Rights Reserved.


Fernando España

el 95% de las instrucciones que precisan de un
CURSOR se puede hacer sin ellos y mas
Eficientemente.--

BRIGADA AINTI-CURSORES
"Diego Uribe" wrote in message
news:

Pero y como se define un tipo tabla?
y como se recorre, es decir, es la primera vez que oigo de este tipo de
variables

Muchisimas gracias por la ayuda Fernando.


"Fernando España" escribió en el

mensaje

news:
> jejeje, por eso digo.. eso esta en el rango del 5%...
>
>
>
> Fernando España
>
> el 95% de las instrucciones que precisan de un
> CURSOR se puede hacer sin ellos y mas
> Eficientemente.--
>
> BRIGADA AINTI-CURSORES
> "Diego Uribe" wrote in message
> news:
> > Ejem!!
> >
> > Como es aquello de la variable tipo tabla?
> > No sera un cursor verdad? -jejeje-
> >
> >
> >
> >
> > "Fernando España" escribió en el
> mensaje
> > news:%
> > > Pero puedes usar una variable tipo tabla
> > >
> > >
> > > Fernando España
> > >
> > > el 95% de las instrucciones que precisan de un
> > > CURSOR se puede hacer sin ellos y mas
> > > Eficientemente.--
> > >
> > > BRIGADA AINTI-CURSORES
> > > "Isaías" wrote in message
> > > news:0a1901c3821a$5bf02050$
> > > > No existen los ARRAYS en SQL.
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Creo que te va a resultar muy interesante este artículo:
http://www.algonet.se/~sommar/arrays-in-sql.html

De todos modos, creo que todos los artículos de este hombre son igual de
interesantes (los recomiendo, merecen la pena todos ellos). Lo único malo es
que están en inglés :-S espero que no tengas problemas con ello...



Un saludo

-
"Sólo sé que no sé nada. " (Sócrates)

Por favor, responder únicamente al foro

Se agradece la inclusión de sentencias DDL

Archivo de respuestas anteriores en:
http://groups.google.com/groups?hl=....public.es
.sqlserver

(Guía de netiquette del foro)
http://www.helpdna.net/bosqlfaq00.htm
http://perso.wanadoo.es/rubenvigon/foro

(FAQ's de SQL Server)
http://support.microsoft.com/defaul.../70faq.asp
http://www.helpdna.net/bosqlfaq.htm

"Diego Uribe" escribió en el mensaje
news:

Hola

Alguien sabe como se crea un vector / arreglo o Array dentro de una

funcion

de SQL Server?

Gracias

Muchas gracias a todos por la ayuda.
Procedere con el desarrollo utilizando lo sugerido.


"Diego Uribe" escribió en el mensaje
news:

Hola

Alguien sabe como se crea un vector / arreglo o Array dentro de una

funcion

de SQL Server?

Gracias