Simple Query. Please help ! - SQL Server Forums

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

Development Tools

ASP.NET

Simple Query. Please help !

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2003-04-17 : 09:29:43

Hi All,

I creating a stored procedure in which I creating a dynamic SQL. But when I am executing the statement through exec it's giving me an error.

"Must declare the variable '@TempItems'."

The code I am using is :-

Declare @TempItems table (ID int IDENTITY,booking_ref bigint,date_of_booking datetime)

declare @sql varchar(5000)

set @sql='INSERT INTO @TempItems (booking_ref, date_of_booking) select distinct booking_ref, date_of_booking from booking_master'

exec(@sql)

Please help me. It's urgent

Thanx a million

Sachin

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2003-04-17 : 09:47:19

Please help Me nay body :(

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 10:38:21

Why can't you use:


Declare @TempItems table (ID int IDENTITY,booking_ref bigint,date_of_booking datetime) 

INSERT INTO @TempItems (booking_ref, date_of_booking) select distinct booking_ref, date_of_booking from booking_master

Brett

8-)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2003-04-17 : 11:10:26

Hi Breet,

It's Because "select distinct booking_ref, date_of_booking from booking_master" is the parameter to the stored procedure. And this is made dynamically in the client(ASP), after checking lot of conditions, authentication. If the user has don't have rights then the statement will be different. So I can't check this in the sp. This is the reason I am sending it as parameter. But when I am trying to execute the statement and insert the result into a table data type, it gives me error. please help.

thanx in advance
Sachin

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 11:16:43

Why don't you post the called sproc...can't really see the problem (or understand it). I mean How do you know to create a table variable if you have the SQL statement be dynamic? Is it just the predicate that's dynamic?

Please post.

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-17 : 21:20:49

Why are you even passing a dynamic SQL string to a stored procedure just to have it execute the string? That's completely pointless.

In any case, you can fix your problem very easily by using a temp table instead of a table variable.

Edited by - robvolk on 04/17/2003 21:21:14

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2003-04-18 : 02:19:49

Hi Breet & Robovolk,

The Code I am using in the sp is Below :-

Alter PROCEDURE sp_PagedItems(@Page int,@RecsPerPage int,@sql varchar(5000))
AS
SET NOCOUNT ON
Declare @TempItems table (ID int IDENTITY,booking_ref bigint,date_of_booking datetime)

set @sql='INSERT INTO @TempItems (booking_ref, date_of_booking) '+ @sql
exec(@sql)

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

Select @FirstRec, @LastRec

SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM @TempItems TI
WHERE TI.ID >= @LastRec
)
FROM @TempItems
WHERE ID > @FirstRec AND ID < @LastRec
SET NOCOUNT OFF
GO

Exec sp_PagedItems 1,12,'select distinct booking_ref, date_of_booking from booking_master'

After Executing the sp I get the following error :- "Must declare the variable '@TempItems'."

I am using this sp for paging the records in SQL server. I could have used the temp tables. But since temp tables are slower and lead to many database locking issues, I am not using the same. Please tell me if u could find any way to execute a dynamic sql statment with a table data type.

thanx in advance
Sachin

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-18 : 03:29:35

You Cannot access @tempItems table with in the dynamic sql unless it is declared inside itself as shown below

ALTER PROCEDURE sp_PagedItems(@Page int,@RecsPerPage int,@sql varchar(5000))
AS
SET NOCOUNT ON

SET @SQL=' Declare @TempItems table (ID int IDENTITY,booking_ref bigint,date_of_booking datetime);
INSERT INTO @TempItems (booking_ref, date_of_booking) '+ @sql +'; SELECT * FROM @TEMPITEMS'
exec(@sql)
GO
Exec sp_PagedItems 1,12,'select distinct booking_ref, date_of_booking from booking_master'

BTW why you are doing this

you are creating a standard temporary table and tying to insert the rows which are build dynamic that doesnt make sence.. i don't understand.

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Edited by - samsekar on 04/18/2003 03:30:55

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2003-04-18 : 03:55:40

thanx a million SEKAR

It worked :)

Sachin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources