CODEWITHSUNDEEP
sql-server-2008syntax-errorprocedure
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7571

Issue with executing SQL Procedure

I have an issue executing below piece of code:

DECLARE @CUTOFFDAYS_i INT
DECLARE @SQL NVARCHAR(MAX)
DECLARE @SQL1 NVARCHAR(MAX)

SET @CUTOFFDAYS_i = 750

CREATE TABLE #TMP(
EMPID INT,
EMPNAME VARCHAR(35)
)

SET @SQL = 'SELECT EMPID, EMPNAME INTO #TMP FROM EMPDB..EMPTABLE 
WHERE DATEDIFF(DAY, CREATEDDATE, GETDATE()) > @CUTOFFDAYS_i
AND ERRORMESSAGE = '''''

 SET @SQL1 = 'SELECT * FROM #TMP'

 EXEC SP_EXECUTESQL @SQL
 EXEC SP_EXECUTESQL @SQL1

Even then I don't get any result upon 

SELECT * FROM #TMP

Upvotes: 1

Views: 79

Answers (2)

valex
valex

Reputation: 24134

First of all you should declare @SQL and @SQL1 as a fixed maximum length varchar not exceeded 8000 (I guess it's not the restriction for the current MS SQL version???):

DECLARE @SQL NVARCHAR(1000)
DECLARE @SQL1 NVARCHAR(1000)

Then you shouldn't use () when call SP_EXECUTESQL

 EXEC SP_EXECUTESQL @SQL
 EXEC SP_EXECUTESQL @SQL1

And the finally you should use global temp table ##TMP in SP_EXECUTESQL for INSERT and then SELECT or you should declare local #Tmp table before the first SP_EXECUTESQL call

DECLARE @CUTOFFDAYS_i INT
DECLARE @SQL NVARCHAR(1000)
DECLARE @SQL1 NVARCHAR(1000)

SET @CUTOFFDAYS_i = 750

SET @SQL = 'SELECT EMPID, EMPNAME INTO ##TMP FROM EMPDB..EMPTABLE 
WHERE DATEDIFF(DAY, CREATEDDATE, GETDATE()) > @CUTOFFDAYS_i
AND ERRORMESSAGE = '''''

 SET @SQL1 = 'SELECT * FROM ##TMP'

 EXEC SP_EXECUTESQL @SQL
 EXEC SP_EXECUTESQL @SQL1

Upvotes: 0

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

Your usage of # is wrong. #TMP is a temporary table that's only availble to the connection that makes it. I think what you're looking for is ##TMP.

But if you need such a temporary table, I would advice you to make a proper table instead as it's much easier to manage and keep track off and keeps less strain on your tempdb.

Upvotes: 2

Related Questions