How to convert this statement to stored procedure?
Hi,
How can I convert this SQL statement to stored procedure?
strSQL = "SELECT * FROM TB_PackageBookings PB " & vbCrLf
strSQL += " WHERE PB.PackageBookingID IN (" & v_lngPackageBookingIdList & ");"
The difficulties here is the IN clause, any idea?
Thanks..
Regards,
kenson
How can I convert this SQL statement to stored procedure?
strSQL = "SELECT * FROM TB_PackageBookings PB " & vbCrLf
strSQL += " WHERE PB.PackageBookingID IN (" & v_lngPackageBookingIdList & ");"
The difficulties here is the IN clause, any idea?
Thanks..
Regards,
kenson
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. It works perfectly. Anyway, which solution you think is better?
I would suggest avoiding dynamic SQL. Dynamic SQL can be very dangeous if someone finds a way to alter the input...
For example this parameter would be very unhealthy for the Dynamic SQL version:
"1,2,4) drop table TB_PackageBookings --"
For example this parameter would be very unhealthy for the Dynamic SQL version:
"1,2,4) drop table TB_PackageBookings --"
As I wrote , to prevent sql injection And to increase performance, the second option should be used...
for the record, here the article about how to use XML as "array" parameter:
http://support.microsoft.com/default.aspx?scid=kb;en-us;555266
for the record, here the article about how to use XML as "array" parameter:
http://support.microsoft.com/default.aspx?scid=kb;en-us;555266
A more general splitting function (for my reference, but also free usage), for having the delimiter parameter:
create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS
begin
declare @dx varchar(9)
--declare @loops int
-- set @loops = 0
DECLARE @TempList table
(
Value varchar(8000)
)
if @delimiter is null set @delimiter = ' '
if datalength(@delimiter) < 1 set @delimiter = ' '
set @dx = left(@delimiter, datalength(@delimiter)-1)
DECLARE @Value varchar(8000), @Pos int
SET @Parameters = @Parameters + @delimiter
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
IF REPLACE(@Parameters, @delimiter, @dx) <> ''
BEGIN
WHILE @Pos > 0 --and @Loops < 100
BEGIN
-- set @loops = @loops + 1
SET @Value = LEFT(@Parameters, @Pos - 1)
IF @Value <> ''
BEGIN
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
END
SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
END
END
INSERT @result
SELECT value
FROM @TempList
RETURN
END
My dataset is more than 5 million records. so solution should be good performance
Thanks :) Very handy
ASKER