I want to use a stored procedure for the following...

I have a form where the user checks boxes for all the ID's they want to find. The stored pocedure should return all members where the CriteriaID is IN this list.

If I just create the SELECT statement in DW it works great. I just do:

SELECT whatever FROM table WHERE CriteriaID IN (Request("CriteriaList")

Where Criteria list looks like: 5120, 5123, 6548 etc.

Now, why doesn't this work when it's in a stored procedure?

I set up my variable: @CriteriaList VarChar(500)

Then do WHERE CriteriaID IN (@CriteriaList) but I get the following error in Query Analyzer:

Syntax error converting the varchar value '5120,5123' to a column of data type int.

What am I doing wrong? If there is just one variable in the list, like just the 5120 it works fine,but as soon as it's a string it crashes. Any Ideas?

Thanks!

Phil Grimpo
Executive Director
Inspirmedia

You need to store the whole thing in a variable and then execute it, for example:

CREATE PROCEDURE spSomethingOrOther

@CriteriaID varchar(500)

AS

SET NOCOUNT ON

DECLARE @strSQL varchar(2000)

SET @strSQL = 'SELECT whatever FROM table WHERE CriteriaID IN (' + @CriteriaID + ')'

EXECUTE(@strSQL)

SET NOCOUNT OFF
GO

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/

Nope, returns no results because it's looking at the enter string, ie '5123, 5120' instead of looking at 5123 and 5120.

Looks like I have to use Dynamic SQL, I just don't know how to do it.

Phil Grimpo
Executive Director
Inspirmedia

I can assure you it works, I've used similar SP's countless times in the past.

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/

Even if CriterID is of type INT? I have not gotten this to work. The only thing that did work (Keep in mind, this is in a STORED PROCEDURE) is found on this page: www.algonet.se/~sommar/arrays-in-sql.html#problem

Thanks for the suggeestion though.

Phil Grimpo
Executive Director
Inspirmedia

Open Query analyser, paste the follwing in a blank query and click run:

CREATE TABLE [dbo].[zzzTest] (
[RecID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[RecText] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Now open the table zzzTest in enterprise manager and type any old rubbish in the RecText field until you have a dozen or so records.


Now create a New Stored Procedure and paste the following code in it:

CREATE PROCEDURE zzzSpTest
@RecIDs varchar(200)
AS
SET NOCOUNT ON
DECLARE @strSQL varchar(1000)
SET @strSQL = 'SELECT RecID, RecText FROM zzzTest WHERE RecID IN (' + @RecIDs +')'
EXECUTE(@strSQL)
SET NOCOUNT OFF
GO


Now go back to Query Analyser, find the stored procedure zzzSpTest, open it and type 1,3,6,8 in the values box for the paramater @RecIDs and click execute.

What happens?

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/