Using table variables for Stored Procedure inputs? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using table variables for Stored Procedure inputs?

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-06-15 : 01:28:59

Can table variable be used as output or input variables for stored procedures? I can find anything in BOL, or Ken Henderson's "Guru's Guide to .." books, or google (so far).

CREATE PROCEDURE [dbo].UP_ExtractPostCard_Card
@PostCardExtractCardResultSet TABLE
([ID] INTEGER IDENTITY(1,1),
[DataLine] varchar(110))

gives a syntax error near TABLE, but the table declaration is fine:

DECLARE @PostCardExtractCardResultSet TABLE
([ID] INTEGER IDENTITY(1,1),
[DataLine] varchar(110))

Works fine..

Anyone?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-15 : 02:44:25

Do you want pass parameter to the procedure and use that as table variable?
Give more details

Madhivanan

Failing to plan is Planning to fail

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-06-15 : 03:28:39

Well, as listed in code, I want to be able to receive a table variable as an input.

If I can do that, I have gotten part of the way. Ideally, I want to pass a table variable as an output parametre from a stored procedure.

That stored procedure could then be invoked by a program, DTS package or script, and would use the contents of the table variable.

The context is that I am generating a output file, with a header and trailer. This file needs to be able to be written from our standard file management system, but the DRP requirements are that it the files be able to be written from either a DTS package or else a script. So, I want to write the code that generates the result set only once, have it put into a table variable, and then be able to be used by any of the calling techniques...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-15 : 05:24:17

quote:
The context is that I am generating a output file, with a header and trailer

Do you want to export data to File? then look for bcp in BOL

quote:
So, I want to write the code that generates the result set only once, have it put into a table variable, and then be able to be used by any of the calling techniques...

Why do you want to use table variable?
Do you want data of that table to be used?
Then move the resultset to table and use it

Madhivanan

Failing to plan is Planning to fail

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-06-15 : 06:11:29

Hi Madhivanan,

Due to the environment we are working in, I am trying to avoid creating tables, since we are running an extract from a Log Shipped (read-only) database.

what I want to do is have a method (currently, looks like I will have more success using a UDF, which can return a table variable) that generates a result set. This method can then either be called by .Net code, which will handle writing of the file, and do logging etc.

However, as a DR requirements, we need to be able to generate this file without the .Net code, so I want the method that creates the file to be able to be handled via a calling SP or a T-SQL script.

In addition, I am trying to do is avoid using temp tables.

Thoughts?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

null
Starting Member

1 Post

Posted - 2005-06-23 : 18:07:29

I'd like to know this as well...would be awesome if you could. Unfortunately, from what I can tell, you can't.

In my scenario, I'm trying to get a stored procedure to exec on several id's and attributes of each id that are local to a trigger event. None of my core db tables hold those specific records of data, so if I could pass the rows of data (the tmp table) to a stored proc it would clean up having to write this code in several other places...I could just call the proc each time, passing it the specific rows it needs to know about.

The only alternative i can see is to create a table and populate it. Pass it, then either drop it @ the end of the proc, or right after the caller calls the proc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-23 : 19:03:06

From CREATE PROCEDURE in BOL:

quote:

data_type

Is the parameter data type. All data types, except the table data type, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

Tara

joshgeake
Starting Member

2 Posts

Posted - 2011-04-15 : 15:55:21

Yeah I wrote a post about this a while ago - essentially you need to form a dynamic sql statement by stitching together a string and then execute it.

See here for the full post - http://www.geakeit.co.uk/2011/02/05/a-table-input-variable-in-a-stored-procedure-and-bypassing-must-declare-the-table-variable-msg-1087/

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-04-19 : 05:20:29

