A good one! - SQL Server Forums

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)

A good one!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-21 : 18:34:18

Brian Glodde writes "Ok, here's your stumper:

Overview:
The details for any given piece of real estate can have any undertermined number of "amenities". I store the major headings in one table and the items under another. As insane as it might seem, the storage of the amenity configuration is done in a comma delimited string: amenityType|amenityItem, amenityType|amenityItem, ... so the data resembles this "7|24,7|25,7|26" and so on.

Here's the simple table structure (all this may be slightly mad, my excuse is I am a programmer, not a DB admin)

tblAmenities
- amenities_id INT 4 (PRIMARY KEY)
- amenities_name varchar 255
- amenities_type INT 4

tblAmentityTypes
- amenityType_id INT 4 (PRIMARY KEY)
- amenityType_name varchar 255

Question: I want to select everything where the id = "7" and the items are "IN" a comma delimited string "24,25,26".

Here's what it looks like from the Query Analyzer:
SELECT * FROM tblAmenities
INNER JOIN tblAmenityTypes
ON tblAmenities.amenities_type = tblAmenityTypes.amenitytype_id
WHERE tblAmenities.amenities_type = 7
AND tblAmenities.amenities_id IN (24,25,26)

Works exactly as it should. Sooooo...naturally, we want speed so let's turn this into a stored proc!!!!

Problem: The elements we want to match on are stored as the primary key INT value!

CREATE PROCEDURE decodeAmenities
@AID INT,
@CONFIG nvarchar(255)
AS
SELECT * FROM tblAmenities
INNER JOIN tblAmenityTypes
ON tblAmenities.amenities_type = tblAmenityTypes.amenitytype_id
WHERE tblAmenities.amenities_type = @AID
AND tblAmenities.amenities_id IN (@CONFIG)
GO

This does not work because it cannot convert the nvarchar argument to INTs for comparison!

Your SQL-saavy brain cycles are greatly appreciated.
I don't want messy SQL in my ASP!! HELP!!

Oh, also, I've already tried to "CONVERT(varchar(10), tblAmenities.amenities_id) IN (@CONFIG)" with no good results."

bglodde
Starting Member

25 Posts

Posted - 2002-01-21 : 22:31:03

With all due respect, you should read a little further into this question. You cannot "EXEC" another stored proc in this case as is mentioned in the FAQ. The datatypes are not matching. This is the real stumper part. If you can solve this please show me how!!! TIA.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 22:39:52

declare @sql varchar(2000)
select @sql =
'SELECT * FROM tblAmenities
INNER JOIN tblAmenityTypes
ON tblAmenities.amenities_type = tblAmenityTypes.amenitytype_id
WHERE tblAmenities.amenities_type = ' + convert(varchar(10),@AID)
AND tblAmenities.amenities_id IN (' + @CONFIG + ')'

exec (@sql)

I assume that was what the link points to.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-21 : 22:43:29

CREATE PROCEDURE decodeAmenities @AID INT, @CONFIG varchar(255) AS
DECLARE @sql varchar(8000)
SELECT @sql ='SELECT * FROM tblAmenities INNER JOIN tblAmenityTypes
ON tblAmenities.amenities_type = tblAmenityTypes.amenitytype_id
WHERE tblAmenities.amenities_type = ^ AND tblAmenities.amenities_id IN (~)'
SELECT @sql=Replace(@sql, '^', Ltrim( Str(@AID, 10, 0) ) )
SELECT @sql=Replace(@sql, '~', @CONFIG)
EXEC (@sql)

This should work fine. I changed @config to varchar; there's no need to use nvarchar at all since it's a CSV of numbers. The ^ and ~ characters are simply placeholders, and the Replace functions pop the appropriate variable values in the proper place(s).

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 22:43:31

You could also

SELECT * FROM tblAmenities
INNER JOIN tblAmenityTypes
ON tblAmenities.amenities_type = tblAmenityTypes.amenitytype_id
WHERE tblAmenities.amenities_type = @AID
AND ',' + @CONFIG + ',' like '%,' + tblAmenities.amenities_id + ',%'

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-21 : 22:45:02

That's gotta be a record, sniped by 2 seconds!