• Thank you SrcName for your reply. I have followed your query and came up with this sample query. It was successfully created but when I am trying to execute, I am getting the below error.

    Error Message:

    Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    MyQuery:

    CREATE PROCEDURE [dbo].[uspChannelHistory]

    (@Channel_nbr INT

    ,@ChannelLevelID INT

    ,@ChannelName VARCHAR(50)

    ,@ChannelID INT

    ,@StateCode VARCHAR(60)

    )

    AS

    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..##Result') IS NOT NULL

    DROP TABLE ##Result

    CREATE TABLE ##Result

    (

    Channel_nbrINT,

    ChannelLevelIDINT,

    ChannelNameVARCHAR(50),

    ChannelIDINT,

    StateCodeVARCHAR(2)

    )

    DECLARE @DB VARCHAR(500)

    DECLARE @Count INT = 0

    DECLARE @N INT = LEN(@StateCode) - LEN(REPLACE(@StateCode, ',', ''))

    WHILE @Count <= @N

    BEGIN

    SET @DB = N'INSERT INTO ##Result

    SELECT

    Channel_nbr

    ChannelLevelID

    ChannelName

    ChannelID

    StateCode

    FROM [Sample_'+ LEFT(@StateCode, 2) +'].[dbo].[StudentHistory]

    WHERE Channel_nbr = @Channel_nbr

    AND ChannelLevelID = @ChannelLevelID

    AND ChannelName = @ChannelName

    AND ChannelID = @ChannelID'

    SET @Count = @Count + 1

    IF@Count <= @N

    SET @StateCode = RIGHT(@StateCode, LEN(@StateCode) - 3)

    EXEC sp_executesql @DB, N'@Channel_nbr INT ,@ChannelLevelID INT ,@ChannelName VARCHAR(50),@ChannelID INT,@StateCode VARCHAR(60) ', @Channel_nbr, @ChannelLevelID, @ChannelName, @ChannelID, @StateCode

    SELECT * FROM ##Result

    END

    --EXEC [dbo].[USP_Producer_History_Search] 2,4,'Media', 4, 'FL, TX'