Tag Archives: #table

Incorrect syntax near the keyword ‘table’ in TSQL

Posted on by
4

Ran into something little that I know I’m going to forget if I don’t write down. It appears that when using a TABLE variable in tsql ( SQL Server 2005 ), you must DECLARE that variable on it’s own line, as opposed to inline with your other @variables.

Typically in my sprocs or sql scripts I do my best to have a main DECLARE block and seperate my @variables with a comma like this.

Typically I DECLARE=

If you're using a TABLE variable, put it on it's own DECLARE line

After some mucking around, it turns out moving the TABLE @variable to it’s own DECLARE line fixes this issue.

DECLARE TABLE @variables on their own line

DECLARE TABLE @variables on their own line

I haven’t found this info in SQL BOL, so I hope this helps somebody else.

Select random value from a range of values

Posted on by
1

Earlier I blogged about creating random numbers using tsql functions.  Here are two techniques for selecting a random value from a pre-defined range of values in a tsql script.  The first technique uses a table variable ( MSSQL 2000 + ), and the second uses a Common Table Expression or CTE ( MSSQL 2005+ ).

Select a random value using a table variable


-- var to hold random integer
declare @field_val int

-- create table var to hold value range [ 0, 512, 1024, 2048, 4096 ]
-- inserting the first value sets the structure for the table variable
SELECT 0 AS 'num'
INTO #temp

-- insert data into table var
INSERT INTO #temp VALUES ( 512 )
INSERT INTO #temp VALUES ( 1024 )
INSERT INTO #temp VALUES ( 2048 )
INSERT INTO #temp VALUES ( 4096 )

-- assign random value
SELECT TOP 1 @field_val = num FROM #temp ORDER BY NEWID()

-- show value
SELECT @field_val

-- drop the table variable
DROP TABLE #temp

Select a random value using a CTE

-- define our data table
WITH data( car )
AS
(
	-- UNION together our range of values
	SELECT 'audi' AS 'car'
	UNION
	SELECT 'bmw' AS 'car'
	UNION
	SELECT 'infinity' AS 'car'
	UNION
	SELECT 'lexus' AS 'car'
	UNION
	SELECT 'porsche' AS 'car'
)
-- select a random value
SELECT TOP 1 car FROM data
ORDER BY NEWID()

Both of these techniques can be used with numbers or text. Just be sure to mind your quotes, and variable datatypes.  Being able to pick a random value in data generation scripts has proven very useful.  I hope this helps somebody else out as well.