Dynamic query in a function

Question

Post reply

Dynamic query in a function

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
November 21, 2010 at 10:07 pm

#99432

Hi All
I wish to keep a function in one database and plan to call ir from different database. For that, I have to pass database name as parameter but I couldn't figure a direct way to use this passed parameter. I dont want to get in complexity of making batch files or extended stored proc for this simple task. Here is what I tried:
alter function dbo.fn1(@db varchar(50))
returns int
as
begin
declare @id int
set @id=3
declare @STR varchar(100)
set @STR='select col1 from '+ @db+'.dbo.test1 where col1=2'
exec(@str)
return @id
end
Any thoughts how this could be accomplished?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Heh... yes... ask an "SQL Server DBA". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

User defined functions have some limitations. One of those limitations is that you can’t use dynamic SQL in it. This is by design and there is no way around it. You’ll have to use a different approach.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 3

sharath.chalamgari

SSCertifiable

Points: 5680

November 22, 2010 at 1:30 am

#1253614

No You can't use the dynamic sql in the functions

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 4

Alternatively, u can write proc rather than function or include string initialization in proc and call the proc from inside sql function.... dont know whether it will work for u ... as per ur business needs.......

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 5

I dont think you can call a proc from inside a function except extended procs.

So it concludes that there is no simple work around expect if I can find that "SQL Server DBA" who can answer it, right RBAR?

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

S_Kumar_S (11/22/2010)
I dont think you can call a proc from inside a function except extended procs.
So it concludes that there is no simple work around expect if I can find that "SQL Server DBA" who can answer it, right RBAR?

My point was that if you're going to tout "SQL Server DBA" in your signature line, you should already know some of the basics. 😉 It's not a title to be taken lightly. One of the basics is that you can't execute dynamic SQL in a function. The only way (without writing a CLR or Extended SProc or resorting to embedded code in a GUI) you're going to be able to pull this off is to use some form dynamic SQL in a stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 7

if you take a proc, stick it in the master database, and mark it as a system object, then it will use the datasase reference of the calling SQL.(it also neeeds to start with sp_)

so for example, if you have a procedure setup as above,that does select * from sys.objects when your db context is db_name() = SandBox ,

it selects from SandBox.sys.objects.

switch contexts, and it might select from Production.sys.objects.

--EXECUTE sp_ms_marksystemobject 'sp_find'

CREATE procedure [dbo].[sp_find2]

@findcolumn varchar(50)

AS

BEGIN

SET NOCOUNT ON

SELECT

TableFound,

ColumnFound

FROM

(

SELECT

1 AS SortOrder,

sys.objects.name AS TableFound,

'' AS ColumnFound

FROM sys.objects

WHERE sys.objects.type IN('U' ,'V')

AND sys.objects.name LIKE '%' + @findcolumn + '%'

UNION ALL

SELECT

2 AS SortOrder,

sys.objects.name AS TableFound,

sys.columns.name AS ColumnFound

FROM sys.objects

INNER JOIN sys.columns ON sys.objects.object_id=sys.columns.object_id

WHERE sys.objects.type IN('U' ,'V')

AND sys.columns.name like '%' + @findcolumn + '%'

) X

ORDER BY

SortOrder,

TableFound,

ColumnFound

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 8

Thanks For reply Lowell. I know we can use sp_ in any database context, but I had to use that function in a select statement(select clause), so this probably won't work.

Anyway, no problem. I'll put the function in all databases where i plan to call it.

But it makes to my wish list for any future sql server releases.

Thanks

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 9

Well Jeff, I didn't ask if we can execute a dynamic sql from function, I asked if there is a work around for it. i knew there is no direct way to do it.

And I am not touting anything here, this is what I am since last 5 years "sql server DBA".

Not sure what made you think that I am taking it lightly.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Lowell SSC Guru Points: 323518 More actions · Answer 10

S_Kumar_S (11/22/2010)
Thanks For reply Lowell. I know we can use sp_ in any database context, but I had to use that function in a select statement(select clause), so this probably won't work.
Anyway, no problem. I'll put the function in all databases where i plan to call it.
But it makes to my wish list for any future sql server releases.
Thanks

i guess you missed my point; as long as your function does not use dynamic sql, ie, looking for a specific table, the technique i described will work.

make your function start with fn_ or sp_, mark it as a system object, and it will work. my example was a proc, but it works with views, functions, everything.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 11

Hi Lowell

I indeed missed your point. But when I tried as you told, I still can't make it working. I was able to create a funtion that starts with fn_ in master database and marked it system object using sp_ms_marksystemobject. So this funtion is added to "system funtions" folder of master database. Also when I query sys.objects, I can see that IS_MS_SHIPPED=1 for this funtion. So i though that i can call it now from any database, but i was wrong . it didnt work. same thing works for proc but not for funtion. pls see below the code i tried. let me know if I missed something.

USE master

GO

create proc sp_p1

as

select 1

GO

EXECUTE sp_ms_marksystemobject 'sp_p1'

create function dbo.fn_alwayswork()

returns int

as

begin

declare @id int

set @id=3

return @id

end

GO

EXECUTE sp_ms_marksystemobject 'fn_alwayswork'

GO

Use mydb

go

select dbo.fn_alwayswork() --doesn't works

sp_p1--works

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 12

U could write a proc instead of writing function with the same code.... call the proc 1st.. this proc should store the output in temp table.. then create a function which will get the values from the temp table....

I could find this way only to get the values in '''select statement'''........

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

S_Kumar_S (11/22/2010)
Well Jeff, I didn't ask if we can execute a dynamic sql from function, I asked if there is a work around for it. i knew there is no direct way to do it.
And I am not touting anything here, this is what I am since last 5 years "sql server DBA".
Not sure what made you think that I am taking it lightly.

BWAA-HAA!!! Show me where you asked for a work-around, please... 😉

S_Kumar_S (11/21/2010)
Hi All
I wish to keep a function in one database and plan to call ir from different database. For that, I have to pass database name as parameter but I couldn't figure a direct way to use this passed parameter. I dont want to get in complexity of making batch files or extended stored proc for this simple task. Here is what I tried:
alter function dbo.fn1(@db varchar(50))
returns int
as
begin
declare @id int
set @id=3
declare @STR varchar(100)
set @STR='select col1 from '+ @db+'.dbo.test1 where col1=2'
exec(@str)
return @id
end
Any thoughts how this could be accomplished?

Anyhow... I believe the only work-around here is like I said... you'll need to use a stored proc with dynamic SQL. I don't believe there's a way around that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

sumit.agrawal (11/23/2010)
this proc should store the output in temp table.. then create a function which will get the values from the temp table....

Just to be clear, one can no more call on a Temp Table from a function than you can run dynamic SQL from a function. If what you're calling a "Temp Table" is actually a permanent table with "temporary" rows, then maybe but you'll really need to watch out for concurrency issues both in the proc and in the function.

I guess my question would be... what is the advantage of first having to run a proc and then select from a function? Why not just return a result set from the proc?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)