May 7, 2009 at 3:05 pm
Hi All,
The situation is like this, We have to create the same reports for different clients. Each client data is stored in different databases. I would like to give the database name as the parameter in the stored procedure. I tried using a variable for "Use 'dbname' ". It did not work. Can anyone help me on this please.
Thanks in advance,
Ashu
May 7, 2009 at 4:26 pm
Static SQL stored procedures are compiled and bound to the objects in the same database, unless another DB is explicitly named in an object reference. You cannot parametize a database reference in static sql.
Even if you could execute the USE command in a static SQL procedure (and you cannot), it wouldn't do you much good because the bindings still remain to the original database.
Long story short: Dynamic SQL is the SOP way to do this. You'll still find some catches with USE, but it can be made to work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 1, 2009 at 10:18 am
I think there could be an alternative.
Let's create a table each in two databases
USE dbOne
GO
CREATE TABLE tblOne(...)
GO
USE dbTwo
GO
CREATE TABLE tblTwo(..)
GO
Lets create a SP (in any database) which accepts a single paramter as database name. We can create the SP in the following way to make it work for your scenario.
CREATE PROCEDURE spOne(@param1 varchar(30))
AS
BEGIN
IF (@param1 = 'dbOne')
BEGIN
SELECT * FROM dbOne.dbo.tblOne NOLOCK
END
IF (@param1 = 'dbTwo')
BEGIN
SELECT * FROM dbTwo.dbo.tblTwo NOLOCK
END
END
Does this answer your question?
This is assuming all the databases are in the same server. Otherwise, linked server is an option.
June 1, 2009 at 10:50 am
Rajram: Yes, that will work. However, you've just given yourself O(n^2) editing/maintenance overhead. For more than a few databases. this is going to be difficult.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2009 at 4:20 pm
I do this all the time. Firstly, in my report database ( which has a linked server to each data server ) I have a table set up based on a client_id. Each client id has a number of attributes such as the server (oracle or SQL) they sit on (linked server), the name of the client etc etc. Everything you need to build a query against that client. These attributes can be accessed by built in functions that return a single (scalar ) value depending on the client_id you pass to it.
Then a typical report stored proc would be :
ALTER procedure [dbo].[oracle_RS_ExpectedReceiptNewDetails]
(
@in_client_id integer
)
As
DECLARE @ora_query varchar(4000),
@sql_query varchar(4000),
@out_client nvarchar(4),
@out_linked_server_id nvarchar(30),
@out_client_name nvarchar(30)
select @out_client = dbo.getClientHostIDFromClientID(@in_client_id)
select @out_client_name = dbo.getClientNameFromClientID(@in_client_id)
select @out_linked_server_id = dbo.getOraServerIDFromClientID(@in_client_id)
Set @ora_query = ''' SELECT ER.ER_NO,
ER.ER_ID,
TRUNC(ER.CREATE_DATE) CREATE_DAY,
TRUNC(ER.EXPECTED_DATE) EXPECTED_DAY,
TRUNC(ER.RECEIPT_DATE) RECEIPT_DAY,
ER.status,
ER.ERHE_GT_01,
ERD.LINE_NO,
ERD.PRODUCT_ID,
ERD.EXPECTED_QTY,
ERD.RECEIVED_QTY
FROM EXPECTED_RECEIPT ER,
EXPECTED_RECEIPT_DETAIL ERD
WHERE ER.ER_ID = ERD.ER_ID AND
ER.status in (''''NEW'''') AND
ER.HOST_ID = ''''' + @out_client + ''''' '''
set @sql_query = 'SELECT ER_NO,
ER_ID,
RECEIPT_DAY,
CREATE_DAY,
EXPECTED_DAY,
STATUS,
ERHE_GT_01,
LINE_NO,
PRODUCT_ID,
convert(bigint, EXPECTED_QTY) EXPECTED_QTY,
convert(bigint, RECEIVED_QTY) RECEIVED_QTY
FROM OPENQUERY(' + @out_linked_server_id + ', ' + @ora_query + ') '
DECLARE @nsql nvarchar(4000)
SET @nsql = CAST( @sql_query AS nvarchar(4000))
EXEC sp_executesql @nsql
END
return(1)
June 17, 2009 at 4:51 pm
A great example of how to do it with Dynamic SQL, Paul.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply