Thank you Keith for your reply. Sorry for not providing the enough info.
CREATE PROCEDURE [dbo].[USP_Student_History_Search]
@State VARCHAR(2)
@StudentID INT
AS
SET NOCOUNT ON;
DECLARE @index INT
DECLARE @N INT =SELECT CHARINDEX(',',@State)
WHILE @index <= @N
BEGIN
SET @DB = N'Select
[State],
[StudentID],
[Marks]
FROM [Academics_'+@State+'].[dbo].[StudentsHistory]'
Declare @sp-2 table
( [State],
[StudentID],
[Marks]
)
END
INSERT INTO @sp-2 VALUES(SELECT FROM @DB)
If the user passes states - 'TX, MI, CA' then the query should loop over three states from 3 different databases one after other and pull the information.
and finally place it in a single table like the picture shown above.
help is greatly appreciated.