temp table sql server stored procedure code example

Example 1: create a quick temp table with stored procedure sql

/* First you need to create a table type. */
CREATE TYPE Names AS TABLE 
(Name VARCHAR(10)) ;
GO
 
/* Next, Create a procedure to receive data for the table-valued parameter, the table of names and select one item from the table*/
CREATE PROCEDURE ChooseAName
  @CandidateNames Names READONLY
AS 
DECLARE @candidates TABLE (NAME VARCHAR(10),
                           theOrder UNIQUEIDENTIFIER)
INSERT  INTO @candidates (name, theorder)
        SELECT  name, NEWID()
        FROM    @CandidateNames
SELECT TOP 1
        NAME
FROM    @Candidates
ORDER BY theOrder
GO
 
/* Declare a variable that references the type for our list of cows. */
DECLARE @MyFavouriteCowName AS Names ;
 
/* Add data to the table variable. */
INSERT  INTO @MyFavouriteCowName (Name)
 SELECT 'Bossy' UNION SELECT 'Bessy' UNION SELECT 'petal' UNION SELECT 'Daisy' UNION SELECT 'Lulu' UNION SELECT 'Buttercup' UNION SELECT 'Bertha' UNION SELECT 'Bubba' UNION SELECT 'Beauregard' UNION SELECT 'Brunhilde' UNION SELECT 'Lore' UNION SELECT 'Lotte' UNION SELECT 'Rosa' UNION SELECT 'Thilde' UNION SELECT 'Lisa' UNION SELECT 'Peppo' UNION SELECT 'Maxi' UNION SELECT 'Moriz' UNION SELECT 'Marla'
 
/* Pass the table with the list of traditional nemes of cows to the stored procedure. */
EXEC chooseAName @MyFavouriteCowName
GO

Example 2: create a quick temp table with stored procedure sql

SET nocount ON
 
DECLARE @FirstTable TABLE (RandomInteger INT)
DECLARE @SecondTable TABLE (RandomInteger INT)
DECLARE @WhenWeStarted DATETIME
DECLARE @ii INT
 
BEGIN TRANSACTION
SET @ii = 0
WHILE @ii < 100000 
  BEGIN
    INSERT  INTO @FirstTable
    VALUES  (RAND() * 10000)
    SET @ii = @ii + 1
  END
SET @ii = 0
WHILE @ii < 100000 
  BEGIN
    INSERT  INTO @SecondTable
    VALUES  (RAND() * 10000)
    SET @ii = @ii + 1
  END
COMMIT TRANSACTION
SELECT  @WhenWeStarted = GETDATE()
SET STATISTICS PROFILE ON
SELECT  COUNT(*)
FROM    @FirstTable first
        INNER JOIN @SecondTable second 
        ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)
  -- 153Ms  as opposed to 653Ms without the hint
SET STATISTICS PROFILE OFF
SELECT  'That took ' 
    + CONVERT(VARCHAR(8), DATEDIFF(ms, @WhenWeStarted, GETDATE())) 
    + ' ms'
go