--CELKO--
3/15/2007 10:20:00 PM
You can Google several different kludges for this; it is a common
newbie misunderstanding. You have some other problems, too. Quit
using those silly prefixes and follow ISO-11179 rules -- example
"spGetGuests" should have been "GetGuests" , and "idGuest" should have
been "guest_id" -- even MS gave up on camel case because it does not
work (your eye jumps to the uppercase letter then backwards tot he
front of the word).
We do not use SELECT * in production code; it is just too dangerous.
I also luck the choice of VARCHAR(50) for a vague, non-scalar variable
-- this is also a magic number newbies use, not the results of
thinking and research.
>> Syntax error converting the varchar value ' + @GuestList + ' to a column of data type int <<
You do not understand that SQL is a **compiled** language and you
expected a string to be used by a BASIC-like interpreter. This is a
much more fundamental error than just no knowing SQL.
You will get a bunch of kludges to split up the string and split it
into integers. They are very slow and dangerous -- "look up SQL
Injection" -- and they do not really work becasue they do not handle
garbage characters, double commas and other things that a real parser
would handle.
Since T-SQL can handle over 1000 parameters, why not declare the
procedure with a long parameter list? You get the benefits of a real
parser so as to avoid the kludges. Try something like this:
CREATE PROCEDURE GetGuests
(@g1 INTEGER, @g2 INTEGER, .., @gn INTEGER)
SELECT guest_id, guest_name, etc.
FROM Guests
WHERE guest_id IN (@g1, @g2, .., @gn);
The default NULLs in the list will be ignored. You might consider
using an EXISTS() or constructing a working table instead, but that is
another issue.