May 11, 2009 at 10:53 am
...and how do you pass a parameter from a function to a cmd batch file calling a proc??
I know that you can use the % in the batch files and "how" do you get a record-set or result-set back from the running on shell?
hmmm
🙂
Cheers,
John Esraelo
May 11, 2009 at 11:05 am
herbey zepeda (9/30/2008)
I'm not sure about this, you can't generate a newid() inside a function. It's generating an error when trying to do so, yet in a sproc you can,has anyone generated a newid() inside a finction?
thanks
Do this:
create view dbo.MyNewID (NewGUID)
as
select newid()
go
Then you use the view dbo.MyNewID in your function.
Check out my blog A Variable Length Random String to see it in action.
May 11, 2009 at 11:23 am
Lynn,
As you can see I have been reading many postings in various forums in order to find the answer I have been looking for.
This is also related to my issues with the Advantage Database data-set / result set returning..as you once or twice have posted comments...
It has been few days that I am struggling with this issue of ".. function can not call proc, function can not run dynamic sql (openquery.. such), function can not do this and that and yet my function caller MUST use "select foo1, foo2 from UDF_foo" to get what the caller needs to do..
and I am getting all sorts of confusing and pit-falls from the users and readers that are really overwhelming here..
It is a simple question and yet I have been pushed and thrown many different angles and yet back to the square one..
I personally don't know if you can call stored procedures from a "view", you can call functions, tables and other views but I don't think you can call procs.. shoot, for that matter you can not even pass a standard @ variable to a view like you would in proc..
if you do not recall my postings and issues then please let me know so I can resend you the link.
thx
Cheers,
John Esraelo
May 11, 2009 at 11:27 am
John Esraelo (5/11/2009)
Lynn,As you can see I have been reading many postings in various forums in order to find the answer I have been looking for.
This is also related to my issues with the Advantage Database data-set / result set returning..as you once or twice have posted comments...
It has been few days that I am struggling with this issue of ".. function can not call proc, function can not run dynamic sql (openquery.. such), function can not do this and that and yet my function caller MUST use "select foo1, foo2 from UDF_foo" to get what the caller needs to do..
and I am getting all sorts of confusing and pit-falls from the users and readers that are really overwhelming here..
It is a simple question and yet I have been pushed and thrown many different angles and yet back to the square one..
I personally don't know if you can call stored procedures from a "view", you can call functions, tables and other views but I don't think you can call procs.. shoot, for that matter you can not even pass a standard @ variable to a view like you would in proc..
if you do not recall my postings and issues then please let me know so I can resend you the link.
thx
I remember. If I remember correctly, I suggested creating a view that used OPENQUERY to access the the database over your linked server. this view would return the entire result set (no parameters or where clause). Then you would use that view in your UDF with the parameters and filter the data from the view.
Did you ever get that done?
May 11, 2009 at 11:33 am
I really don't know how to use an openquery in a "view".. plus I really want the Advantage database engine to filter my records in order to minimize the net traffic and not brining all those records to sql and then filtering them.. as there are quite a few callers / users will be using this set of functions not just one function.. So, the "imaging" system that is going to use these function will be jammed with unwanted traffic..
unless you have another Ace in your Sleeve then I am all ears..
so::
> how to use the openquery (please show examples if possible)
> avoid excessive traffic more what I need..
Cheers,
John Esraelo
May 11, 2009 at 11:39 am
Also, if you recall Lynn I had to use a dynamic statement since there are various parameters from within a proc.. so, I am not sure this would work in "view"..
I am doomed..
Cheers,
John Esraelo
May 11, 2009 at 11:40 am
John Esraelo (5/11/2009)
Also, if you recall Lynn I had to use a dynamic statement since there are various parameters from within a proc.. so, I am not sure this would work in "view"..I am doomed..
I'd have to see the code again.
Also, I remember you saying you weren't concerned about performance at this point, you just wanted to get it working.
May 11, 2009 at 11:43 am
...Let me ask you this::
I know that there are some xp and CLRs that can be called from a function, no? If that is the case do you think I should be able to convert my proc to a CLR type and then call that from within the func?
I don't think I have ever practiced this either and I will have to give this a shot..
Also, I was thinking about the SKT (SPID Key Table) and then immediately change my mind about that as soon as I figured out the administration portion of that.. argh..
Anyway, still hanging in here ..
FYI:: I will have to find a solution before the end of the day today.. no matter what.. otherwise I will go crazy and sure my boss my "kill" my process :))
Cheers,
John Esraelo
May 11, 2009 at 11:46 am
yes, of course, not too worry about the a little performance hit, but, we are talking about brining the entire table or even the entire result-set from a possible complex query.. we are talking about millions of records in some cases.. ouch.. buddy..
Cheers,
John Esraelo
May 11, 2009 at 11:54 am
It is still worth a try. I found the code but it is a bit confusing as it has nested subqueries. Can you write the query as if you were running directly on the target server without any parameters (just like you wanted the entire result set).
May 11, 2009 at 12:39 pm
The following code is a very simple example using existing databases on a "retired" server.
create view dbo.OpenQueryTest
as
SELECT
jobname,
jobdesc
FROM
OPENQUERY(DWSQL1, 'SELECT * FROM D11Peeps.dbo.Job')
alter function dbo.OpenQueryTestFunc (
@JobName varchar(256)
)
returns table
as
return (
select * from dbo.OpenQueryTest where jobname like '%' + @JobName + '%'
)
;
select * from dbo.OpenQueryTestFunc('Principal');
May 11, 2009 at 12:44 pm
I am to a point that need to make a decision on whether I should redesign the function-callers structure rather than trying to convert the existing functions to return the data in a table-valued function form.
There are just too many restrictions with functions and most of them make sense and those are there to protect the database against "injections" or such.
Anyway, I am going to take a look at other possible angles and just move on.. I don't think I will be able to get what I wanted.
Cheers,
John Esraelo
May 11, 2009 at 2:13 pm
It looks good, but there is no way that I can get away with multiple query structure trying to run in a view. This will require some dynamic string building process + a few "declare" statements.. and that is not possible in a view structure.. As "view" has a select statement and occasionally we can squeeze a "case" statement or "IF" statement in there.. but nothing like we are doing.. do you agree on that... please correct me as I am in need of a solutions badddddd..
The thing is that views can not call stored procedures either, and, that is a serious limitation in late-binding world.
Cheers,
John Esraelo
May 11, 2009 at 2:24 pm
You need to explain WHAT it is you are trying to accomplish. How is this being used?
May 11, 2009 at 2:42 pm
here is the 99 beers on the wall 😛
please refer to the link in below::
http://www.sqlservercentral.com/Forums/Topic711409-338-1.aspx
Cheers,
John Esraelo
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply