MSSQL Temporary Table Learning

Learn how to play MSSQL's snacks at home through a stored procedure because I do not know how to call SQL dynamically in the stored procedure so I have to think of another way to think of the temporary table is the key point is to get the number of data stored in the temporary table and then read the temporary table to achieve the purpose of obtaining all the number of bars I learned a lot of new things.

1. How to determine the existence of temporal tables (temporary table name #tt)

if (exists (select * from tempdb). sysobjects WHERE id = object_id (' tempdb. #tt ')))

2. The concept of temporal tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible in all sessions.
The name of the local temporary table is preceded by a number character (#table_name), and the name of the global temporary table is preceded by two number characters (# #table_name).

It's usually a local temporary (#) Other people can't see the creation method and create a table just before the table name has a #

3. Functions that execute SQL dynamically in stored procedures sp_executesql Remember that the variable type of dynamic SQL cannot be a varchar must start with n, for example nvarchar

EXEC sp_executesql @sqllen, N ' @TotleCount int output ', @TotleCount output

Results of the original using temporal tables

if (exists (select * from sysobjects where id= object_id (' proctest ')))
Begin
drop proc Proctest
End

Go
Create proc Proctest
@FeeID varchar (100),
@Money varchar (100),
@Index int,
@Size int,
@TotleCount int Output
As
if (exists (select * from tempdb). sysobjects WHERE id = object_id (' tempdb. #tt ')))
Begin
TRUNCATE TABLE #tt
End
Else
Begin
CREATE TABLE #tt
(
TLen int
)
End
DECLARE @sql varchar (max)
DECLARE @sqllen varchar (max)
Set @sql = ' Select Row_number () over (order by Main_time) as RowIndex, * from main where 1=1 '
if (ISNULL (@FeeID, ')! = ")
Begin
Set @sql + = ' and main_feeid= ' [email protected]
End
if (ISNULL (@Money, ')! = ")
Begin
Set @sql + = ' and main_money= ' [email protected]
End
DECLARE @start int
DECLARE @end int
Set @start = (@Index-1) * @Size +1
Set @[email protected]* @Index
Set @sqllen = ' INSERT INTO #tt select COUNT (1) from (' [email protected]+ ') T '
--print (@sqllen)
EXEC (@sqllen)
Select @TotleCount =tlen from #tt
Set @sql = ' select * FROM (' [email protected]+ ') t where RowIndex between ' + CONVERT (varchar), @start) + ' and ' + conver T (varchar), @end)
EXEC (@sql)

The result after rewriting

if (exists (select * from sysobjects where id= object_id (' proctest ')))
Begin
drop proc Proctest
End

Go
Create proc Proctest
@FeeID varchar (100),
@Money varchar (100),
@Index int,
@Size int,
@TotleCount int Output
As

DECLARE @sql varchar (max)
declare @sqllen nvarchar (max)
Set @sql = ' Select Row_number () over (order by Main_time) as RowIndex, * from main where 1=1 '
if (ISNULL (@FeeID, ')! = ")
Begin
Set @sql + = ' and main_feeid= ' [email protected]
End
if (ISNULL (@Money, ')! = ")
Begin
Set @sql + = ' and main_money= ' [email protected]
End
DECLARE @start int
DECLARE @end int
Set @start = (@Index-1) * @Size +1
Set @[email protected]* @Index

Set @sqllen = ' Select @TotleCount = count (1) from (' [email protected]+ ') T '
EXEC sp_executesql @sqllen, N ' @TotleCount int output ', @TotleCount output

Set @sql = ' select * FROM (' [email protected]+ ') t where RowIndex between ' + CONVERT (varchar), @start) + ' and ' + conver T (varchar), @end)
EXEC (@sql)

Call

DECLARE @aa int
EXEC proctest ' 7 ', ', 1,40, @aa output
Select @aa

MSSQL Temporary Table Learning