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 generating a SQL statement that includes "INTO #temp"?

Hi there. I started with a simple stored procedure like
select col1, col2 into #temp from table1
select * from #temp
...Do something else here...
For some reason, I'd like to generate the first query dynamically.
declare @.sqlStmt varchar(200)
set @.sqlStmt = 'select col1, col2 into #temp from table1'
exec (@.sqlStmt)
select * from #temp
Unfortunately, it seems like #temp table is not visible anymore in the
"select * from #temp"
- Is it because exec creates a new session, and so #temp created in
the new session is not visible in the original session?
- Using global temp table ##temp may be too dangerous if two
concurrent users are running the same stored procedure. Right?
- Another workaround is to include the "select * from #temp" into the
@.sqlStmt. However, it is not a very elegant solution if the size of
stored procedure is big, but the portion that I'd like to generate
dynamically is small.
- Any other better solution?
ThanksInstead of using the SELECT...INTO statement, create a fixed temporary table
or a table variable and insert data into it. I can't really see any
legitimate reasons to use SELECT...INTO in your case (based on your post).
ML
http://milambda.blogspot.com/|||Thanks! That helps!|||>> Any other better solution? <<
First, avoid dynamic SQL. This says that you have no idea what you
wanted the procedure to do, so you have to "fake it" at the last
minute.
Next, from your narative it looks like a derived table or a VIEW is a
better answer, instead of mimicking the way we wrote scratch tapes in
1950's tape file systems.
CREATE VIEW Foobar (col1, col2)
AS
SELECT col1, col2 FROM Table1;
and then in the stored procedure, which will be compiled in the
database and not built dynamically. The VIEW will always be current.
CREATE PROCEDURE Woowoo (..)
BEGIN ..
SELECT col1, col2 FROM Foobar;
.Do something else here..
END;|||--CELKO-- (jcelko212@.earthlink.net) writes:
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
Rubbish. To see an example of how dynamic SQL can be used to solve a
common business problem, see http://www.sommarskog.se/dyn-search.html.
(And in difference to your books, this is a free resource. :-)
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|||To answer you questions:
When you exec (@.SQL), exec sp_executesql, or exec MyStoredProcedure, any
local temporary tables (#MyTable) will go out of scope and be automatically
deleted when the respective statement ends.
You can get around this by using a global temporary table (##MyTable) but as
you stated, a global temporary table is visable to all processes. Not only
does the same stored procedure executed concurrently have problems but ANY
T-SQL located in other stored procedure, dynamically submiteed by
applications, etc. that happen to create/use a global temporary table of the
same name (##MyTable) will have problems too.
SQL Server protects local temporary tables by concatenating a system
generated numeric suffix to the name. You could do the same. Seeing you're
dynamically creating the global table, you could concatenate the @.@.SPID
(process id) or something else. But then the remaining code would have to b
e
dynamic too and either exec (@.SQL) or exec master.dbo.sp_executesql (which
has more functionality)
Sounds like you need a permanent user table in your database (as suggested
by another forum member) and have a column that contains your @.@.SPID to
idenify the block of rows that belong to your process as opposed to other
concurrent executions.
Col1 - PK, int, identity, clustered (data is always added at the end of the
table data)
Col2 - SPID
Col3-n logical keys of your data
unique constraint on col2, col3, etc.
for rerunability, delete from xxx where spid = @.@.SPID so you have a fresh
workspace
if you need several workspaces, add another column after SPID that creates a
sub-block of data. you're sp can have as many sub-blocks as needed.
Just my two cents,
Joe
"domtam@.hotmail.com" wrote:

> Hi there. I started with a simple stored procedure like
> select col1, col2 into #temp from table1
> select * from #temp
> ....Do something else here...
> For some reason, I'd like to generate the first query dynamically.
> declare @.sqlStmt varchar(200)
> set @.sqlStmt = 'select col1, col2 into #temp from table1'
> exec (@.sqlStmt)
> select * from #temp
> Unfortunately, it seems like #temp table is not visible anymore in the
> "select * from #temp"
> - Is it because exec creates a new session, and so #temp created in
> the new session is not visible in the original session?
> - Using global temp table ##temp may be too dangerous if two
> concurrent users are running the same stored procedure. Right?
> - Another workaround is to include the "select * from #temp" into the
> @.sqlStmt. However, it is not a very elegant solution if the size of
> stored procedure is big, but the portion that I'd like to generate
> dynamically is small.
> - Any other better solution?
> Thanks
>|||> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
First, ignore Celko.
Because he sticks to the ANSI SQL standard he probably hasn't used these
Microsoft SQL Server features so he just blanks them and says they are
kludges etc...
Celko needs to get out and do some real development work and get some real
development experience.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134003996.366060.171490@.z14g2000cwz.googlegroups.com...
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
> Next, from your narative it looks like a derived table or a VIEW is a
> better answer, instead of mimicking the way we wrote scratch tapes in
> 1950's tape file systems.
> CREATE VIEW Foobar (col1, col2)
> AS
> SELECT col1, col2 FROM Table1;
> and then in the stored procedure, which will be compiled in the
> database and not built dynamically. The VIEW will always be current.
> CREATE PROCEDURE Woowoo (..)
> BEGIN ..
> SELECT col1, col2 FROM Foobar;
> ..Do something else here..
> END;
>

Dynamic fields in SQL

I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)The standard solution for your problem is to model the problem in the database, stroring the user defined attributes in a separate table.

Something like:
create table MY_TABLE (id int, name varchar(32) primary key(id))
create table MY_DYNAMIC_ATTRIBUTES
( id int
, attribute_name varchar(20)
, attribute_type_code char(1)
, attribute_value varchar(255)
primary key (id, attribute_name)
foreign key (id) references MY_TABLE(id)
)

The problem is usually the application reading and writing these tables.
A general report would need crosstabbing.

Specific reports, where you already know which fields are involved is easier, could even be done with a view:

create view MY_DYNAMIC_VIEW as
select
t.id
, t.name
, d1.attribute_value as address
, d2.attribute_value as city
, convert(int, d3.attribute_value) as age
from MY_TABLE t
left join MY_DYNAMIC_ATTRIBUTES d1 on d1.id = t.id and d1.attribute_name = 'address'
left join MY_DYNAMIC_ATTRIBUTES d2 on d2.id = t.id and d2.attribute_name = 'city'
left join MY_DYNAMIC_ATTRIBUTES d3 on d3.id = t.id and d3.attribute_name = 'age'|||Originally posted by plextoR
I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)

Suggestions?

Yeah, don't do it...

Just think what kind of mess you'll end up with...

Ummmm I want a varchar(8000) column...ummm I want another one...and another one...

Booooooooooooooooooooooom

What's business requirement to support, in non tech terms...