|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-09-22 : 08:41:59
|
Simona writes "Hallo!
*********
CREATE PROCEDURE test2
(
@clauza varchar(255),
@Page int,
@RecsPerPage int
)
AS
-- turn NOCOUNT ON
SET NOCOUNT ON
-- Find out where we will start our records from
DECLARE @RecCount int
SELECT @RecCount = @RecsPerPage * @Page + 1
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
firstname varchar(50),
lastname varchar(50),
extension varchar(4),
cell varchar(30),
phone varchar(30)
)
-- Insert the rows from employees table into the temp. table
--INSERT INTO #TempItems (firstname,lastname, extension, cell, phone)
DECLARE @cautare varchar(255)
SELECT @cautare ="SELECT firstname, lastname, extension, cell, phone FROM employees WHERE"
+ @clauza
EXEC (@cautare)
INSERT INTO #TempItems (firstname,lastname, extension, cell, phone)
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indication of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID > @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
**********
Sorry for the long lines, actually is an entire SP and I can not make it to work, I have the nice error 156 : incorrect syntax near the keyword declare, and, after I broke it, piece bu piece, seems like the line in trouble is : DECLARE @cautare varchar(255)
So, the SP, is receiving a dynamic query from an asp page, is creating a temporary table and is inserting into it the values of the SQL statement.
I really need your help!
Appreciate in advance, I know maybe it's a syntax problem, but I am a newbie and Ii really need help!
Simona" |
|
Dynamic Stored Procedure