Previamente y antes de profundizar en estos conceptos de SQL, tenga en cuenta que me gusta hacer todos mis trabajos y productos en SQL Management Studio. Entonces, si quieres proseguir sigue adelante, procede y ábrelo.
Variables
Las variables son extremadamente útiles en los scripts de SQL. Esto ofrecen la flexibilidad necesaria para crear herramientas poderosas para usted. Se podrá observar de su gran utilidad en los entornos dinámicos de SQL. Los mismos que pueden, y que a menudo son utilizados como contadores de bucles para controlar cuántas veces desea que se ejecute el código dentro del bucle. Es importante indicar que se puede utilizar una instrucción para probar el valor de los datos, o un procedimiento almacenado puede usarlo para retornar a un valor.
Podemos usar la instrucción DECLARE para indicar o declarar una o más variables. A partir de ahí, podemos utilizar el comando SET para inicializar o asignar un valor a la variable. Aquí hay un ejemplo rápido:
|
1 2 3 |
DECLARE @MAXRECORD INT |
Se Puede declarar múltiples variables en la misma instrucción especifica DECLARE simplemente sepárelas con una coma. El ejemplo sería:
|
1 2 3 4 5 6 7 8 |
/*Declare Variables*/ DECLARE @DBNAME NVARCHAR(100), @RECOVERYMODE NVARCHAR(100), @MAXRECORD INT, @CURRENTRECORD INT, @SQL NVARCHAR(MAX) |
Eso parece bastante simple.
Hemos declarado una variable llamada @maxrecord como un tipo de datos INT. De tal forma que ahora podremos utilizar al SET para asignarle un valor. De la siguiente forma se tiene:
|
1 2 3 |
SET @MAXRECORD = 10 |
Acabamos de asignar la variable INT @maxrecord con un valor de 10. Esto puede cambiar a lo largo de un documento SQL, de esta forma puede estar proporcionando una gran flexibilidad.
Piense en una variable como una caja que actúa como un indicador de posicionamiento para cualquier valor que desee poner en ella.
La instrucción SET solo se puede usar en variable a la vez. Esto puede volverse engorroso si necesita asignar valores de atributos múltiples. Pero es importante indicar que usted, sí necesita usar varias declaraciones de conjunto.
|
1 2 3 4 5 |
SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery]) SET @CURRENTRECORD = 1 SET @SQL = '' |
Sin embargo, pese a todo usted puede usar una instrucción select para hacer que este proceso sea un poco más fácil. (En gran medida dependiendo de lo que estés tratando de lograr). Puede usted observar aquí un ejemplo rápido de asignación de valores a variables en una instrucción select.
|
1 2 3 4 5 |
SELECT @DBNAME = '['+[DBName]+']' FROM [#DBRecovery] WHERE [ROWNUM] = @CURRENTRECORD |
SQL dinámico
A menudo pienso en Dynamic SQL como “código que piensa”. Aunque no es del todo un proceso cognitivo, este mismo se puede usar para manejar muchas tareas que probablemente sean repetitivas o tengan una alta carga administrativa. El concepto de SQL dinámico es uno que le permite construir un script SQL o un lote completo de código SQL y presentarlo como una cadena de caracteres. Entonces puedes tomar esta cadena y ejecutarla. En esta instancia SQL Server nos brinda un par de opciones para ejecutar cadenas de caracteres como código T-SQL. Una de estas opciones nos muestra que se logra a través del simple comando EXECUTE (me gusta usar “EXEC”) o el procedimiento almacenado sp_executesql.
Entonces, nos preguntamos de nuevo, ¿por qué querríamos usar Dynamic SQL? Una razón que justificaría esta opción sería la automatización de las tareas administrativas. Supongamos que tiene un servidor que no es de producción que con cada espacio limitado usted desea asegurarse de que todas las bases de datos en su servidor estén en modo de recuperación simple (para minimizar el crecimiento de los registros de transacciones). Por esta razón es que en lugar de consultar manualmente el servidor para identificar las bases de datos que cumplen con este parámetro para luego configurarlas manualmente en el modelo de recuperación adecuado, el mismo puede escribir un fragmento de código para ejecutarlo periódicamente. Consecuentemente, este código puede identificar las bases de datos que no cumplen con las reglas establecidas y luego hacer el cambio por usted. De esta manera usted podrá observar que está reduciendo así sus gastos administrativos. Esto le ahorrará tiempo y le permitirá concentrarse en cumplir plazos reales, proyectos y demás.
Comprendiendo el uso del comando EXEC
El comando EXEC existía antes del robusto y fuerte procedimiento almacenado sp_executesql . Por esto En lugar de colocar cadenas Unicode en uno de los parámetros, simplemente colóquelo entre paréntesis después de exec.
EXEC (declaración sql)
Usted puede adicionalmente colocar el fragmento de código en una variable y ponerla la variable entre paréntesis. (Tenga en cuenta que EXEC permite el uso de cadenas de caracteres regulares y cadenas de caracteres Unicode como entrada).
Comprendiendo el procedimiento de almacenado sp_executesql
Este es un excelente procedimiento de almacenado incorporado para el SQL Server. Esta opción le permitirá utilizar parámetros de entrada y salida que le habilitan a que su código SQL dinámico sea seguro y eficiente. Por esta razón es que Los parámetros no solo tienen un propósito de flexibilidad, sino que también inhiben los ataques de inyección SQL, ya que aparecen como operadores y no forman parte del código real.
También tenga en cuenta que mientras la instrucción SQL se mantiene y permanece igual, sin embargo, solo cambian los parámetros, y que el optimizador de consultas utilizará el mismo plan de ejecución en caché.
Usemos el ejemplo anterior que se refiere a bases de datos y modelo de recuperación simple. No voy a escribir el documento explicativo completo aquí, ya que está más allá del alcance del artículo. Pero los fragmentos que proporciono deben permitirle acceder a suficiente información para mostrar cómo usar este método.
Mi objetivo es averiguar si algunas bases de datos no están en el modelo de recuperación simple. Si es este es el caso entonces es así, que quiero que las mismas se pongan en modo compatible.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
/*Insert Databases names into Temp Table*/ BEGIN TRY DROP TABLE #DBRecovery END TRY BEGIN CATCH SELECT 1 END CATCH SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]), DBName = [name], RecoveryModel = [recovery_model_desc] INTO #DBRecovery FROM sys.[databases] WHERE [recovery_model_desc] NOT IN ('Simple') /*Declare Variables*/ DECLARE @DBNAME NVARCHAR(100), @RECOVERYMODE NVARCHAR(100), @MAXRECORD INT, @CURRENTRECORD INT, @SQL NVARCHAR(MAX) /*Initialize Variables*/ SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery]) SET @CURRENTRECORD = 1 SET @SQL = '' /*BEGIN LOOP*/ WHILE @CURRENTRECORD <= @MAXRECORD BEGIN SELECT @DBNAME = '['+[DBName]+']' FROM [#DBRecovery] WHERE [ROWNUM] = @CURRENTRECORD /*Build Command*/ SET @SQL = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY SIMPLE' EXEC sys.[sp_executesql] @SQL /*Next Record*/ SET @CURRENTRECORD = @CURRENTRECORD + 1 END DROP TABLE [#DBRecovery] |
Me gusta indicar y utilizar los criterios que permitan colocar las bases de datos que deben modificarse en una tabla temporal.
|
1 2 3 4 5 6 7 8 |
SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]), DBName = [name], RecoveryModel = [recovery_model_desc] INTO #DBRecovery FROM sys.[databases] WHERE [recovery_model_desc] NOT IN ('Simple') |
Como se puede ver, estoy usando variables, ya que las mismas juegan un papel muy importante en la creación de SQL dinámico.
|
1 2 3 4 5 6 7 |
DECLARE @DBNAME NVARCHAR(100), @RECOVERYMODE NVARCHAR(100), @MAXRECORD INT, @CURRENTRECORD INT, @SQL NVARCHAR(MAX) |
Yo uso SET para asignar valores a algunas de estas las variables. En este caso, quiero que la variable @maxrecord sepa cuántos registros y ciclos en total debemos recorrer. El @currentrecord actúa como nuestro contador. Una vez que @currentrecord sea igual a @maxrecord, el ciclo finalizará.
|
1 2 3 4 5 6 |
/*Initialize Variables*/ SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery]) SET @CURRENTRECORD = 1 SET @SQL = '' |
Yo me permito el uso la variable @SQL para almacenar el código real que se ejecutará. En este caso particular es un comando simple para cambiar el modelo de recuperación de la base de datos. Es importante que usted recuerde que ya identificamos qué bases de datos no están en modo simple cuando nosotros habíamos creado la tabla temporal.
|
1 2 3 4 |
/*Build Command*/ SET @SQL = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY SIMPLE' |
Luego, finalmente, yo uso el procedimiento almacenado sp_executesql para ejecutar el comando que está guardado y almacenado en la variable @SQL.
|
1 2 3 |
EXEC sys.[sp_executesql] @SQL |
A medida que el ciclo o bucle se mueve al siguiente registro de la tabla, @DBNAME procederá al almacenado del nombre de una base de datos diferente, haciendo que el comando se ejecute en una base de datos diferente. Este es un ejemplo de código SQL que actúa dinámicamente.
Otra forma de visualizar un bucle:
Él inició su carreta de IT en el mundo de la atención al cliente y eventualmente se movió al lado de administración de redes/sistemas. Después de un tiempo, él desarrolló afección por trabajar con Bases de Datos (principalmente SQL Server) y ha enfocado su carrera en eso por los últimos ocho años. Él ha trabajado para varias industrias en ambientes grandes y pequeños, todos con necesidades diferentes.
SQL Shack le ha provisto con una oportunidad de contribuir a una comunidad que le dio mucho a través de los años.
Ver todas las entradas de Aamir Syed
- Usar variables en SQL dinámico - November 5, 2019
- Cuándo utilizar las tablas temporales de SQL frente a las variables de tabla - September 16, 2019
- Reportes de respaldos SQL con PowerShell - February 28, 2017