Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: Use SPs & Functions without adding them to DB

    Using SQL Server Management Studio (AKA query analyzer) I'm connecting to a database and doing some work on it.

    I have a few 'utility' functions and stored procedures that I would like to use in this database but I dont want to acctually CREATE these objects in the database.

    For example I have a useful stored procedure that takes a @SEARCHSTRING parameter and returns a list of all the Stored Procedures that contain the search parameter.

    I would like to use this SP to search the database I'm connecting to, but I dont want to create this SP in the database. Suppose the database is a production database, or maybe I just dont have CREATE access on it.

    Is there any way I can have all my utility functions somehow stored locally, and just USE them on any database that I connect to?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can't use something that doesn't exist. You can use temporary stored procedures and functions, but this means that your command object will have to contain the body of the procedure/function (create....), and then the execution line. So, in a sense, it will be viewed as a batch. Once you exit the execution scope of the batch, - all temporary objects are marked for removal.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2010
    Posts
    75
    I know I cant use something that doesnt exist... I guess what I was really asking is, you know how in Management Studio you can be connected to multiple databases at once? Well can I be connected to a local database that contains all of my utility functions, AS WELL AS the actual database I'm interested in, and somehow execute my LOCAL stored procedure on the remote database? I dont know if that clarifies what I was asking, or if your answer is still the same.

    What I am doing now is simply copying the BODY of the utility stored procedure from a text file saved on my desktop (or from my local database that contains all my utility functions), to the query window of the database in question, whenever I need the functionality. Is that what you meant by 'temporary functions' ? It seems like there should be an easier way.

    Basically I'd like to be able to connect to any database, and without having to do any extra work, have all of my utility functions available and ready to go.
    Last edited by BeerOclock; 03-15-10 at 17:54.

  4. #4
    Join Date
    Feb 2007
    Posts
    38
    Hi

    I dont think you can do it either.

    But if you tired of doing
    What I am doing now is simply copying the BODY of the utility stored procedure from a text file saved on my desktop (or from my local database that contains all my utility functions), to the query window of the database in question, whenever I need the functionality.

    Save the BODY of the utility stored procedure to input.sql(text file) then use the following isql command in DOS prompt.

    isql -i input.sql -o result.out -ddb -Sserver -Uuser -Ppassword

    You can then review the result.out for the test result.

    And you can create a batch file run_isql.bat as below to pass the database and server name etc.
    run_isql.bat contains
    isql -i input.sql -o result.out -d%1 -S%2 -U%3 -P%4

    In DOS command prompt type the following command:
    run_isql dbname servername username password

    I hope this can help you.

  5. #5
    Join Date
    Feb 2010
    Posts
    75
    Thats disappointing and surprising that I cant do this seemingly simple thing...
    Thanks to all who answered!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would disagree on the disappointing and surprising assessment.

    The simple thing would be to write your sprocs as scripts instead.
    An alternative is to create a separate schema in your database(s) for holding administrative code. I've find this solution very useful, and it keeps the production code pristine.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1. You can use the Templates in SSMS. Click CTRL + ALT + T to see them. You can edit, create, delete and parameterise these. They are local to SSMS.
    2. You can use the Snippet Manager in SQL Prompt (my preference)
    3. You can create your procedures in Master and prefix them sp_. These are then scoped to the database you are connected to.

    simples

  8. #8
    Join Date
    Feb 2010
    Posts
    75
    Thanks!

    I dont *think* templates are what I'm after..

    I tried creating a dbo.sp_test in Master but It wasnt visible to other databases. I tried creating a sys.sp_test2 in Master but it said I didnt have permission to do so. Can you give me an example of option # 3?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did you try executing the procedure? This sproc will not be "visible" in other databases (depending what you mean by visible)

  10. #10
    Join Date
    Feb 2010
    Posts
    75
    Yeah, poor choice of words. What I meant was

    "Could not find stored procedure 'dbo.sp_test1'."

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    USE master
    GO
    
    IF NOT EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.sp_yay')) 
    BEGIN
        EXEC('
    CREATE PROCEDURE  dbo.sp_yay
    AS
    BEGIN
        SELECT NULL
    END'
        )
    END
    GO
    
    ALTER PROCEDURE dbo.sp_yay
    AS
        SELECT  'Yay for pootle!'
    GO  
    
    EXEC    tempdb.dbo.sp_yay
    GO 
    EXEC    msdb.dbo.sp_yay
    GO 
    EXEC    model.dbo.sp_yay
    GO 
    
    USE master
    GO
    
    DROP PROCEDURE dbo.sp_yay
    GO

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I should add this would not be my recommended course. If I understand you correctly, I think you should persevere with the templates until you can justify more concretely why they are not suitable.

  13. #13
    Join Date
    Feb 2007
    Posts
    38
    I am sorry to interupt your conversation but I misunderstood that BeerOclock wanted to connect to databases in different servers. That's why I suggested to use isql. If this is for all in the same server, then obviously we have solution as pootle flump suggested. My apology for poor understanding...

  14. #14
    Join Date
    Feb 2010
    Posts
    75
    Dont be sorry, I appreciate any and all help.
    You did understand me correctly, I do want to connect to databases in different servers. I work on several projects simultaneously, and each project has a development database on a different server. I'd like to have one common spot for all my utility functions (I was thinking that a local database on my machine would be ideal) and I would like to be able to connect to these various other databases, and have all my utility functions available.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •