Using SQL IN command with parameters

[JimmyFo]

Hi all, I'm having some trouble using the SQL IN command when using a parameter that contains the IN data.

For example, without using parameters, I could do:
select * from assetsmaster where extension in ('doc','vsd')

But I would like to do it this way:
select * from assetsmaster where extension in (@strFile)

My stored procedure would look similar to this:
GO
CREATE PROC dbo.GetAssetList2
(
@xmlFileList varchar(1000),
@strContainsClause varchar(1000),
@strFileTypes varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @intDocHandle int

EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlFileList

SELECT DISTINCT
AssetsMaster.AssetMasterUID,
AssetsMaster.AssetFileName
FROM
AssetsMaster
WHERE
AssetsMaster.Extension IN (@strFileTypes)

What format does the parameter need to be in? I can't execute the procedure like this, as it tries to parse the parameter before executing, I think:
USE [GTGAssets]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetAssetList2]
@xmlFileList = N'<NewDataSet...Table></NewDataSet>',
@strContainsClause = N'soa',
@strFileTypes = N'VSD'

SELECT 'Return Value' = @return_value

GO

So my problem is, how does the parameter get passed so I can test it?
Thanks!
James

 

[JimmyFo]

Sorry, my bad, the @strFileTypes in the last code above should read:

@strFileTypes = N'DOC','VSD','BMP'

As it was above, it works, as it's only one item being passed in.

Thanks,
James

 

[Golom]

You should probably post in forum183 (Microsoft SQL Server Programming)

I suspect that the problem is that

[blue]AssetsMaster.Extension IN (@strFileTypes)[/blue]

is being interpreted as

AssetsMaster.Extension IN ([red]"'DOC','VSD','BMP'"[/red])

i.e a single string rather than a comma-delimited set of values.

 

[JimmyFo]

Yeah, I'm seeing now the problem is a bunch of extra single quotes are being added - I'll post over in that other forum, thanks.