locked
Stored Procedure Multiple Databases RRS feed

  • Question

  • I want to be able to declare what database to use in the below stored procedure

    How can I pass the @site Variable to the line replacing [s1] with whatever value is passed.

    Also pasted the modify script so you can see the entire procedure..

    SET @FROM = @FROM + ' FROM [S1].[dbo].[ITEM_VIEW]

    USE [S1]
    GO
    /****** Object: StoredProcedure [dbo].[GETITEMDESCRIPTION]  Script Date: 02/04/2011 14:40:41 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    
    
    
    ALTER PROCEDURE [dbo].[GETITEMDESCRIPTION]
    @BEGITEM AS NVARCHAR(60)
    @SITE AS NVARCHAR(60)
    AS
    
    
    /* ******************** *
      TABLES:
    	[item]
     * ********************* */
    
    
    DECLARE @STR AS NVARCHAR(1024)
    DECLARE @FROM AS NVARCHAR(1024)
    DECLARE @WH AS NVARCHAR(1024)
    DECLARE @TMP AS NVARCHAR(1024)
    
    SET @STR = ''
    
    SET @FROM = '[ITEM_VIEW].[ITEM],[ITEM_VIEW].[DESCRIPTION] '
    
    SET @FROM = @FROM + ' FROM [S1].[dbo].[ITEM_VIEW]'
    
    SET @WH = ''
    SET @TMP = ''
    
    IF @BEGITEM != ''
     BEGIN
     SET @TMP = '[ITEM] = "' + @BEGITEM + '"'
     -----------------------------------------------------
    --S1Reports modification
    set @WH = case @WH when '' then '' else @WH + ' AND ' end
    -- IF @WH != ''
    --  SET @WH = @WH + ' AND '
    
     SET @WH = @WH + @TMP
     -----------------------------------------------------
     END
    IF @WH != '' -- MAKE SURE THE PROCEDURE ONLY EXECUTES IF THERE IS A RESTRICTING WHERE CLAUSE
     BEGIN
     SET @STR = 'SELECT TOP 1 ' + @STR -- DEFAULT SETTING
    
     SET @STR = @STR + @FROM
    
    --S1Reports modification
     SET @STR = @STR + case when len(@WH) > 0 then ' ' + 'WHERE ' + @WH else '' end
    
     EXECUTE(@STR)
    
     END
    
    
    

    Friday, February 4, 2011 7:52 PM

Answers

  • >>>How can I pass the @site Variable to the line replacing [s1] with whatever value is passed.

    DECLARE @db SYSNAME

    SET @db='master'

    SET @FROM = @FROM + ' FROM ['+@db+'].[dbo].[ITEM_VIEW]'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, February 6, 2011 7:24 AM

All replies

  • >>>How can I pass the @site Variable to the line replacing [s1] with whatever value is passed.

    DECLARE @db SYSNAME

    SET @db='master'

    SET @FROM = @FROM + ' FROM ['+@db+'].[dbo].[ITEM_VIEW]'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, February 6, 2011 7:24 AM
  • >>>How can I pass the @site Variable to the line replacing [s1] with whatever value is passed.

    DECLARE @db SYSNAME

    SET @db='master'

    SET @FROM = @FROM + ' FROM ['+@db+'].[dbo].[ITEM_VIEW]'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, February 6, 2011 7:24 AM