Problem with CTE and Table variable combination

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 2008 Forums

Transact-SQL (2008)

Problem with CTE and Table variable combination

Author

Topic

amir_sarajan
Starting Member

3 Posts

Posted - 2010-08-10 : 10:24:41

-- I have problem with the following query it just doesn't execute :(
-- I don't know why! I think there must be a problem with CTE and
-- Table variable parameters combination in user defined functions !!!

USE TestDB2

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1]
(
[ID] [int] IDENTITY(1, 1)
NOT NULL ,
[Name] [nvarchar](50) NULL ,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [ID] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON [PRIMARY]

GO

CREATE TYPE [dbo].[Table1IDs] AS TABLE(
[ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

GO

CREATE FUNCTION Test
(
@IDs Table1IDs READONLY
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
WITH SelectedEntities ( ID, Name )
AS ( SELECT Table1.*
FROM @IDs AS IDparam
INNER JOIN dbo.Table1 ON IDparam.ID = Table1.ID
)
SELECT *
FROM SelectedEntities
)

GO

INSERT INTO dbo.Table1 VALUES('a');
INSERT INTO dbo.Table1 VALUES('b');
INSERT INTO dbo.Table1 VALUES('c');
INSERT INTO dbo.Table1 VALUES('d');
INSERT INTO dbo.Table1 VALUES('e');

GO

-- Story starts here. I'll appreciate it if some one can check this
-- out. :)

DECLARE @Param AS Table1IDs

INSERT INTO @Param
SELECT ID
FROM dbo.Table1
WHERE ID < 10

SELECT *
FROM dbo.Test(@Param)

amir_sarajan
Starting Member

3 Posts

Posted - 2010-08-10 : 10:28:50

Also check this one
http://connect.microsoft.com/SQLServer/feedback/details/377661/cte-based-table-valued-function-with-table-type-variable-reference-fails-to-produce-execution-plan-or-execute
it seems someone else also had encountered this problem

parody
Posting Yak Master

111 Posts

Posted - 2010-08-11 : 04:35:32

Your sample code ran without any errors on my local 2008 install. Do you have the latest service pack installed

amir_sarajan
Starting Member

3 Posts

Posted - 2010-08-13 : 13:44:17

thanks parody
but I've already installed service pack 1 which is freely available for download from the following link
http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en
the service pack 2 is also available but it's still a CTP and it hasn't been guaranteed yet for production server

I wrote the same query but without any function and executed it directly. it worked !!!!!
but when ever I wanted to execute that function the sql server didn't respond and even didn't produce estimated execution plan !!!

It seems the only choice that I have is SQL server 2008 R2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources