|
我有以下代码,问题是我的变量列表@LocationList本质上是一个csv字符串。当我将其用作(@LocationList)中whereLocationID的一部分时,它说它不是int(LocationID是int)。我怎样才能使该csv字符串在子句中被接受?
\, S H9 Q4 n8 a, L N* {6 NDeclare @LocationList varchar(1000)
5 l$ F n+ C& [* D' MSet @LocationList = '1,32'0 w: c- Z2 T6 K( _. R
select Locations from table where Where LocationID in (@LocationList)+ {* U0 n- R6 }
4 @7 a4 N, ]4 T6 f) D解决方案:
. C" n7 W, u5 H& `% N7 z
6 ~& e% A& a8 j0 ~3 M) b
+ W* V6 j/ ?+ g1 y X) V# J0 J0 }
最有效的方法是使用动态SQL,例如提到rt2800(带有Michael Allen的注入警告)0 B2 ~7 E: N" z* a3 [* X( a
但是,您可以创建一个函数:
0 e j8 }( O" Z4 hALTER FUNCTION [dbo].[CSVStringsToTable_fn] ( @array VARCHAR(8000) )1 u# ?, \4 `; F+ F
RETURNS @Table TABLE ( value VARCHAR(100) )) l; Y! d9 \" ~, D E
AS
; @8 ], ]$ g/ {# C5 S$ | BEGIN, s: q& V, y7 z* u% q4 F2 s+ m
DECLARE @separator_position INTEGER,
. @6 J/ C' ~) n Z" ~3 q @array_value VARCHAR(8000)5 G* q" m' v# D# X5 {9 i
SET @array = @array + ','
! T; A% ^; T ?( }9 E; d0 E WHILE PATINDEX('%,%', @array) 0
* J1 \' o* D. {# f BEGIN
6 {& H; I- }+ r( ^/ w3 S SELECT @separator_position = PATINDEX('%,%', @array)
& k: c( y6 l+ O0 r. C SELECT @array_value = LEFT(@array, @separator_position - 1)- W% O5 S4 l) W
INSERT @Table
6 j; f! _- g& |6 B% d) K VALUES ( @array_value )1 ]3 |: ?9 I3 A# v3 `
SELECT @array = STUFF(@array, 1, @separator_position, '')
7 T7 l- n. x F2 r2 H' K* v END
, |8 @! B z1 N4 C% q8 R3 ` RETURN- h) T X. M% T3 ?
END3 P8 e& M1 }( W
并从中选择:- ?0 E& R% R4 i( M
DECLARE @LocationList VARCHAR(1000)7 C7 s0 X: Z6 V% N
SET @LocationList = '1,32'
! W3 a2 ^3 p ?) c7 ^' tSELECT Locations
& H$ d$ i' |( i! oFROM table
& [) l! z& e9 V9 y) a1 j* gWHERE LocationID IN ( SELECT */ C& Q, P" X& J5 @5 `
FROM dbo.CSVStringsToTable_fn(@LocationList) )- R: ~! d1 E R% D
或者
6 O" F- s+ o" Q- N, s+ _) ASELECT Locations: \4 B9 s i; G4 r: x
FROM table loc' t, n% w( S( O/ v9 t5 `
INNER JOIN dbo.CSVStringsToTable_fn(@LocationList) list
& D" ~" O$ W: m9 D# b3 V ON list.value = loc.LocationID; N9 o. x7 ]; T2 O; ^9 q
当您尝试从SSRS向PROC发送多值列表时,这非常有用。 |
|