SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 dynamic SQL in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnbrown74
Starting Member

United Kingdom
2 Posts

Posted - 12/16/2010 :  07:59:33  Show Profile  Reply with Quote
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  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

johnbrown74
Starting Member

United Kingdom
2 Posts

Posted - 12/16/2010 :  12:38:02  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000