| Author |
 Topic  |
|
|
johnbrown74
Starting Member
United Kingdom
2 Posts |
 Posted - 12/16/2010 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/16/2010 : 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
United Kingdom
2 Posts |
Posted - 12/16/2010 : 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 |
|
|
| |
Topic |
|
|
|
dynamic SQL in stored procedure
