Can a stored proc have a parameter to change the database to be used?

  • 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

  • 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]

  • 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.

  • 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]

  • 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)

  • 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