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'