Execute different database in one stored procedure-CodePudding

I have two databases on my SQL server. is that possible to use one store procedure to call two different databases to receive the data?

Below is an example.

@product AS nvarchar(max) = NULL

BEGIN 

IF @product= 'iPhone'
BEGIN
USE [iPhone Database]
END 

ELSE IF @product ='Samsung'
BEGIN
USE [samsung Database]
END

SELECT * FROM Product

END

I'm new to SQL, thanks in advance.

CodePudding user response：

Hi the simple way to do this is you can create a view with query of two database that union together

CREATE VIEW dataview AS
SELECT * FROM DB1.tb1
UNION ALL
SELECT * FROM DB2.tb2

then you can query and select data in you store procedure

CodePudding user response：

Firstly, this is a bad design, although I'm not sure how you arrived at it. Secondly the code you posted is not a stored procedure (it has no create procedure in it). Anyway here are a few ways to do what you want:

A. use a view (as posted in the prior answer)

CREATE VIEW EverythingView
AS 
SELECT 'iPhone' as ProductType, * FROM [iPhone Database]..Product
UNION ALL
SELECT 'Samsung' as ProductType, * FROM [samsung Database]..Product

now you can run this:

SELECT * FROM EverythingView WHERE ProductType = 'iPhone'

B. Embed it in the proc

This can suffer from various issues around parameter sniffing. Not recommended

CREATE PROC MyProc 
@product AS nvarchar(4000) = NULL
AS

BEGIN 

IF @product= 'iPhone'
    SELECT * FROM [iPhone Database]..Product

IF @product= 'Samsung'
    SELECT * FROM [samsung Database]..Product
    
END

C. The right way. Put it in one table

I don't know how your data arrives in these seperate databases but the best thing to do is have one product table with everything in it

SELECT * FROM Product WHERE ProductType = 'iPhone'