June 1, 2006 at 3:42 pm
Hello everyone,
I'm fairly new to SQL Server. I created a function that I wanted to revise so that I can pass the database I want to use as a parameter. The function looks like this:
ALTER FUNCTION training_fn_ARBProtestReasons (@input_DB_Tbl varchar(100), @input_year int, @input_prop_id int, @input_case_id int )
RETURNS varchar(100)
AS
BEGIN
declare @output_reasons varchar(100)
declare @reason varchar(10)
declare @sSQL varchar(2000)
set @output_reasons = ''
set @sSQL = ''
DECLARE REASONS CURSOR
FOR select reason_cd
-- from pacs_training.dbo._arb_protest_reason
from @input_DB_Tbl
where prop_val_yr = @input_year and prop_id = @input_prop_id
and case_id = @input_case_id
OPEN REASONS
FETCH NEXT FROM REASONS into @reason
while (@@FETCH_STATUS = 0)
begin
if (@output_reasons = '')
begin
select @output_reasons = rtrim(@reason)
end
else
begin
select @output_reasons = @output_reasons + ', ' + rtrim(@reason)
end
FETCH NEXT FROM REASONS into @reason
end
CLOSE REASONS
DEALLOCATE REASONS
RETURN (@output_reasons)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*******************************************************************************
When I tried to update the function I get the following error:
Server: Msg 137, Level 15, State 2, Procedure fn_ARBProtestReasons, Line 17
Must declare the variable '@input_DB'.
*******************************************************************************
I wanted to use it later in a query like this:
select prop_id, cad.dbo.fn_ARBProtestReasons('pacs_training.dbo._arb_protest_reason',prop_val_yr,prop_id,case_id)
from _arb_protest
where prop_id = 30661
Any help would be greatly appreciated.
Joe O.
June 1, 2006 at 5:43 pm
You would have to use dynamic SQL to specify which DB to use. And for good reasons, you can't use dynamic SQL in a function.
How many databases do you have with this table in? If it's just different test copies etc, then you should copy the function without DB qualifier into each db you want to use, then call the local copy each time. If you have multiple databases containing the same type of data which all needs to be queried together, the question arises - why are they in different databases, and for that matter, different tables.
The short answer is, you can't do it, and probably shouldn't even if you could.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 2, 2006 at 7:57 am
Hello Tim,
Thanks for your reply. It is just for test purposes. We have a copy of the same tables on a test database and just wanted to run some queries. But mainly, it was curiousity. I wanted to know if it was possible for future use in upcoming projects.
I won't battle with this one and take your advice. Thanks again.
JLO
June 2, 2006 at 12:17 pm
Actually, an afterthought. If you ar enot using this for anything important, you could have a look at the undocumented system proc sp_MSforeachdb, which I think uses dynamic SQL. The warning still stands though.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 3, 2006 at 9:18 am
If you have a limited number a DBs and you know the names in advance you could simply put an if statement in your cursor. |This would allow you to do what you wanted.
-------------------------------
if @db = 'test'
select * from test.table
if @db = 'live'
select * from live.table
--------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply