Thanks to all, I got this solved. I have assigned a GUID to the temp table name and built/select/drop it dynamically. I have tested it and it is working perfect.

Once...

Thank you very much Keith for your concern, I will look into this.

I am also trying to do a "UNION ALL" but not sure how to do it dynamically like...if...

Thank you Sean Lange, I did not think about it. I liked the idea of "Jeff Moden" but I am not sure about how to create a partitioned view.

I would...

Thank you Keith for prompt reply. I have changed the @DB to NVARCHAR.

SP Executed succeessfully but when I executed by passing parameters it throws error.

Msg 207, Level 16, State 1,...

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...

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