Problem using Table-valuesParameter in Dynamic SQL

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 using Table-valuesParameter in Dynamic SQL

Author

Topic

danyeung
Posting Yak Master

102 Posts

Posted - 2011-03-13 : 23:00:52

I used the table-value parameter in stored procedure as input parameter, @TableTypeParam DataInputType READONLY. It worked fine if I use “Select * from @TableTypeParam”, but I could not alter the stored procedure if using @TableTypeParam in dynamic SQL (See the following code). I got “Must declare the scalar variable @TableTypeParam” error. Any suggestion how to make it work? Thanks.


While @idx <= @NumberOfMfg
BEGIN 
Set @strSQL = ' insert into Fact_Test
Select ID, Name, Sales’ + Right('00' + CAST(@idx as CHAR(2)), 2) + ' From @MORTableTypeParam’
exec sp_ExecuteSQL @strSQL	
END

Sachin.Nand

2937 Posts

Posted - 2011-03-14 : 04:49:17

AFAIK you cannot access table variables in dynamic sql.You will have to replace the table variable with temporary table.

Try the following code for testing purpose.


Create table Fact_Test_Test(id int)

Declare @strSQL nvarchar(100)=''
Declare @idx int=1

create table #TableTypeParam (id int)
insert #TableTypeParam
select 1

BEGIN 
Set @strSQL = 'insert into Fact_Test_Test(Id)
Select id from  #TableTypeParam where id=@idx'
exec sp_ExecuteSQL @strSQL,N'@idx int',@idx=@idx
END

select * from Fact_Test

Drop table  Fact_Test_Test
Drop table #TableTypeParam

PBUH

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-03-14 : 06:29:00

Nice table design! Love the "Sales1", "Sales2" etc. column names! You wouldn't need dynamic SQL if you didn't decide to create a table variable like that, it's very bad practice. Dynamic SQL should be used extremely sparingly. You're better off having a column "SalesManagmentNumber" spreading the data for each id over several rows and then transforming the data in the front end to have column names of the different Sales Managment Numbers.

I think you're a bit confused about what Right('00' + CAST(@idx as CHAR(2)), 2) is doing. You take a number, convert it to two characters, then add two zeros on the front, then only take the right two character from the resulting string of four characters. Those two zeros are never going to used. Might as well write CAST(@idx as CHAR(2)) without the other bits.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 07:50:03

quote:
Originally posted by michael.appleton
I think you're a bit confused about what Right('00' + CAST(@idx as CHAR(2)), 2) is doing. You take a number, convert it to two characters, then add two zeros on the front, then only take the right two character from the resulting string of four characters. Those two zeros are never going to used. Might as well write CAST(@idx as CHAR(2)) without the other bits.

Although I *totally* agree with you on the first part, the second part is simply not true. This is a very common way to generate a number with leading 0's, i.e. "Sales001" and "Sales034".

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

danyeung
Posting Yak Master

102 Posts

Posted - 2011-03-14 : 13:09:10

quote:
Originally posted by Sachin.Nand

AFAIK you cannot access table variables in dynamic sql.You will have to replace the table variable with temporary table.

Try the following code for testing purpose.

Create table Fact_Test_Test(id int)

Declare @strSQL nvarchar(100)=''
Declare @idx int=1

create table #TableTypeParam (id int)
insert #TableTypeParam
select 1

BEGIN
Set @strSQL = 'insert into Fact_Test_Test(Id)
Select id from #TableTypeParam where id=@idx'
exec sp_ExecuteSQL @strSQL,N'@idx int',@idx=@idx
END

select * from Fact_Test

Drop table Fact_Test_Test
Drop table #TableTypeParam

PBUH

danyeung
Posting Yak Master

102 Posts

Posted - 2011-03-14 : 13:13:17

quote:
Originally posted by danyeung

[quote]Originally posted by Sachin.Nand

AFAIK you cannot access table variables in dynamic sql.You will have to replace the table variable with temporary table.

It works with temp table, but the applications for multiple users. If multiple users executing the stored procedure at the same time, it will bump.

danyeung
Posting Yak Master

102 Posts

Posted - 2011-03-14 : 13:16:09

quote:
Originally posted by michael.appleton

Nice table design! Love the "Sales1", "Sales2" etc. column names! You wouldn't need dynamic SQL if you didn't decide to create a table variable like that, it's very bad practice.

I have to use dynamic SQL because the column numbers are dynamic and selected by users.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-15 : 12:39:48

Here is a sample accessing a table valued parameter using dynamic sql and sp_executeSql:

-------------------------------------------------------------------------------
-- Create Table Type
-------------------------------------------------------------------------------
CREATE TYPE [dbo].[NameTableType] AS TABLE(
	[Name] [varchar](35) NULL
)
GO

-------------------------------------------------------------------------------
-- Create Table for test data
-------------------------------------------------------------------------------
CREATE TABLE dbo.TestTable
(
    Name VARCHAR(35)
)
GO

