[RESOLVED] How to test sproc with IN operator

[conwa02976]

I am using SQL Server 2008 and I need to test a stored procedure that has an IN operator in the WHERE clause. Here is a sample query:

SELECT *
FROM tblTable
WHERE (SNum = @SNum) AND (DID IN(@DID1))

If I manually put in the parameters in the query like this:

SELECT *
FROM tblTable
WHERE (SNum = 1496) AND (DID IN(1527,1573))

then my query gets back correct results.

The problem I am having is when I try to execute the sproc from a NEW QUERY window with this command:
EXEC savedproc 1496,'1527,1573'

I get nothing in the recordset.

@SNum is an INT parameter and @DID1 is a nvarchar(100) parameter.

Thanks for any help!!

[GaryMazzone]

That is because you are supplying a string type and it is being treated as string data. '1527,1573' is not the same as 1527,1573.

Possible answer is to supply the string. Then create a function that takes the supplied string and a diliminator and return a table of numbers from the function. Now you can use that as a Join clause in the SP.

[conwa02976]

I will give that a shot. I had changed the field type of the DID field from integer to nvarchar in the FROM table thinking that if I sent in a string it would accept that. Apparently not.

[jggtz]

CREATE PROC dbo.GetOrderList1
(
	@OrderList varchar(500)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @SQL varchar(600)

	SET @SQL = 
	'SELECT OrderID, CustomerID, EmployeeID, OrderDate
	FROM dbo.Orders
	WHERE OrderID IN (' + @OrderList + ')'

	EXEC(@SQL)	
END

Also
http://vyaskn.tripod.com/passing_arr...procedures.htm

JG

[techgnome]

EW! Yuck! Dynamic SQL! Pithowey! Worst possible thing you could do.

CREATE PROC dbo.GetOrderList1
(
	@OrderList varchar(500) -- Assumption: @orderList is a CSV. IE: 1,2,3,566,200,1000
)
AS
BEGIN
	SET NOCOUNT ON

DECLARE @ListTable TABLE (fieldValue int)
DECLARE @tempList varchar(500)
DECLARE @tmpValue int

SET @tempList = @OrderList -- Make a copy of the data to manipulate
WHILE CHARINDEX(',', @tempList) > 0 
  BEGIN
	SET @tmpValue = CONVERT(int, LEFT(@tempList, CHARINDEX(',', @tempList) -1))
	SET @tempList = SUBSTRING(@tempList, CHARINDEX(',', @tempList) + 1, LEN(@tempList) -CHARINDEX(',', @tempList) + 1)
	INSERT INTO @ListTable (fieldValue) VALUES (@tmpValue)
  END
-- Insert the final remaining value
INSERT INTO @ListTable (fieldValue) VALUES (CONVERT(int, @tempList))
SELECT * FROM @ListTable -- Or you can use it in a join or what ever.

END

-tg

[GaryMazzone]

I suggested the function so that it could be used again if need in for another SP without having to do the re-write each time.

[jggtz]

EW! Yuck! Dynamic SQL! Pithowey! Worst possible thing you could do.
-tg

C'mon techgnome, be serious and ellaborate explaining why, so everybody could learn
But don't forget to include the advantages

[techgnome]

three words: SQL Injection. Performance.

Consider what happens if some one were to pass in " 0); DELETE FROM tblUser WHERE UserID NOT IN (0" as a value for @OrderList.

	SET @SQL = 
	'SELECT OrderID, CustomerID, EmployeeID, OrderDate
	FROM dbo.Orders
	WHERE OrderID IN (' + @OrderList + ')'

Stuff that into your query and you get this:

	SET @SQL = 
	'SELECT OrderID, CustomerID, EmployeeID, OrderDate
	FROM dbo.Orders
	WHERE OrderID IN (0); DELETE FROM tblUser WHERE UserID NOT IN (0)'

You've now just wiped out all your users. Now think about what would happen if that went against your orders table... or your A/R table...

Additionally, because it is dynamic SQL, it has horrible performance... the execution plan can't be cached...

At least with using a table in the manner I did, the execution plan can be cached (gives performance) and if the @OrderList passed in contains something malicious, it's going to throw an error, which can be trapped for (hasta la bye-bye SQL injection.)

-tg

[conwa02976]

I am testing these possibilities now. I will let you guys know soon.

[conwa02976]

techgnome, your code worked like a charm. GaryMazzone, I will be putting it in a function because I will be using it across stored procedures. Thanks for everyones help. I will mark this thread as resolved.