hi
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableBigInt(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value BigInt)
AS
BEGIN
IF(dbo.ValidateExpectedType(106, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @Value BigInt
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @Value = SUBSTRING(@Data, @ptr, 8)
SET @Ptr = @Ptr + 8
IF(@Value=4788860670574159219)
SET @Value = NULL

INSERT INTO @DataTable (Value) VALUES(@Value)
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableBit(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value int)
AS
BEGIN
IF(dbo.ValidateExpectedType(108, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @ThisByte tinyint, @ThisBit tinyint

SELECT @Length = (CAST(SUBSTRING(@Data,2,1) AS INT) & 3) + 4 * (DataLength(@Data)-1)+1, @Ptr = 5

WHILE(@Ptr<@Length)
BEGIN
SET @ThisByte = SUBSTRING(@Data, @ptr/4+1, 1)

SET @ThisBit = (@ThisByte / (POWER(4,(@Ptr%4)))) & 3--TSQL doesn't have bit shift operators:-(
IF(@ThisBit>1)
SET @ThisBit = NULL

SET @Ptr = @Ptr + 1

INSERT INTO @DataTable (Value) VALUES(@ThisBit)
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableDateTime(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value Datetime)
AS
BEGIN

IF(dbo.ValidateExpectedType(104, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @ms bigint, @msf bigint, @hours bigint, @D DateTime
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @ms = SUBSTRING(@Data, @ptr, 8)
IF(@ms=119112338422928)
SET @D = NULL
ELSE
BEGIN
SET @hours = @ms / 3600000
SET @D = DATEADD ( hh , @hours, 0 )
SET @msf = @ms % 3600000
SET @D = DATEADD ( ms , @msf, @D )
END
INSERT INTO @DataTable (Value) VALUES(@D)
SET @Ptr = @Ptr + 8
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableInt(@Data image)
--Gareth 28 june 2003
---2,147,483,648 = NULL
---2,147,483,647 = Use Next DWORD
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value int)
AS
BEGIN

IF(dbo.ValidateExpectedType(101, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @ID int
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @ID = SUBSTRING(@Data, @ptr, 4)
IF(@ID<-2147483646)
IF(@ID=-2147483648)
SET @ID = NULL
ELSE
BEGIN
SET @Ptr = @Ptr + 4
SET @ID = SUBSTRING(@Data, @ptr, 4)
END
INSERT INTO @DataTable (Value) VALUES(@ID)
SET @Ptr = @Ptr + 4
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableMoney(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value Money)
AS
BEGIN
IF(dbo.ValidateExpectedType(107, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @Value Money
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @Value = SUBSTRING(@Data, @ptr, 8)
SET @Ptr = @Ptr + 8
IF(@Value=478886067057415.9219)
SET @Value = NULL

INSERT INTO @DataTable (Value) VALUES(@Value)
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableNvarchar(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value Nvarchar(4000))
AS
BEGIN
IF(dbo.ValidateExpectedType(102, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @VarcharLength smallint, @Value Nvarchar(4000)
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @VarcharLength = SUBSTRING(@Data, @ptr, 2)
SET @Ptr = @Ptr + 2
IF(@VarcharLength<0)
SET @Value = NULL
ELSE
BEGIN
SET @Value = SUBSTRING(@Data, @ptr, @VarcharLength)
SET @Ptr = @Ptr + @VarcharLength
END
INSERT INTO @DataTable (Value) VALUES(@Value)
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableRowversion(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value varbinary(8))
AS
BEGIN
IF(dbo.ValidateExpectedType(109, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @Value varbinary(8)
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @Value = SUBSTRING(@Data, @ptr, 8)
SET @Ptr = @Ptr + 8
IF(@Value=0x4C756D756D626121)
SET @Value = NULL

INSERT INTO @DataTable (Value) VALUES(@Value)
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableUniqueidentifier(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value uniqueidentifier)
AS
BEGIN

IF(dbo.ValidateExpectedType(105, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @GUID uniqueidentifier , @NULL_GUID uniqueidentifier
SET @NULL_GUID = '50617472-6963-6520-4C756D756D626121'

SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @GUID = SUBSTRING(@Data, @ptr, 16)
IF (@GUID=@NULL_GUID)
SET @GUID = NULL

INSERT INTO @DataTable (Value) VALUES(@GUID)
SET @Ptr = @Ptr + 16
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create FUNCTION dbo.GetTableVarchar(@Data image)
--Gareth 28 june 2003
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value Varchar(8000))
AS
BEGIN
IF(dbo.ValidateExpectedType(103, @Data)<>1) RETURN

DECLARE @Ptr int, @Length int, @VarcharLength smallint, @Value Varchar(8000)
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
SET @VarcharLength = SUBSTRING(@Data, @ptr, 2)
SET @Ptr = @Ptr + 2
IF(@VarcharLength<0)
SET @Value = NULL
ELSE
BEGIN
SET @Value = SUBSTRING(@Data, @ptr, @VarcharLength)
SET @Ptr = @Ptr + @VarcharLength
END
INSERT INTO @DataTable (Value) VALUES(@Value)
END

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create FUNCTION dbo.ValidateExpectedType(@TypeExpected tinyint,@Data image)
--Gareth 28 june 2003
RETURNS bit
AS
BEGIN
DECLARE @TypeGiven tinyint
SET @TypeGiven = SUBSTRING(@Data, 1, 1)

IF(@TypeGiven=@TypeExpected)
RETURN 1

DECLARE @Expected varchar(32), @Given varchar(32)
SELECT @Expected = 'Unknown', @Given = 'Unknown'

IF(@TypeExpected=101)
SET @Expected = 'Int'
ELSE IF(@TypeExpected=102)
SET @Expected = 'Nvarchar'
ELSE IF(@TypeExpected=103)
SET @Expected = 'Varchar'
ELSE IF(@TypeExpected=104)
SET @Expected = 'DateTime'
ELSE IF(@TypeExpected=105)
SET @Expected = 'Uniqueidentifier'
ELSE IF(@TypeExpected=106)
SET @Expected = 'BigInt'
ELSE IF(@TypeExpected=107)
SET @Expected = 'Money'
ELSE IF(@TypeExpected=108)
SET @Expected = 'Bit'

IF(@TypeGiven=101)
SET @Given = 'Int'
ELSE IF(@TypeGiven=102)
SET @Given = 'Nvarchar'
ELSE IF(@TypeGiven=103)
SET @Given = 'Varchar'
ELSE IF(@TypeGiven=104)
SET @Given = 'DateTime'
ELSE IF(@TypeGiven=105)
SET @Given = 'Uniqueidentifier'
ELSE IF(@TypeGiven=106)
SET @Given = 'BigInt'
ELSE IF(@TypeGiven=107)
SET @Given = 'Money'
ELSE IF(@TypeGiven=108)
SET @Given = 'Bit'

DECLARE @X int
SET @X = 'The real error is - Function expected a list of ''' + @Expected + ''' types but was given a list of ''' + @Given + ''' types.'--This is a hack to raise an error message, as 'RAISERROR' can not be used in a function

RETURN 0

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-19 : 10:54:38

Hopefully, I didn't miss a response. But, the short answer is you cannot use table-valued parameters in SQL 2000. As others have said, you'll need to use an alternate method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources