Dynamic query in a function

Greetings:
I am trying to write a view that includes a column derived from a function
call.
The function is written thus:
CREATE FUNCTION dbo.fnc_CapArrayValue
(@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
RETURNS REAL
AS
BEGIN
Declare @.Result REAL
SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id =
@.measurementid) and (tracking_id = @.trackingid))
RETURN @.Result
END
GO
What am I doing wrong here? When I run the view this function resides in, I
get a timeout error.Does the same query executed against the view return results in a resonable
time?
If not then posting the view definition and the definitions and indexes of
the underlying tables would give a few clues.
Is this an extract of somethimg much more complicated - if not I cannot see
much reason for using the function - unless you need it in a constraint or
similar.
Re-reading your post - is the function being used in a view definition, and
itself retriving data from a view (assuming vw_CapArrayData is a view?)
BTW you may get a deluge saying prefixing vw on the front of view names is a
bad idea, which I happen to think it is - be warned.
I suspect that the combination of a view based on a function, based on a
view has caused the optimiser to get a tad and produce a poor
execution plan.
Mike John
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:y2ikg.3677$Oh1.923@.news01.roc.ny...
> Greetings:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL
> AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id
> =
> @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
> What am I doing wrong here? When I run the view this function resides in,
> I
> get a timeout error.
>|||You cannot use Dynamic SQL inside functions. The workaround is to use a CASE
expression like:
SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
WHEN 'value_2' THEN col_2
..
WHEN 'value_n' THEN col_n
END
FROM dbo.vw_CapArrayData
WHERE measurement_id = @.m_id
AND tracking_id = @.t_id ) ;
Alternatively you can create a procedure to get this information, however
you'll have to make careful considerations for security and performance.
Anith|||Thomas Mick (nospam@.nospam.com) writes:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData
> where (measurement_id = @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
This function appears funny to me. It will return the value in @.JET if
there is a matching row, else it will return NULL. This could be expressed
more clearly.
Avoid scalar UDFs that performs data access. This can cause quite severe
performance degradation, in queries that calls the UDF, as the query
more or less gets converted to a cursor behind the scenes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I am trying to write a view that includes a column derived from a functio
n call. <<
Take a freshman course in BASIC Software Engineering. Try to be sober
on the days that that coupling and cohesion are discussed. It will
keep you from public embarassment in the future. You might also want
to Google over to Larry O'Brien's current column this month for a
reminder.
You even even put prefixes like "fnc_" on names! You never read
ISO-11179. Measurement_id? There is no name possible in a data model.
So very wrong! BIGINTs because you have more data than there are atoms
in the Universe!!
Almost everything. Forget the crashes; your whole mindset is wrong.
Post specs and we can try to get you something that is correct.
--CELKO--|||Are you always this condescending? I am relatively new to the world of
databases, not a seasoned veteran; hence the reason I am asking a question.
I appreciate what you have inputted, but not the attitude used to deliver
it.
BTW, I am in no way embarrassed as I am working on curing my ignorance and
appreciate all the help I can get.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
> Take a freshman course in BASIC Software Engineering. Try to be sober
> on the days that that coupling and cohesion are discussed. It will
> keep you from public embarassment in the future. You might also want
> to Google over to Larry O'Brien's current column this month for a
> reminder.
> You even even put prefixes like "fnc_" on names! You never read
> ISO-11179. Measurement_id? There is no name possible in a data model.
> So very wrong! BIGINTs because you have more data than there are atoms
> in the Universe!!
>
> Almost everything. Forget the crashes; your whole mindset is wrong.
> Post specs and we can try to get you something that is correct.
> --CELKO--
>|||Thank you for your response, Mike,
Allow me to better describe what I am attempting to do without divulging
confidential information.
I have two views that I am joining together with related data. On the one
side is yield data related to particular failures. On the other side is data
from a related process generated during the manufacturing processes prior to
generating the yield data; this data is related by three of their fields.
The two views link together predictably and the data returned is valid.
However, I must also get a related value from an array table to insert into
the view I am creating; hence the reason I was exploring the use of a
function.
I hope this is more understandable, if not I'm screwed; this problem has me
at my wits end right now.
Thomas Mick
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uyz7m8KkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Does the same query executed against the view return results in a
> resonable time?
> If not then posting the view definition and the definitions and indexes of
> the underlying tables would give a few clues.
> Is this an extract of somethimg much more complicated - if not I cannot
> see much reason for using the function - unless you need it in a
> constraint or similar.
> Re-reading your post - is the function being used in a view definition,
> and itself retriving data from a view (assuming vw_CapArrayData is a
> view?) BTW you may get a deluge saying prefixing vw on the front of view
> names is a bad idea, which I happen to think it is - be warned.
> I suspect that the combination of a view based on a function, based on a
> view has caused the optimiser to get a tad and produce a poor
> execution plan.
> Mike John
> "Thomas Mick" <nospam@.nospam.com> wrote in message
> news:y2ikg.3677$Oh1.923@.news01.roc.ny...
>|||Erland,
Thank you for your constructive response. I truly appreciate it and will
learn accordingly.
Thomas Mick
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E429816C9FYazorman@.127.0.0.1...
> Thomas Mick (nospam@.nospam.com) writes:
> This function appears funny to me. It will return the value in @.JET if
> there is a matching row, else it will return NULL. This could be expressed
> more clearly.
> Avoid scalar UDFs that performs data access. This can cause quite severe
> performance degradation, in queries that calls the UDF, as the query
> more or less gets converted to a cursor behind the scenes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just ignore him Thomas, most be do - he's an ignorant, condesending idiot
who has a high opinion of himself, oh and watch out for his stuck in the
80's model thinking.
Don't let this single fool put you off posting here again!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:Hdnkg.3705$Oh1.2508@.news01.roc.ny...
> Are you always this condescending? I am relatively new to the world of
> databases, not a seasoned veteran; hence the reason I am asking a
> question. I appreciate what you have inputted, but not the attitude used
> to deliver it.
> BTW, I am in no way embarrassed as I am working on curing my ignorance and
> appreciate all the help I can get.
> Thomas
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
>|||I have a feeling I already know the answer to this, but I have to ask
anyway; can a stored procedure be run from within a function? Or
alternatively, can I run a stored procedure from within a view?
You see, I need to insert this value into a column of a view I am creating
for an engineer; a stored procedure would most likely be the best method of
retrieving what I need.
Thomas Mick
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eWGUFjMkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> You cannot use Dynamic SQL inside functions. The workaround is to use a
> CASE expression like:
> SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
> WHEN 'value_2' THEN col_2
> ...
> WHEN 'value_n' THEN col_n
> END
> FROM dbo.vw_CapArrayData
> WHERE measurement_id = @.m_id
> AND tracking_id = @.t_id ) ;
> Alternatively you can create a procedure to get this information, however
> you'll have to make careful considerations for security and performance.
> --
> Anith
>

