I use the COALESCE function with success.
CREATE PROCEDURE XXX
@Opt int,
@City varchar (26) = NULL,
@item_descp varchar (35)= NULL,
@commodity_code char (4) = NULL
AS
SELECT ALL
FROM dbo.tbl_XXX
WHERE item_id like '%' + COALESCE(@item_id,item_id) + '%'
AND item_descp like '%' +COALESCE(@item_descp,item_descp) + '%'
AND commodity_code like '%' + COALESCE(@commodity_code,commodity_code) +'%'
ORDER BY CASE
WHEN @Opt = 1 THEN item_id
WHEN @Opt = 2 THEN item_descp
WHEN @Opt = 3 THEN commodity_code
END
Thanks,
Dave Bosky
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 29, 2002 8:19 AM
To: CF-Talk
Subject: RE: Dynamic queries in stored procedures
the only way to do it is to use the EXEC function.
DECLARE @tempvar varchar(200)
DECLARE @ColumnName varchar(100)
SET @ColumnName = 'MyColumn'
SET @tempvar = 'SELECT * FROM ' + @ColumnName
EXEC(@tempvar)
Just remember that with you have to make sure that you cast non-string
variable to variable.
DECLARE @tempvar varchar(200)
DECLARE @Amount int
SET @Amount = 20
SET @tempvar = 'SELECT * FROM ThisColumn WHERE Amount = ' + CAST(@Amount AS
varchar(10))
If not SQL Server thinks you're trying to add a string and a number together
and will throw an error.
Anthony Petruzzi
Webmaster
954-321-4703
http://www.sheriff.org
-----Original Message-----
From: Kola Oyedeji [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 29, 2002 4:18 AM
To: CF-Talk
Subject: Dynamic queries in stored procedures
Hi
Could anyone tell me the best way to execute dynamic queries using stored
procedures?
Thanks
Kola
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
