Post reply

Stored procedure that do some job in a subset of databases

  • December 18, 2006 at 7:45 am

    #170780

    Hello Everyone,

    I am looking for a way I can write a stored procedure that will do somme work in a subset of database:

    - Deleting object of a user

    - Dropping the user

    - Inserting a row in a table

    ...

    Is there a simple way I can "position" myself in a database and, after having done the work, "position" myself in another database (...) ?

    Any suggestion?

    Best regards.

    Carl

  • December 18, 2006 at 9:27 am

    #678704

    Carl

    If I'm understanding you correctly, this is what you do:

    (1) Create a stored procedure in the master database called sp_MyWork (the sp_ prefix is important).

    (2) Run the stored procedure against the MyDB database like this: EXEC MyDB.dbo.sp_MyWork

    (3) Run it against the MyOtherDB database like this: EXEC MyOtherDB.dbo.sp_MyWork

    John

  • December 18, 2006 at 9:35 am

    #678710

    Hello John,

    It could be the case..

    But what I was thinking is:

    (1) Create a stored procedure in the master database called sp_MyWork

    (2) Run the stored procedure against "hardcoded" or "dynamically found" databases MyDD, MyOtherDB, ...

    Regards.

    Carl

  • December 18, 2006 at 10:14 am

    #678722

    Hello John,

    Can I do that with dynamically found databases?

    Regards.

    Carl

  • December 18, 2006 at 10:17 am

    #678726

    Don't know if it still works in 2005 but you'll get the idea :

    EXEC sp_MsForEachDB 'EXEC ?.dbo.sp_MyWork'

    Or just use straight dynamic sql and you'll be fine.

  • December 18, 2006 at 10:42 am

    #678738

    Thank's... I will try it with the unofficial  stored procedure sp_MSforeachdb.

    Best regards.

    Carl

  • December 18, 2006 at 10:55 am

    #678740

    If it is forbidden in your environement, then just use a loop or cursor and build the dynamic sql string just like in the command.  The results will be the same and the punishement may be avoided .

  • December 18, 2006 at 11:06 am

    #678745

    I'll avoid to punish myself

    Thanks.

    Carl

  • December 18, 2006 at 11:46 am

    #678754

    It's good to be it's own boss .

  • December 18, 2006 at 11:47 am

    #678755

    Can I pass a variable to sp_MyWork using EXEC sp_MSforeachdb 'EXEC ?.dbo.sp_MyWork' ?

    I have to call sp_MyWork with a username...

    How?

    Regards.

    Carl

  • December 18, 2006 at 11:52 am

    #678759

    Yes the command parameter is a nvarchar, so you can pretty much do whatever you want.  The difference lies with the fact that ? is a placeholder for the current database.  Other than that it's normal dynamic sql.

    I'm not sure it's legal to concatenate the username directly in the parameter... maybe you'd have to create a variable, set the dynamic sql there and then pass the variable... and I now realise that it would have been faster to test it myself than to type this message .

  • December 18, 2006 at 11:55 am

    #678761

    And now to answer myself .

    DECLARE @a int

    DECLARE @sql AS VARCHAR(100)

    set @a = 0

    SET @sql = 'EXEC ?.dbo.sp_MyWork ' + CONVERT(varchar(10), @a)

    EXEC sp_MsForEachDB @sql

    EXEC sp_MsForEachDB 'EXEC ?.dbo.sp_MyWork ' + CONVERT(varchar(10), @a)

    /*

    Serveur : Msg 170, Niveau 15, État 1, Ligne 9

    Line 9: Incorrect syntax near '+'.

    */

  • December 18, 2006 at 12:03 pm

    #678762

    Wow... Thank's a lot "Ninja" for this example .

    I'll try it soon.

    Best regards.

    Carl

  • December 18, 2006 at 1:58 pm

    #678804

    It works well.

    Thanks a lot Ninja.

    Carl

  • December 18, 2006 at 2:08 pm

    #678806

    NP.

     

    So when are you scheduled for that public humiliation session?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply