dynamic SQL in stored procedure

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)

dynamic SQL in stored procedure

Author

Topic

johnbrown74
Starting Member

2 Posts

Posted - 2010-12-16 : 07:59:33

Hi

I wonder if anyone could help me. I am trying to write a stored procedure for SQL 2000 using dynamic SQL as below:

ALTER PROCEDURE PageColours
(
@startRowIndex int,
@maximumRows int,
@colourlist varchar(160)
)
AS

--Create a table variable
DECLARE @TempItems TABLE
(
rowID int IDENTITY,
recordID int
)

DECLARE @SQLStatement varchar(512)

SET NOCOUNT ON

-- Insert the rows from tblItems into the temp. table
SET @SQLStatement = "INSERT INTO " + @TempItems + " (recordID) SELECT id FROM stockitems " + @colourlist

EXEC(@SQLStatement)

-- Now, return the set of paged records
SELECT id,name
FROM @TempItems t
INNER JOIN stockitems ON
stockitems.id = t.recordID
WHERE rowID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

RETURN

@colourlist contains the WHERE construct with a list of colours I am trying to match an item to e.g

WHERE itemcolour LIKE '%red%' AND itemcolour LIKE '%blue%'

@colourlist is built dynamically via ASP before being sent into the stored procedure

When I try and save the stored procedure I keep getting the error message:

must declare the variable '@TempItems'

As you can see this has been declared as a table variable. Am I right in thinking that tables created inside stored procedures can be used in dynamic SQL?

Any help would be much apprceiated.

Thanks

John

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-16 : 08:33:39

A table variable is only visible in the scope it was created in - in this case the stored procedure. It is not visible in anything the procedure calls, including dynamic SQL.

If you want to do this (which is generally not a good idea, you're vulnerable to SQL injection here), you'll need a temp table, not a table variable.

--
Gail Shaw
SQL Server MVP

johnbrown74
Starting Member

2 Posts

Posted - 2010-12-16 : 12:38:02

quote:
Originally posted by GilaMonster

A table variable is only visible in the scope it was created in - in this case the stored procedure. It is not visible in anything the procedure calls, including dynamic SQL.

If you want to do this (which is generally not a good idea, you're vulnerable to SQL injection here), you'll need a temp table, not a table variable.

--
Gail Shaw
SQL Server MVP

Thanks for the help, got it to work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources