SQL Server Performance

Use database name from variable in SP

Discussion in 'General Developer Questions' started by muntyanu, Jun 12, 2006.

  1. muntyanu New Member

    Hi All,

    I need to use in stored procedure database which name I am passing as a parameter.
    The question is : how can I use that name from parameter to reference database with the same name and use it in cursor declaration. Is that possible at all?

    Thank you in advance for any help,
    Roman
  2. SQLDBcontrol New Member

    Roman,

    if you create the procedure in the master database when you execute that procedure from another database it will be executed within the context of the database you're in. So you don't need to pass a parameter to the database.



    use master
    go

    create sp_myprocedure
    as
    select * from sysobjects --will run within the context of the database you're int
    go

    use northwind
    go
    exec sp_myprocedure --will return objects in northwind.dbo.sysobjects


    Now you haven't explained how you want to use the database name but hopefully this will help you. If you need to pass the database name as a parameter then you'll need to start messing with dynamic sql.



    Karl Grambow

    www.sqldbcontrol.com
  3. Madhivanan Moderator

    Give us more details on what you are trying to do. Why do you want to pass dbname as objects and use Cursor?

    Madhivanan

    Failing to plan is Planning to fail
  4. muntyanu New Member

    Thank you Karl and Madhivanan for attention to my matter,

    I will exlpain my goal in more details.
    My application running in such a way that customer can set prefix for database during product installation.
    Base name for database is "Clinical" and customer can install it as "Dental_Clinical". I can figured out current database name from ini file and use correct connection string when access SP in "Dental_Clinical"from C# code.
    Our product has another database that in current installation has same prefix "Dental_". So assume that the name of second database is "Dental_Lab".
    In that stored procedure from "Dental_Clinical" I have cursor that pulling some data from "Dental_Lab" database

    DECLARE EncounterProviders_cursor CURSOR FOR
    SELECT Dental_Lab.dbo.Results.Description
    FROM ClinicalParticipation INNER JOIN
    Dental_Lab.dbo.Results.Date ON ClinicalParticipation.Date = Dental_Lab.dbo.Results.Date
    WHERE (ClinicalParticipation.ActID = @EncounterID)

    I can pass "Dental_Lab" as a parameter to SP to know what current name for "Lab" database is. My question is how can I use that string to access "Dental_Lab" database in my cursor?

    Thank you very much in advance

    Roman
  5. ranjitjain New Member

    Read about building dynamic SQL in BOL.
  6. Adriaan New Member

    Using dynamic SQL for opening a cursor is problematic, to say the least. You probably need to use dynamic SQL to insert data into a temp table, then run the cursor based on the temp table - that is, if you really need to use a cursor.
  7. Madhivanan Moderator

    Why are you using Cursor? Can you post the code?

    Madhivanan

    Failing to plan is Planning to fail
  8. muntyanu New Member

    Thank you all for attention,
    actually I found the way how to use dynamic SQL with cursor
    SELECT @sql = 'DECLARE my_cur INSENSITIVE CURSOR FOR ' +
    'SELECT col1, col2, col3 FROM ' + @table
    EXEC sp_executesql @sql
    FETCH NEXT FROM my_cur
    and it is working for me.


    Madhivanan,

    I need cursor to concatenate all names from the records which I got from cursor execution
    I am pretty sure there is more proper way to do it just in a query, but not very familiar with TSQL.
    If you can point me to more proper solution I would very appreciate your help

    OPEN EncounterProviders_Cursor
    FETCH NEXT FROM EncounterProviders_Cursor
    INTO @ProviderName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Use current ProviderID to get provider name
    IF @Providers <> ''
    BEGIN
    SET @Providers = @Providers + '; '
    END
    Set @Providers = @Providers + @ProviderName

    -- Try to get next record
    FETCH NEXT FROM EncounterProviders_Cursor
    INTO @ProviderName
    END

    Roman
  9. Madhivanan Moderator

  10. FrankKalis Moderator

  11. muntyanu New Member

    Thank you very much everyone. <br />My case is definetely the case when I need to concatenate strings on server side.<br />Actually, I should have been referenced<a target="_blank" href=http://www.sommarskog.se/dynamic_sql.html>http://www.sommarskog.se/dynamic_sql.html</a> as I found solution for using dynamic SQL for cursor in it <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br /><br />Thanks,<br />Roman
  12. balaganesh2k Member

  13. Madhivanan Moderator

    quote:Originally posted by balaganesh2k

    I wonder why nobody yet mentioned:http://www.sommarskog.se/dynamic_sql.html

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs


    Hi Frank,

    This link is reallly good.
    Thanks Frank.

    Ganesan B.
    Well defined problem is half way solved.
    When you reply, dont copy and paste the reply. Click "Reply with Quote" icon and post your reply

    Madhivanan

    Failing to plan is Planning to fail
  14. balaganesh2k Member

    quote:Originally posted by Madhivanan


    quote:Originally posted by balaganesh2k

    I wonder why nobody yet mentioned:http://www.sommarskog.se/dynamic_sql.html

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs


    Hi Frank,

    This link is reallly good.
    Thanks Frank.

    Ganesan B.
    Well defined problem is half way solved.
    When you reply, dont copy and paste the reply. Click "Reply with Quote" icon and post your reply

    Madhivanan

    Failing to plan is Planning to fail

    Thanks Madhivanan.


    Ganesan B.
    Well defined problem is half way solved.

Share This Page