dynamic mdx

Hello i'm new user,

i have a calculated member

the function LINREGSLOPE juste calculate the slope ( ie: a) of the equation Y= a *X+b

calculated member Name= Gain

LINREGSLOPE([TIME].[Month].&[2006-02-01T00:00:00]:[TIME].[Month].&[2006-06-01T00:00:00],[Mesures].[NumofCustomer],[TIME].[Month], [TIME].[Rank])

i write

the formula do the job veru well. What i want to do now is to

transform the expression to be dynamic with two parameter

I hope this will help.

I seen in this forum that this is possble. how to extract

this information. How can i concat the parametre!month.value with the

string ?

i see idea of solution here but i dont know how to exploit it

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=443431&SiteID=1

how can i do this please help

thanks

hi,

If I am not mistaken, I think you want to restrict the query based on static value instead of dynamic parameters. Right? If that is the case you can just include a where condition to your query like this:

where [Dimension].[Heirarchy].&[Member1]:[Dimension].[Heirarchy].&[Member2]

Note: Dimesion is your dimension name

Heirarchy is your heirachy name.

Member1 is the start date

Member 2 is the end date

I hope this will help.

Sincerely,

Amde

|||thanks amde,

no it's the opposite from static de dynamique.

if the user change the parametre the query will change too.i want to write my expresion with a parameter.

i want somethink like this:

linregslope([Time].[month].&"param1":[Time].[month].&"param2",[measures].[numberCustomer],[Time].[Month].[MonthNumber]).

if you have others questions feel free to ask me.

visite the link in my first post.

I hope this will help.

any idea is welcome

casagrandi

|||

ok thanks

