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]

)

SET @index = @index + 1

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.