[RESOLVED] How to test sproc with IN operator

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:

Code:

---

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

---

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

Code:

---

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!!

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.

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.

Code:

---

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

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

Code:

---

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

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.

Quote:

---

Originally Posted by techgnome

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

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.

Code:

---

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

---

Stuff that into your query and you get this:

Code:

---

        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

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

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.