April 17, 2011 at 2:56 am
Hey all, pretty new at this SQL Business but here goes:
trying to write a dinamic table function which goes something like this:
create function [dbo].[GetRangeMatches](@From int,@To int, @CheckField nvarchar(50), @CheckTable nvarchar(50))
returns table
as
return(
select distinct CheckTable.MyField
from @CheckTable as CheckTable
where ((case when isnull(@from,'') = '' then 1 else @CheckField end) >= (case when @from ='' then 1 else @From end) and @CheckField<=@To)
or ((case when isnull(@To = '')='' then 1 else @CheckField end) <= (case when @To ='' then 1 else @To end) and @CheckField>=@From)
)
the aim is to insert any table, field and range to check if exists in the table. it needs to be dynamic so that i can set it for any table and field and get a result.
the above comes up with an error message saying :"Must declare the table variable "@CheckTable"."
would be ever so grateful for any help.
thx
April 17, 2011 at 6:23 am
First a comment, generic procedures that do everything, everywhere, automatically, are notorioiusly problematic. TSQL just isn't conducive to that approach.
The problem you're hitting is that you can't refer to tables by a variable name. They have to be named in order for the Algebrizer in the Optimizer to resolve them. So, to do a dynamic query like this you need to build and execute a string, something like:
DECLARE @MySQL nvarchar(max) = 'INSERT INTO ' + @MyTableVariable + '...etc.'
While you can then use EXEC @MySQL to run it, you'll be better off learning about sp_executesql so that you can build parameters into the string and execute it that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2011 at 7:09 am
Grant Fritchey (4/17/2011)
While you can then use EXEC @MySQL to run it, you'll be better off learning about sp_executesql so that you can build parameters into the string and execute it that way.
But first read up on SQL injection, because object names can't be included as parameters. Also note that dynamic SQL can't be used in functions.
And as Grant said, this is a bad approach to the problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2011 at 7:15 am
Thank you very much guys, much appreciated. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply