Let's make an example
you want to use a temporary table in the same Stored Procedure based on different Condition, as below:
IF(Condition)
BEGIN
select something & Insert into #TempTable
where condition
END
ELSE
BEGIN
select something & Insert into #TempTable
where condition
END
If you try to compile this kind of code, it will return an error like 'There is already an object named #myTempTable in the database'. SQL doesn't allow you to use the ‘into #TempTable’ twice in the same Stored Procedure.
So the simplest way is, create the temporary table first, then make the if-else condition
you want to use a temporary table in the same Stored Procedure based on different Condition, as below:
IF(Condition)
BEGIN
select something & Insert into #TempTable
where condition
END
ELSE
BEGIN
select something & Insert into #TempTable
where condition
END
If you try to compile this kind of code, it will return an error like 'There is already an object named #myTempTable in the database'. SQL doesn't allow you to use the ‘into #TempTable’ twice in the same Stored Procedure.
So the simplest way is, create the temporary table first, then make the if-else condition
DECLARE @VAL INT=1;--Create temp Tableselect * into #Temp from dbo.tblCountries where 0=1--insert records to temp tableIF(@VAL=1)BEGININSERT INTO #Tempselect *FROM dbo.tblCountries--WhereENDELSEBEGININSERT INTO #Tempselect *FROM dbo.tblCountries--WhereENDDROP TABLE #Temp
No comments:
Post a Comment