|
我有以下代码,问题是我的变量列表@LocationList本质上是一个csv字符串。当我将其用作(@LocationList)中whereLocationID的一部分时,它说它不是int(LocationID是int)。我怎样才能使该csv字符串在子句中被接受?
. F" z# h( H6 d# `# yDeclare @LocationList varchar(1000)
3 m7 M/ }+ w, b( R/ ~9 N, r+ GSet @LocationList = '1,32'
7 c& K( ]8 I0 g# Q7 u- I3 a* \select Locations from table where Where LocationID in (@LocationList)& b3 | h0 X' j( M# Q$ @ I% W- E
* k0 O: y7 @& L6 J2 ?, ?8 `7 M
解决方案:4 P, h% o# u+ x- C
9 a5 D6 Y$ h# t
# |0 p4 f: W2 j" U; A4 ?, t' K9 \
6 K, O# {0 A' _2 S0 Q) h6 { 最有效的方法是使用动态SQL,例如提到rt2800(带有Michael Allen的注入警告)
) s& `0 D3 f' D1 W/ Y但是,您可以创建一个函数:7 [7 L) U8 ^5 H1 r( z
ALTER FUNCTION [dbo].[CSVStringsToTable_fn] ( @array VARCHAR(8000) )
7 D% \( K3 \( B( YRETURNS @Table TABLE ( value VARCHAR(100) ): i2 e2 a! x# Y& s
AS & }5 d5 M2 O- L' a& h q
BEGIN& h/ |6 t# w. Y' `5 S1 o3 k9 q
DECLARE @separator_position INTEGER,
! s( e: W' @8 X0 z, G7 h- G @array_value VARCHAR(8000)
7 } x1 m: X" v+ m9 [ SET @array = @array + ','3 K# }1 ^9 G4 `
WHILE PATINDEX('%,%', @array) 0
) N+ p% P4 T; z/ O: c BEGIN1 f0 F- n6 ]/ Z$ r) C5 c
SELECT @separator_position = PATINDEX('%,%', @array)
% q5 R( ~8 U0 U( j SELECT @array_value = LEFT(@array, @separator_position - 1)
8 A+ i% J6 \. \# d% M3 D2 O5 c INSERT @Table( L. T' D. y6 H( O# O
VALUES ( @array_value ). E: u% m8 D" u; _) C9 y4 b3 C
SELECT @array = STUFF(@array, 1, @separator_position, '')
, _% i. F' L+ J- w4 E( [ END
) P4 B+ p* i6 S# @; y0 B/ D RETURN9 U3 e5 q O G+ B
END' }* O' \: k! l
并从中选择:- M% N+ I8 v3 |% z" ^+ m
DECLARE @LocationList VARCHAR(1000)2 c5 s* w) U' G9 A0 @) O: D- X( C
SET @LocationList = '1,32'1 k. I$ N/ G6 O; J; W2 O
SELECT Locations
2 d+ i6 ]: h* ?3 S$ j( bFROM table+ p! e% M$ O& J C
WHERE LocationID IN ( SELECT *+ J6 U, s" o8 i6 D# ]: H5 K
FROM dbo.CSVStringsToTable_fn(@LocationList) ); M( U! Y& x3 r' y8 D
或者
) T/ u0 |- a5 n& mSELECT Locations. N- |" J" W$ a# Z3 l" e, ^. X8 T( n
FROM table loc* c# Y/ t: l, Z2 @
INNER JOIN dbo.CSVStringsToTable_fn(@LocationList) list, c2 M2 T7 n3 F
ON list.value = loc.LocationID
# e! X* }6 P" h6 z/ j: ^* O' _9 `: e当您尝试从SSRS向PROC发送多值列表时,这非常有用。 |
|