Dynamic WHERE clause (SQL server 2005)

I'm in a situation where a user should be able to choose what data to
retrieve from a table. The criteria is not constant, sometime it is included
,
other times not. The problem I'm facing is how to create a dynamic WHERE
clause. I would prefere to avoid client side embedded SQL.
My first idea was to create a table valued functions that takes the criteria
and using dynamic SQL returns a table with the subset of data. The reason I
chose a TVF is so I can call it from multiple stored procedures.
The following function is accepted by SQL server, but when I run against it
I get the following error 'Only functions and extended stored procedures can
be executed from within a function.'
ALTER FUNCTION [dbo].[GetTagIds2]
(
@.TagMask NVARCHAR(50)
)
RETURNS @.ResultTable TABLE
(
TagId INT PRIMARY KEY NOT NULL,
Name NVARCHAR(100) NOT NULL
)
AS
BEGIN
DECLARE @.DynSql VARCHAR(1024);
-- this works fine
-- INSERT INTO @.ResultTable(TagId,Name)
-- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
-- WHERE IMS_Tag.Name LIKE(@.TagMask)
SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
EXEC sp_executesql @.DynSql;
RETURN
END
Any idea why this is not working?
If there are any better ways of doing this please let me know.You cannot use dynamic SQL in a function; you will need to use a different
approach.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/share_data.html
"Christopher Kimbell" <c_kimbell@.newsgroup.nospam> wrote in message
news:0BB5E543-76BC-4EC7-B038-DF0E9308D3DC@.microsoft.com...
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included,
> other times not. The problem I'm facing is how to create a dynamic WHERE
> clause. I would prefere to avoid client side embedded SQL.
> My first idea was to create a table valued functions that takes the
> criteria
> and using dynamic SQL returns a table with the subset of data. The reason
> I
> chose a TVF is so I can call it from multiple stored procedures.
> The following function is accepted by SQL server, but when I run against
> it
> I get the following error 'Only functions and extended stored procedures
> can
> be executed from within a function.'
>
> ALTER FUNCTION [dbo].[GetTagIds2]
> (
> @.TagMask NVARCHAR(50)
> )
> RETURNS @.ResultTable TABLE
> (
> TagId INT PRIMARY KEY NOT NULL,
> Name NVARCHAR(100) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.DynSql VARCHAR(1024);
> -- this works fine
> -- INSERT INTO @.ResultTable(TagId,Name)
> -- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
> -- WHERE IMS_Tag.Name LIKE(@.TagMask)
> SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
> SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
> SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
> EXEC sp_executesql @.DynSql;
> RETURN
> END
> Any idea why this is not working?
> If there are any better ways of doing this please let me know.|||Christopher Kimbell wrote:
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included, other times not. The problem I'm facing is how to create a
> dynamic WHERE clause. I would prefere to avoid client side embedded
> SQL.
See Erland's article on this topic here:
http://www.sommarskog.se/index.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks guys!
The information was very usefull.