SQL Server Performance Forum – Threads Archive

passing a variable to the TOP option

Here is the command that I need to run INSERT INTO #B SELECT TOP @TOPCNT FROM #A My first problem is that I can’t use a variable as the parameter in the TOP command. I have tried putting the SQL into a variable and then using the EXEC (@command), but the EXEC can’t reference local temporary tables. Any suggestions?
Live to Throw
Throw to Live

---

This should work … DECLARE @SQL VARCHAR(100) SET @SQL = ‘SELECT TOP ‘ + CAST(@TOPCNT AS VARCHAR(10)) + ‘ * FROM #A’ INSERT INTO #B
EXEC (@SQL)

---

or set rowcount @topCnt
insert into #b select * from #a
set rowcount 0

---

The ROWCOUNT trick worked. I can’t use the EXEC because you won’t be able to reference the temp table created by the EXEC. I assume this is because the EXEC command starts up a new session. I don’t have time to find out why, but it’s working with the ROWCOUNT. Thanks for all the replies. Live to Throw
Throw to Live

---

Temp table would have to be created before exec.

---

I am talking about having the EXEC create the temp table with an INTO clause and then referencing it. Live to Throw
Throw to Live

---

You should use CREATE TABLE for temp tables. SELECT … INTO …. FROM can cause all kinds of problems, for instance locking issues – avoid it wherever possible.

---

]]>