sql-server - stored - variables @@ sql server

Servidor Sql que usa la variable en la consulta dinámica

Question

Tengo una cadena devuelta de una consulta que dice:

 +----------------------+
| returnquerystring |
+----------------------+
| exam1,exam2,exam3 |
+----------------------+

Estoy usando esta cadena devuelta como nombres de columna en una consulta dinámica.

 select * from (select score,exam from table1) x
pivot ( max(score) for exam in (exam1,exam2,exam3)

Esta consulta funciona dándome

+-------------+-----------+-----------+
| exam1 | exam2 | exam3 |
+-------------+-----------+-----------+
| 10 | 20 | 30 |
+-------------+-----------+-----------+

Sin embargo, no he podido obtener la declaración "in" de pivote para usar nada excepto los valores codificados de exam1, exam2, exam3. Por ejemplo, he usado SSMS y he creado una consulta que coloca con éxito el examen1, el examen2, el examen3 en @ var1. Sin embargo, @var1 arrojará errores cuando se use en lugar de examen1, examen2, examen3.

 declare @var1 varchar(100)
select @var1 = value from table
select * from (select score,exam from table1) x
pivot ( max(score) for exam in (@var1)
Incorrect syntax near ''@var1''.

Para verificar que lo estaba haciendo correctamente, lo hice y funcionó.

 declare @var1 int
select top 1 @var1 = id from name
select * from name where id = @var1

Esto proporcionó la fila de datos para la identificación 1 en la tabla de nombres sin error.

He notado en mis experimentos que (exam1, exam2, exam3) no puede ser (''exam1, exam2, exam3'') con las comillas.

Estoy usando ColdFusion CFSCRIPT y parece que las comillas simples están entrando en la consulta, así que probé varias pruebas con las funciones de ColdFusion para eliminarlas sin éxito.

Así que traté de usar la función de SQL Server ''replace'' alrededor de @var1 y eso arroja un error sobre la sintaxis en el reemplazo.

Esto es cuando intenté usar un ejemplo como el anterior en SSMS y todavía obtuve errores. Así que al eliminar ColdFusion de la ecuación, todavía no funciona. Mi idea era enviar toda la declaración a través del pivote como una consulta para evitar problemas de ColdFusion, pero no funciona en SSMS.

Estoy usando SQL SERVER 8 y SSMS 11.

¿Alguna idea de cómo hacer que esto funcione?

 examColumns = exam1,exam2,exam3
public any function qryExamScores(string examColumns) {
thisQry = new Query();
thisQry.setName("returnqry");
thisQry.setDatasource(application.datasource);
thisQry.addParam(name="columnNames",value=arguments.examColumns,cfsqltype="cf_sql_varchar");
result = thisQry.execute(sql="
select * from
(select id,score,exam
from table
where value1 = ''XXXXX''
and value2 = ''11111'') x
pivot
(
max(score) for exam in (:columnNames)
) p
");
returnqry = result.getResult();
return returnqry;
}

Answer 1

pasar examen1, examen2, examen3 como param varchar como: parametervalue

Queryparam (o variables de vinculación) solo se puede usar en literales. Como "exam1, exam2, exam3" se utilizan como nombres de columna en esta consulta específica, no puede aplicar queryparam a ellos. Cuando haces eso, le estás diciendo a la base de datos que esos valores son cadenas simples. Eso causa un error porque pivot espera nombres de objeto, no cadenas.

Elimine el queryparam y la consulta funcionará como se espera. Sin embargo, obviamente eso puede exponer su base de datos a la inyección sql (dependiendo de la fuente de columnNames ). Lo mismo se aplica al uso de cualquier SQL dinámico ( exec , sp_executesql , ...). Así que asegúrese de validar completamente la entrada antes de implementar este enfoque.

...
// build pivot statement with dynamic column names
columnNames = "exam1,exam2,exam3";
sqlString = "SELECT *
FROM (
SELECT score,exam
FROM table1
) x
PIVOT
(
MAX(score) FOR exam IN ("& columnNames &")
)
AS pvt ";
result = qry.execute( sql=sqlString ).getResult();
writeDump( result );

Editar:

Además, probablemente debería incluir los nombres de las columnas entre corchetes para evitar errores de sintaxis si los valores contienen espacios u otros caracteres no válidos para los nombres de las columnas.

 "[exam1],[exam2],[exam3]";

Answer 2

Necesita usar Dynamic SQL para usar el valor de la variable (@ var1) dentro de Pivot

declare @var1 varchar(100)='''',@sql nvarchar(max)
select top 1 @var1 = value from table
set @sql = ''select * from (select score,exam from table1) x
pivot ( max(score) for exam in ([''+@var1+''])) piv''
exec sp_executesql @sql

Si desea tener más de un valor en columnas pivote, use esto.

SELECT @var1 += ''['' + Isnull(CONVERT(VARCHAR(50), value), '''') + ''],''
FROM table
SELECT @var1 = LEFT(@var1, Len(@var) - 1)
SET @sql = ''select * from (select score,exam from table1) x
pivot ( max(score) for exam in ('' + @var1 + '')) piv''
EXEC Sp_executesql @sql