-------------------------------------------------------------------------------
-- Popualte test data
-------------------------------------------------------------------------------
INSERT dbo.TestTable (Name)
VALUES ('Abe'), ('Bill'), ('Chuck'), ('Dave'), ('Edgar'), ('Fred')
GO

-------------------------------------------------------------------------------
-- Create stroed proc to test
-------------------------------------------------------------------------------
CREATE PROCEDURE dbo.NameTest
(
	@NameList NameTableType READONLY
)
AS
BEGIN
    SET NOCOUNT ON; 

    -- based on SQL Team article:
    -- http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures

        --DECLARE @ParamNameList NameTableType;
        DECLARE @Sql NVARCHAR(MAX);
        DECLARE @ParameterDefinition NVARCHAR(MAX);

    SET	@ParameterDefinition = 
        N'
	        @NameListParam NameTableType READONLY
        ';

    SET	@Sql = 
        N'
            SELECT 
                *
            FROM
                dbo.TestTable
            INNER JOIN
                @NameListParam AS N
                ON TestTable.Name = N.Name
            WHERE 1 = 1
        ';
    	
	PRINT @Sql
	
	EXEC sp_executeSql
	    @Sql,
	    @ParameterDefinition,
	    @NameListParam = @NameList;
END
GO

-------------------------------------------------------------------------------
-- Test Setup
-------------------------------------------------------------------------------
DECLARE @RC int
DECLARE @NameList NameTableType

INSERT @NameList (Name) VALUES ('Bill'), ('Edgar')

EXECUTE @RC = dbo.NameTest 
   @NameList
GO

-------------------------------------------------------------------------------
-- Cleanup
-------------------------------------------------------------------------------
DROP PROCEDURE dbo.NameTest
DROP TABLE dbo.TestTable
DROP TYPE [dbo].[NameTableType]

aleximas
Starting Member

11 Posts

Posted - 2011-03-15 : 22:28:52

I think you're a bit confused about what Right('00' + CAST(@idx as CHAR(2)), 2) is doing. You take a number, convert it to two characters, then add two zeros on the front, then only take the right two character from the resulting string of four characters. Those two zeros are never going to used. Might as well write CAST(@idx as CHAR(2)) without the other bits.
unspammed

rcicotte
Starting Member

1 Post

Posted - 2012-01-18 : 08:00:57

Thanks for this example. It's just what I needed

quote:

Originally posted by Lamprey

Here is a sample accessing a table valued parameter using dynamic sql and sp_executeSql:

-------------------------------------------------------------------------------
-- Create Table Type
-------------------------------------------------------------------------------
CREATE TYPE [dbo].[NameTableType] AS TABLE(
	[Name] [varchar](35) NULL
)
GO

-------------------------------------------------------------------------------
-- Create Table for test data
-------------------------------------------------------------------------------
CREATE TABLE dbo.TestTable
(
    Name VARCHAR(35)
)
GO

-------------------------------------------------------------------------------
-- Popualte test data
-------------------------------------------------------------------------------
INSERT dbo.TestTable (Name)
VALUES ('Abe'), ('Bill'), ('Chuck'), ('Dave'), ('Edgar'), ('Fred')
GO

-------------------------------------------------------------------------------
-- Create stroed proc to test
-------------------------------------------------------------------------------
CREATE PROCEDURE dbo.NameTest
(
	@NameList NameTableType READONLY
)
AS
BEGIN
    SET NOCOUNT ON; 

    -- based on SQL Team article:
    -- http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures

        --DECLARE @ParamNameList NameTableType;
        DECLARE @Sql NVARCHAR(MAX);
        DECLARE @ParameterDefinition NVARCHAR(MAX);

    SET	@ParameterDefinition = 
        N'
	        @NameListParam NameTableType READONLY
        ';

    SET	@Sql = 
        N'
            SELECT 
                *
            FROM
                dbo.TestTable
            INNER JOIN
                @NameListParam AS N
                ON TestTable.Name = N.Name
            WHERE 1 = 1
        ';
    	
	PRINT @Sql
	
	EXEC sp_executeSql
	    @Sql,
	    @ParameterDefinition,
	    @NameListParam = @NameList;
END
GO

-------------------------------------------------------------------------------
-- Test Setup
-------------------------------------------------------------------------------
DECLARE @RC int
DECLARE @NameList NameTableType

INSERT @NameList (Name) VALUES ('Bill'), ('Edgar')

EXECUTE @RC = dbo.NameTest 
   @NameList
GO

-------------------------------------------------------------------------------
-- Cleanup
-------------------------------------------------------------------------------
DROP PROCEDURE dbo.NameTest
DROP TABLE dbo.TestTable
DROP TYPE [dbo].[NameTableType]

-ron

Life is water's way of walking around!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources