Using the IN Notation in Stored Procedures with several IN arguments.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using the IN Notation in Stored Procedures with several IN arguments.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-25 : 21:40:26

Håkon writes "Using the IN Notation in Stored Procedures with several IN arguments.

I have a problem when I try to use sereral values in the IN argument in a stored procedure.

I have a procedure called GetData. It has Two in-arguments, DType and PCode.
The Stored Procedure works fine with one value in each argument, like
GetData 'var1','var2'

When I try to use several values with each In argument, like
GetData 'var1, var3','var2' I get a problem. The IN clause sees
'var1, var3' as one value and no match with the database is found.

I tried to solve this by using a temp table in the stored procedure

CREATE TABLE tab ( X VARCHAR (30) )
INSERT INTO tab VALUES('var1')
INSERT INTO tab VALUES('var3')

Just to test it I inserted two values into it.

I then replace the

SELECT @sql = @sql + "DefectType IN ('"+@DType+"') "
Line with:

SELECT @sql = @sql + "DefectType IN (Select * from tab ) "

This works if i run the stored procedure from SQL Query analyser, but when i try
to run it from my ASP page i get an error massage saying

" ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed. "

Does anyone know why the stored procedure not return any recordset when i
use the temp table in the stored procedure?

Is there any other way make my IN clause acept two (or more) in variables from the
the DType argument ?

//Start of the Stored Procedure
CREATE PROCEDURE GetData
@DType VARCHAR(40) = NULL,
@PCode VARCHAR(40) = NULL

AS

/* Local variable decarations */
DECLARE @sql VARCHAR(400)

SELECT @sql = " SELECT DefectType, COUNT(DefectId) AS var1, scan_date " +
" FROM mnt_defect_reporting " +
" WHERE "

IF @DType IS NOT NULL
BEGIN
SELECT @sql = @sql + "DefectType IN ('"+@DType+"') "
END

IF @PCode IS NOT NULL
BEGIN
SELECT @sql = @sql + " AND ProductCode IN ( '"+@PCode+"' ) "
END

SELECT @sql = @sql + " GROUP BY scan_date, DefectType "
SELECT @sql = @sql + " ORDER BY scan_date, DefectType "

EXEC (@sql)
GO

//End of the Stored Procedure"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources