It's just not legal in Microsoft SQL (assuming you mean Microsoft SQL
Server); the SQL statement is compiled when the SP is created, and can't be
dynamic in that way. You have two options:
1) rewrite in a way that can be compiled.
SELECT 'name not given', *
FROM Directory
WHERE CountryID = @country
AND ( (@type IS NULL)
Or (DirectType = @type)
)
2) create the SQL statement as a string and use Exec
declare @mysql varchar(300)
select @mysql = ' select ...'
exec @mysql
-----Original Message-----
From: Paul Johnston [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 15, 2000 9:08 AM
To: Cf-Talk
Subject: SQL Server Stored Procedure problem
Someone tell me why this isn't working. It's annoying me now:
SELECT 'name not given', *
FROM Directory
WHERE CountryID = @country
IF ( @type IS NULL )
BEGIN
AND DirectType = @type
END
Only if a value is passed into the SP by cold fusion do I want the AND
clause to go.
Thanks
Paul
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
