Trying to get a Slect Into to work

Transact-SQL
1

Hi,
I have a stored procedure that I send into SQL statement each time it is run, from a Vb.Net app. Each time it is called it sends in a new SQL statement in.
So first I drop the table and then I do a select into in the statement to create a new table to store the results in.
This works fine, except if the user makes a mistake in the SQL Statement, say a wrong table name. It returns an error saying that it can not find the table that I am making in the select Into statement; which is because it was dropped.
How can I fix this?

Thank you

Here is my Procedure code:

ALTER PROCEDURE [dbo].[uspCustomSQLSelect_To_Table]
-- Add the parameters for the stored procedure here
@sqlString varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
  @ErrorMessage   varchar(2000)
 --,@ErrorSeverity  tinyint
 --,@ErrorState     tinyint

 	IF 	
    EXISTS(SELECT *
    FROM TempHoldTbl)
    DROP TABLE TempHoldTbl
 
BEGIN TRY

	DECLARE @finalSQL nvarchar(max)

	set @finalSQL = 'SELECT * INTO TempHoldTbl FROM (' + @sqlString + char(13) + ') CustomSQLSelect'

	PRINT @finalSQL

    EXEC (@finalSQL)
	
END TRY
	
BEGIN CATCH
	SET @ErrorMessage  = ERROR_MESSAGE()
	--SET @ErrorSeverity = ERROR_SEVERITY()
	--SET @ErrorState    = ERROR_STATE()
	--RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
	SELECT @ErrorMessage AS [An error occurred...]
END CATCH

END

2

What is it that you call a "Fix"?
Do you still want the table after the error? If so, create one in the error handling section.

3

Hi,
I found the problem, I was using the wrong code drop table code for this.
I needed to be using this instead.

IF OBJECT_ID('dbo.TempHoldTbl', 'U') IS NOT NULL
DROP TABLE dbo.TempHoldTbl;

It now is good

Thanks