i found the solution

this isi the code:

WITH

set [RangeMonth] as LastPeriods(STRTOMEMBER (@.FromTIMEMonth,CONSTRAINED),STRTOMEMBER (@.ToTIMEMonth,CONSTRAINED))

MEMBER [Measures].[GAIN PER MONTH] AS 'LINREGSLOPE([RangeMonth],[Measures].[mymeasure],Rank([TIME].[Month], [TIME].[Month].Members))'

,format="percent"

SELECT NON EMPTY {[Measures].[GAIN PER MONTH],[Measures] ..........

sql

Dynamic Filtering, a further question

Am I correct to assume that the Host_Name function, when used in a filter
expression at the publisher will return the subsriber host name?
What does SUSER_SNAME return:
The name of the current logged on user at the subscriber?
The name of the SQL server run account ath the subsciber?
Or the SQLServerAgent run account at the subscriber?
Tony Toker
Data Identic Ltd.
That depends. If it is a pull subscription, which means the agent is running
on the subscriber it will return the name of the subscriber. If it is a push
subscription, which means the agent is running on the publisher it will
return the name of the publisher.
Unless of course we are talking merge replication and you override this
behavior by using the HostName parameter in the merge agent commands
section. (right click on your merge agent, select agent propertes, Steps,
run agent, click edit, and in the command section look or add a -HostName
and then enter the name you wish to be replaced by the HostName parameter
here.
SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xxxx@.xxxx.com> wrote in message
news:celd7r$n8l$1$830fa7b3@.news.demon.co.uk...
> Am I correct to assume that the Host_Name function, when used in a filter
> expression at the publisher will return the subsriber host name?
> What does SUSER_SNAME return:
> The name of the current logged on user at the subscriber?
> The name of the SQL server run account ath the subsciber?
> Or the SQLServerAgent run account at the subscriber?
> Tony Toker
> Data Identic Ltd.
>
|||<mini-quibble>
"SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber."
The Merge Agent typically runs under SQL Server Agent at the Distributor for
push subscriptions (not the publisher) or at the Subscriber for pull
subscriptions.
</mini-quibble>
Regards,
Paul Ibison
|||That is correct. Thanks for the correction.
Keep in mind that for most topologies - the publisher will be on the same
server as the distributor.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uM%23AusJeEHA.1000@.TK2MSFTNGP12.phx.gbl...
> <mini-quibble>
> "SUser_SName will resolve to account that the SQL Server agent runs under.
> For a push, its the SQL Server agent account on the publisher, for a pull
> the SQL Server agent account on the Subscriber."
> The Merge Agent typically runs under SQL Server Agent at the Distributor
for
> push subscriptions (not the publisher) or at the Subscriber for pull
> subscriptions.
> </mini-quibble>
> Regards,
> Paul Ibison
>

Dynamic Execute in Function?

Is it possible to dynamically execute SQL in a Function?
Set @.selectit = ' Select Startdate from [dbo].[' + @.tablename
+ '] Where testcol=113'
Exec (@.selectit )
Is what I want to do but Query Analyzer won't let me do it.
SQL Server 2000.
Thanks.On Fri, 30 Sep 2005 16:54:52 -0400, xenophon wrote:

>Is it possible to dynamically execute SQL in a Function?
Hi xenophon,
No, that's not possible. See "User-Defined Functions" in Books Online.
Why would you want to do this anyway? Why don't you know the name of
your table?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||WHO CARES IF IT POSSIBLE?
No programmer who has has a **basic** software engineering course would
use dynamic SQL in the middle of a procedure. Remember the terms
"coupling " and "cohesion"'|||Hello,
You are unable to use it in Function. However, you can use it in stored
procedure. The sample code for your reference:
create proc aaa (@.tablename varchar(10) )
as
begin
declare @.sec varchar(300)
set @.sec= ' Select customerID from ' + @.tablename + ' Where
customerID="ALFKI"'
exec (@.sec)
end
exec aaa 'customers'
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic dimenssion security

I am trying to pass the username to a vb function in the allowed members set of a dimenssion. For some reason it is not getting to the function. All of the other parameters are being received as I am logging them in the event log.

Any ideas on why it looks like the username is not being picked up at all would be great.

Thanks in advance.

Chris

Chris,

I'm assuming you are using the UserName() function? Can you post the MDX expression you have in the allowed members set definition?

You might try creating a test calculated member using some or all of the MDX expression you are using for that parameter just to make sure it is resolving in the manner you think it should...

Dave F.

|||

Thanks for the reply Dave. In the allowed members set I am doing a call to a vb strored procedure which takes in as parameter a username and level in the hierarchy it is providing security for. The stored procedure then executes a sql query on a view which returns an mdx string providing a set of members for a specific level.

If you execute the stored procedure from a query in management studio for example

SecurtiySP.GetOrgMembers(USERNAME, 7)

This then does return correctly. But when reporting services executes a query, it doesnt seem to pass the users username at all. I believe it may be to do with the account RS is running as and that it isnt part of AD and cant impersonate other accounts, so I will have a look next tuesday and see what I can find.

|||

Yes, the problem could be with having Reporting Services in the mix (or any other mid-tier application). If Reporting Services is running on a separate server, you would have to set up Kerberos in order for Reporting Services to impersonate the authenticated users in relation to Analysis Services. Also the server running Reporting Services will have to be trusted for delegation. More on this here:

http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx

|||

Hi Michael,

Thanks for the reply. I have since found the problem. It seems accoring to this article http://support.microsoft.com/kb/913667 that we needed to run sp1 and the hotfixes. It is now passing the correct username :)

Chris

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
Jamie
Hi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie
|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pas
s
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
--
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> > Is there a method to send getdate() to a function without the function
> > returning an error: (functions is called from a view)
> > Incorrect syntax near '('
> >
> > example:
> > Select * from myfunction(getdate())
> >
> > create function myfunction (@.Today datetime)
> > returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> > Begin
> > --do stuff
> > return
> > End
> >
> >
> >
> > --
> > Regards,
> > Jamie
>
>

Dynamic Date Function for @date

Greetings...
I have a report that is being deployed w/ the parameters @.sdate (Start Date)
and @.edate (End Date). My goal is to setup a subscription to a few folks w/
the default dates to be the previous day. I tried using the =datetime.today
-1 but recieved errors during compile. If someone has a solution to this
please reply, it will be most helpful.
Thanks,
--
Ben Sullins
Our Vacation Store
http://ovstravelfolio.comFor others in the same boat I have found the solution to this...
To set the default value for report parameters using an expression you must
first be in 'layout' view in report designer. From there you will need to
select 'Report' from the properties window. Then click on the elipse (...)
next to 'Report Parameters'. From there you will see the Report Parameters
Dialogue box, this is where you set the default value for the parameters. You
can use other query results or functions like this,
=datetime.today.adddays(-1).tostring("mm/dd/yy").
Hope this helps!
"Ben Sullins" wrote:
> Greetings...
> I have a report that is being deployed w/ the parameters @.sdate (Start Date)
> and @.edate (End Date). My goal is to setup a subscription to a few folks w/
> the default dates to be the previous day. I tried using the =datetime.today
> -1 but recieved errors during compile. If someone has a solution to this
> please reply, it will be most helpful.
> Thanks,
> --
> Ben Sullins
> Our Vacation Store
> http://ovstravelfolio.com

Dynamic CLR Table-Valued Function

Is it possible to create a CLR Table-Valued Function in SQL 2005 in which th
e
fields can be defined at runtime? In other words, table-valued function must
define a table definition in the [SqlFunction] attribute, which makes the
definition of the columns you are returning from the function static. How ca
n
I overcome this limitation and at runtime define the columns to return?
What I want to achieve is something similar to the in-line Table-Value
Function available in SQL 2000 and SQL 2005, but now using the CLR."examnotes" <Fernando@.discussions.microsoft.com> wrote in
news:89B31AF7-70BD-4CF4-B319-D25FEE310058@.microsoft.com:

> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime?
[snip]

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
Not as far as I know.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels,
Thanks for the answer. By the way, congratulations on your work. I have your
book since it was released and it has been a great reference for me.
Fernando
"Niels Berglund" wrote:

> "examnotes" <Fernando@.discussions.microsoft.com> wrote in
> news:89B31AF7-70BD-4CF4-B319-D25FEE310058@.microsoft.com:
>
> [snip]
>
> Not as far as I know.
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||Hello Fernando,

> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime?
Yes and I don't think so.
I don't think so: The typical TVF pattern isn't going to accomodate that
-- Understand that SQL Server really expects the return value of any CLR
function to come form System.Data.SqlTypes. At least that's what I've figure
d
out so far. There's not SqlTable type in that namespace, so I don't see how
you're doing to do this any conventional way.
My work-around would be have the TVF return a single column of XML.

> In other words,
> table-valued function must define a table definition in the
> [SqlFunction] attribute, which makes the definition of the columns you
> are returning from the function static.
This is true if and only if you are using Visual Studio to deploy the functi
on
because Visual Studio consumes that attribute to write the CREATE FUNCTION
statement. Fire up profiler and what the deployment process, you'll see it.

> How can I overcome this
> limitation and at runtime define the columns to return?
Write a code generator that generates the TVF, because -- short of that --
you're not going to get around the fact that a T-SQL TVF requires a static
table definition. Or return XML.

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
I don't see any easy ways to do that, at least today.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent,
Thanks for the great ideas to workaround this issue. The code generator
probably will work better for my case as I intend to join results from
multiple Table-Valued Functions.
Thanks!,
Fernando
"Kent Tegels" wrote:

> Hello Fernando,
>
> Yes and I don't think so.
> I don't think so: The typical TVF pattern isn't going to accomodate that
> -- Understand that SQL Server really expects the return value of any CLR
> function to come form System.Data.SqlTypes. At least that's what I've figu
red
> out so far. There's not SqlTable type in that namespace, so I don't see ho
w
> you're doing to do this any conventional way.
> My work-around would be have the TVF return a single column of XML.
>
> This is true if and only if you are using Visual Studio to deploy the func
tion
> because Visual Studio consumes that attribute to write the CREATE FUNCTION
> statement. Fire up profiler and what the deployment process, you'll see it
.
>
> Write a code generator that generates the TVF, because -- short of that --
> you're not going to get around the fact that a T-SQL TVF requires a static
> table definition. Or return XML.
>
> I don't see any easy ways to do that, at least today.
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Kent Tegels <ktegels@.develop.com> wrote in
news:b87ad74f0cb8c7ba687dccef80@.news.microsoft.com:

> Hello Fernando,
> This is true if and only if you are using Visual Studio to deploy the
> function because Visual Studio consumes that attribute to write the
> CREATE FUNCTION statement. Fire up profiler and what the deployment
> process, you'll see it.
[snip]

> Write a code generator that generates the TVF, because -- short of
> that -- you're not going to get around the fact that a T-SQL TVF
> requires a static table definition. Or return XML.
The issue is not so much the table definition in itself, but the
FillRowMethod method, which has to have the same output param definition as
the table definition.
Niels|||"examnotes" <Fernando@.discussions.microsoft.com> wrote in
news:B7466A77-02A2-42DC-8583-8AC96C49D670@.microsoft.com:

> Niels,
> Thanks for the answer. By the way, congratulations on your work. I
> have your book since it was released and it has been a great reference
> for me.
>
Thanks so much!
Niels|||Fernando (Fernando@.discussions.microsoft.com) writes:
> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime? In other words, table-valued
> function must define a table definition in the [SqlFunction] attribute,
> which makes the definition of the columns you are returning from the
> function static. How can I overcome this limitation and at runtime
> define the columns to return?
You really should not be able to. A table-valued function is something
that can be used in a query:
SELECT a.col1, a.col2, b.col3, ...
FROM tbl t
JOIN tblfunction (@.par1, @.par2) f ON t.keycol = f.somecol
A query is static, and all parts in it must be known when the query
is compiled. SQL Server has deferred name resolution, so you might be
able to create the procedure if an object is missing. But if the query
does not compile, when it's time to execute it you get an error.
After all, with your dynamic TVF, what is going to happen if it does
not return somecol in its result set?

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
You cannot do inline-functions with the CLR. CLR TVF are akin to
multi-statement functions in T-SQL. An inline function is in fact
not a function at all; it is a macro which is expanded into the query,
and the optimizer builds the query plan for the expanded query. It
goes without saying that is not going to happen with a CLR function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."

Dyamic view/function based on table data (?)

Hey,

First, sorry if this post appear twice, because, I can not find my post
recently send, trying to post it once again.
I'm out of ideas, so, I thought, will search help here again :(

I'm trying to prepare a view for ext. app. This is in normal cases very
easy, but what if the view structure should be dynamic?!

Here is my point (I will siplify the examples).

I have a table:

create table t_data (
id bigint identity (1,1) not null,
valvarchar(10) not null,
data varchar(100) not null
constraint [PK_t_data] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )

go

insert into t_data (val, data) values
('1111111111','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('2222222222','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz12345abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('3333333333','12345abcdefghijklmnoprstuvwxyz12345 67890abcdefghijklmnoprstuvwxyz1234567890abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('4444444444','67890abcdefghijklmnoprstuvwxyz12345 67890abcdefg12345hijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('5555555555','1230abcdefghijklmnoprst12345uvwxyz1 234567890abcdefghijklmnoprstuvwxyz67890abcdefghijk lmnoprstuvwxyz')
go

create table t_dataVal (
id bigint identity (1,1) not null,
valvarchar(10) not null,
fill varchar(4) not null
constraint [PK_t_dataVal] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )

go

insert into t_dataVal (val, fill) values ('1111111111','AAAA')
insert into t_dataVal (val, fill) values ('2222222222','KKKK')
insert into t_dataVal (val, fill) values ('3333333333','DDDD')
insert into t_dataVal (val, fill) values ('4444444444','ZZZZ')
insert into t_dataVal (val, fill) values ('5555555555','CCCC')
go

create table t_conf (
id bigint identity (1,1) not null,
start int not null,
length int not null,
description varchar(20) not null,
constraint [PK_t_conf] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )
go

insert into t_conf (start, length, description) values (1,10,'value_1')
insert into t_conf (start, length, description) values (11,3,'value_2')
insert into t_conf (start, length, description) values
(55,15,'value_3')
insert into t_conf (start, length, description) values (33,2,'value_4')
insert into t_conf (start, length, description) values (88,1,'value_5')
insert into t_conf (start, length, description) values (56,7,'value_6')
go

Now here is the issue:
table t_conf contain data, which can be modified by user. The user is
seting the appropriate values.
Now, there should be a view, which returns:
- as headers (collumn names) this what is defined in description column
of t_conf (for example: value_1, value_2 ... value_6)
- as values, substrings of all data from t_data, cutted with start and
length values for appropriate decription from t_conf.
- first two columns of view, should be column val and fill of t_dataVal
table

So the effect should be like this:
valfillvalue_1value_2value_3value_4value_5value6
1111111111AAAA1234567890abc...
2222222222KKKK1234567890abc...
3333333333DDDD12345abcdefgh...
4444444444ZZZZ67890abcdefgh...
5555555555CCCC1230abcdefghi...

of course, for all other value_x should be the appropriate substrings
shown.

Sounds simple, hm?
Well, I'm trying to do this, since yesterday evening, and can not :(

In real life, the call of view/function might happend a lot.
The table t_data might have around 4000 records, but the data string is
longer (around 3000 characters).

Application, might acess a udf, which returns table, and I was focusing
in that.
Was trying, to create local temp table in function, to insert values,
using cursor over t_conf.
Unfortunately, everything what I get, is just a vertical representation
of the data, and I need it horizontal :(
The other problem in function is, that I can not use exec() (wll known)
so I can not even create a table,
dynamicly, using as column names description value from table t_conf,
and as size of field length from this table.

Sorry, that the description is maybe not exactly for my problem, but
this is because I'm not even sure, which way to use :(

any help will be appreciated!

Thank You - MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

Now here is the issue:
table t_conf contain data, which can be modified by user. The user is
seting the appropriate values.
Now, there should be a view, which returns:
- as headers (collumn names) this what is defined in description column
of t_conf (for example: value_1, value_2 ... value_6)
- as values, substrings of all data from t_data, cutted with start and
length values for appropriate decription from t_conf.
- first two columns of view, should be column val and fill of t_dataVal
table

A view, just like a table, has a fixed number of columns, so it can't
be dynamic. Theoretically, you could have a trigger on the configuration
table that recreates the view each time some adds or removes a value.
I'm not really sure that I like this alternative a lot.

Overall, the entire design seems unsound to me. What would the point be
to store all data in a single column, and then use another table to
specify where the value boundaries are. I could guess that this is an
import table, but even in that case I would question if the field
definition should be apply before the data makes it to SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx