SQL Stored Procedures and Lists of Strings

[Adam72]

Hey all. I've been pulling my hair out all day today trying to get this to
work. I can do what i want to do in a straight <cfquery>, but i'm trying to do
it in a SP for better performance etc.

Basically, I've set up Contribute templates for my client for certain holiday
destinations. My client can then insert a comma seperated list of city codes
into the TemplateParameter which then gets passed to my CF code to retreive the
latest specials.

Using a straight query such as;

SELECT * FROM WebSpecial
WHERE ProductType = 'Package'
AND CityCode IN (<cfqueryparam list="yes" value="#SQLCityCodeList#"
cfsqltype="cf_sql_varchar" separator=",">)
AND BrandID = 1
ORDER BY ItemPriority

Works fine when SQLCityCodeList = 'LON,PAR" for example. But when i try to use
in a stored procedure, it just won't work.

<cfstoredproc procedure = "DestinationSpecialsByCityCodes" dataSource="#DS#"
username="#DSUN#" password="#DSPW#" debug="yes">
<cfprocresult name = DestinationSpecials maxrows="100">
<cfprocparam type="IN" CFSQLType=CF_SQL_INTEGER value="1" dbVarName=@BrandID>
<cfprocparam type="IN" CFSQLType=CF_SQL_VARCHAR value="Package"
dbVarName=@ProductType>
<cfprocparam type="in" CFSQLType=CF_SQL_VARCHAR value="LON,PAR"
dbVarName=@CityCodes>
</cfstoredproc>

I've hard coded the list just for testing purposes. The SP is as follows;

CREATE PROCEDURE DestinationSpecialsByCityCodes
(@BrandID INT,
@CityCodes nvarchar(128),
@ProductType nvarchar(32))

AS SELECT * FROM WebSpecial
WHERE (ProductType = @ProductType)
AND (CityCode IN (@CityCodes))
AND (SaleEndDate >= GetDate()
AND TravelEndDate >= GetDate()
AND BrandID = @BrandID)
ORDER BY ItemPriority
GO

I've tried so many combinations of "AND (CityCode IN (@CityCodes))" that it's
not funny. I've even rebuilt the list that is fed to the SP so that each item
is wrapped in single quotes but to no avail.

Has anyone else come across this? And if so, did you find a work around?

Peace,

Adam

[Dan Bracuk]

You don't have any quotes around your list items. The cf function ListQualify might help you.

[Sojovi]

You have to run dynamic SQL statements, like :

EXEC('SELECT * FROM table WHERE column IN (' + @varWithList +')')

or the for SQL 2000 and newer (optimized command)

DECLARE @SQLString Nvarchar(4000)
SET @SQLString = 'SELECT * FROM table WHERE column IN (' + @varWithList +')'
EXECUTE sp_executesql @SQLString

Regards

[Adam72]

Thanks Sojovi. This seems to deal with the list of strings ok but now i've run
into something else that i can't work out.

My procedure definition is (simplified for this purpose);

CREATE PROCEDURE DSCC (@BrandID int)
AS
DECLARE @SQLString AS nvarchar(4000)
SET @SQLString = 'SELECT * FROM WebSpecial WHERE BrandID = ' + @BrandID
EXEC(@SQLString)
GO

But now, when i try to call this via CF, i get an error:

"Syntax error converting the varchar value &apos;SELECT * FROM WebSpecial
WHERE BrandID = &apos; to a column of data type int. "

If i put it back to straight inline SQL as such;

CREATE PROCEDURE DSCC (@BrandID int)
AS
SELECT * FROM WebSpecial WHERE BrandID = @BrandID
GO

It works fine and returns all records.

Any ideas?

Thanks. Adam

[Adam72]

Thanks Dan. I did try it with single quotes wrapping each element but that
didn't work either. I didn't know about the ListQualify function though,
that's a much better way to do it than i had. Cheers.

[Sojovi]

When you run dynamic SQL statements, you can concatenate only strings. In your
case, @BrandID is an integer, so if you want to concatenate the value in the
SQL statement you have convert it into a string.
You can do one of these choices :

1) SET @SQLString = 'SELECT * FROM WebSpecial WHERE BrandID = ' + STR(@BrandID)
OR
2) SET @SQLString = 'SELECT * FROM WebSpecial WHERE BrandID = ' +
CAST(@BrandID AS NVARCHAR) //OR VARCHAR OR CHAR OR NCHAR, there's no difference
in the result, but for SQL are differents data types.

Regards

[Adam72]

Thanks Sojovi! Fixed it straight up!

Thanks for your help.