Caveman's Blog

My commitment to learning.

Posts Tagged ‘temp table

SQL Server: How to select data from executing dynamic SQL

leave a comment »


In his post I will show you how to select data from executing dynamic SQL. This method is handy in a scenario where you plan to create a Function Import (using EntityFramework [2]) for a stored procedure that returns a result set based on the execution of a dynamic SQL. Capturing the result in a temp table or a table variable will allow EF to define the return type of the Function Import. In my example I am using table variables as they are easier to maintain than temp tables which have a physical presence. The following code returns one row of employee details that was insert into the table variable using dynamic SQL. Dynamic SQL can be executed using exec [3] statement or the sp_exectuesql system stored procedure.

declare @employee table(id int, name nvarchar(50))
declare @str nvarchar(1000) = 'select 1, ''Joe'''

insert into @employee(id, name)
exec(@str)

select * from @employee

DNYSQL-1

Here is another way to capture the output where sp_executesql is used to execute a dynamic sql string. The following code snippet returns the total numbers View in the database from the sys.objects object catalog view [4].

Declare @strSQL as  nvarchar(100)
Declare @strParam as nvarchar(100)
Declare @XType as nvarchar(2) = 'V'
Declare @rtnCount as varchar(100)

Set @strSQL = 'Select @ObjCount=count(*) from Sysobjects Where xType=@xType'
Set @strParam = '@ObjCount int output, @xType nvarchar(2)'
execute sp_executesql @strSQL, @strParam, @rtnCount  output ,@xType

Select @rtnCount [View Count]

DNYSQL-2

References:
1. sp_executesql (Transact-SQL)
2. Entity Framework
3. EXECUTE (Transact-SQL)
4. Object Catalog Views (Transact-SQL)

Written by cavemansblog

May 22, 2013 at 12:13 pm

Sql Server: Temp table vs table variable

leave a comment »


Temp table vs table variable. One question that lingers in my mind is; how to decide between using one over the other?  I will present some points that I have gathered that could aid in helping you make a decision in picking one over the other:

1. Reseed the index of a table variable is not allowed.

2. We will not be able to explicitly create Indexes on a table variable.

3. Altering the schema of a table variable is not possible

4. Table variables do not have to drop it after usage, they will be automatically cleaned up when they are out of scope.

5. Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. They are not affected by transaction rollbacks.

6. Table variables contribute to more efficient Query Processing.

7. CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

8. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK.

Table variables will offer best performance when the data size is small and the . The next time you have to make a choice, choose wisely between the two and make a wise choice for the usage to derive optimal performance from your queries.

References:
1. Table (Transact SQL) – Microsoft SQL Server

Written by cavemansblog

October 24, 2012 at 12:03 am

SQL Server: How to check for the existence of a temp table?

leave a comment »


Temporary tables in SQL are created and maintained in the tempdb database. Here is the TSQL to create a temporary table called “#temp_employee” and the following that is code that lists all the tables in the tempdb database.


create table #temp_employee(emp_id int, emp_name varchar(50))

select * from tempdb.sys.tables

In the list of tables below the fifth row identifies the temp table that we have created above. The actual name of the temp table has got appended. In order to search for the existence of this table we have to do a wild card search on the tempdb like in the SQL below:


if exists(select * from tempdb.sys.tables where name like '#temp_employee%')
print 'temp tables exists'