Yesterday one of my colleagues named Anjusha came for an help. There is an SP which generates a salary details. The situation is that the sp is retuning the dataset by executing dynamic query using the SP_sqlexec.I want to generate the report based on some conditions like when salary > 15000 EmpPF = 0.This is easy since we all knew that it can be done with the help of case statement. But the thing is the result set in SP’s and the columns are dynamic. So First I thought to move the Sp result into a temporary table. For that I have created dynamic query for creating the temporary table based on the rows from a table. For that I ran a test script.
DECLARE @id VARCHAR(MAX)
DECLARE @tabsql VARCHAR(MAX)
SET @id = ”
SELECT @id = @id + ‘ ‘ + ‘[‘ + test_name + ‘]’ + ‘ varchar(50) ,’
FROM [tbl_HR_SalComponents_Test]
ORDER BY displayorder
SELECT @tabsql = ‘create table #tmp(‘ + ‘ ‘ + +Substring(@id, 0, Len(@id)) +
‘)’
EXEC Sp_sqlexec @tabsql
select * from #tmp –For checking whether the table is created
When I ran the script, it throws an error
Msg 208, Level 16, State 0, Line 14
Invalid object name ‘#tmp’.
But when I print the @tabsql it is working fine without an Error. This makes to learn more about Temporary tables. During that research I learned Global Temporary table. This solved my situation. Why this query worked when I use ##tmp?.
SELECT @tabsql = ‘create table ##tmp(‘ + ‘ ‘ + +Substring(@id, 0, Len(@id)) +
‘)’
The reason is temporary table are automatically dropped after the execution of a Procedure, where as global table variables exist.
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
Hope everyone enjoyed the article. Happy Diwali………!