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)
 cant use parameter for Dynamic SQL???

Author  Topic 

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-02-12 : 06:40:43
I have this procedure

CREATE PROCEDURE dbo.RJ_PriceRequest
@fromdate SMALLdatetime, @todate SMALLdatetime, @ecity varchar(30), @ewkind varchar(40), @NoOfPeople1 int, @NoOfPeople2 int, @NoOfPeople3 int, @NoOfRooms smallint
AS
declare @i smallint, @sql varchar(400)
set @i=1
set @sql=''
WHILE @i<=@NoOfRooms
BEGIN
IF @NoOfrooms >3
set @NoOfrooms=3
IF @i>1
set @sql= @sql + ' UNION ALL '
set @sql= @sql + '(select * from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople' + CAST(@i AS char(1)) + '))'
set @i=@i+1
END
IF @i>2
set @sql= 'select sid,SUM(AVEp) as price from ( ' + @sql + ') as g group by sid'
PRINT @sql
EXEC(@sql)
GO

I write a dynamic sql and run it at the last line, but it can't read the parameters such as @fromdate, @todate....

please advise

Thanks

**Jonathan**

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-12 : 06:50:34
The parameters must be added per VALUE, not per name.
Put some extra single ' into the code.

As always when dealing with dynamic sql, please have a look at the printed statement (print @sql) and see what is wrong.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-12 : 06:51:02
exec() can't be parameterize, use sp_executesql instead.

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-12 : 06:54:44
And you don't need dynamic sql for this.
CREATE PROCEDURE dbo.RJ_PriceRequest
(
	@fromdate SMALLdatetime
	@todate SMALLdatetime,
	@ecity varchar(30),
	@ewkind varchar(40),
	@NoOfPeople1 int,
	@NoOfPeople2 int,
	@NoOfPeople3 int,
	@NoOfRooms smallint
)
AS

SET NOCOUNT ON

DECLARE	@Temp TABLE ([sid] INT, [avep] VARCHAR)

IF @NoRooms >= 1
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1)

IF @NoRooms >= 2
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople2)

IF @NoRooms >= 3
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople3)

select		sid,
		SUM(AVEp) as price
from		@temp
group by	sid

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-02-12 : 20:39:35
quote:
Originally posted by Peso

And you don't need dynamic sql for this.
CREATE PROCEDURE dbo.RJ_PriceRequest
(
	@fromdate SMALLdatetime
	@todate SMALLdatetime,
	@ecity varchar(30),
	@ewkind varchar(40),
	@NoOfPeople1 int,
	@NoOfPeople2 int,
	@NoOfPeople3 int,
	@NoOfRooms smallint
)
AS

SET NOCOUNT ON

DECLARE	@Temp TABLE ([sid] INT, [avep] VARCHAR)

IF @NoRooms >= 1
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1)

IF @NoRooms >= 2
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople2)

IF @NoRooms >= 3
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople3)

select		sid,
		SUM(AVEp) as price
from		@temp
group by	sid

Peter Larsson
Helsingborg, Sweden


will creating a temp table affect performance signifigantly? Because this thing needs to be real time and online all the time. Please advise


**Jonathan**
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-02-12 : 20:59:15
An additional question based on the current one. I had the result for UNION ALL, but how should I do if I want to have intersect of them? (I am using sql 2k)

Thanks

**Jonathan**
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-12 : 21:36:36
quote:
will creating a temp table affect performance signifigantly ?

No.

quote:
An additional question based on the current one. I had the result for UNION ALL, but how should I do if I want to have intersect of them? (I am using sql 2k)

What kind of intersect ? Please explain more, preferably with some sample data and result.



KH
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-02-12 : 21:41:38
In my original coding

@fromdate SMALLdatetime, @todate SMALLdatetime, @ecity varchar(30), @ewkind varchar(40), @NoOfPeople1 int, @NoOfPeople2 int, @NoOfPeople3 int, @NoOfRooms smallint
AS
declare @i smallint, @sql varchar(400)
set @i=1
set @sql=''
WHILE @i<=@NoOfRooms
BEGIN
IF @NoOfrooms >3
set @NoOfrooms=3
IF @i>1
set @sql= @sql + ' UNION ALL '
set @sql= @sql + '(select * from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople' + CAST(@i AS char(1)) + '))'
set @i=@i+1
END
IF @i>2
set @sql= 'select sid,SUM(AVEp) as price from ( ' + @sql + ') as g group by sid'

I used Union to combine all the data from 3 table into one (if @NoOfrooms=3)but what if I want the intersect of the 3 table, what should I do? (I am using sqk 2k)

**Jonathan**
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-12 : 21:44:28
Sorry, still don't understand. Please post your table structure, sample data and result that you want.

And, why goes back to Dynamic SQL ? Peter's solution is not working for you ?

KH
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-02-12 : 22:17:28
Peter s code works perfect for me.
I was using UNION ALL in my original code and Peter s code achieve the same result too.
So I am just wondering how to modify Peter''s code so that only the intersection of the tables is aggregated into @Temp



**Jonathan**
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-12 : 22:22:30
You still have not explain what is intersection that you want.

Or do you mean you don't want the duplicate sid ? see the code in red

CREATE PROCEDURE dbo.RJ_PriceRequest
(
	@fromdate SMALLdatetime
	@todate SMALLdatetime,
	@ecity varchar(30),
	@ewkind varchar(40),
	@NoOfPeople1 int,
	@NoOfPeople2 int,
	@NoOfPeople3 int,
	@NoOfRooms smallint
)
AS

SET NOCOUNT ON

DECLARE	@Temp TABLE ([sid] INT, [avep] VARCHAR)

IF @NoRooms >= 1
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1) a
   where   not exists (select * from @Temp x where x.sid = a.sid)

IF @NoRooms >= 2
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople2)
   where   not exists (select * from @Temp x where x.sid = a.sid)

IF @NoRooms >= 3
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople3)
   where   not exists (select * from @Temp x where x.sid = a.sid)


select		sid,
		SUM(AVEp) as price
from		@temp
group by	sid


KH
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-02-12 : 23:07:55
Sorry my bad

if you look into Peter''s code, you will find out if @NoOfRooms=1 then
INSERT @Temp
select [sid],
[avep]
from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1)
will be inserted into @Temp

If @NoOfRooms=2 then 2 of them will be inserted into @Temp and so on

INSERT @Temp
select [sid],
[avep]
from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1)
INSERT @Temp
select [sid],
[avep]
from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople2)

If I call the first inserted as TABLE1 and the second one inserted as TABLE2
Above code will UNION TABLE1 and TABLE2 into @Temp.
Now I would like to have intersect of TABLE1 and TABLE based on their primary key sid.
That is to say, if TABLE1 has sid 1,2,3,4
and TABLE2 has sid=3,4 then the desired result I want will be only sid=3,4
Hope this will clear your doubt.
Thank you

**Jonathan**
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-12 : 23:15:58
[code]
select *
from
(
select [sid],
[avep]
from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1)
where @NoRooms >= 1
) t1
inner join
(
select [sid],
[avep]
from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople2)
where @NoRooms >= 2
) t2 on t1.sid = t2.sid
inner join
(
select [sid],
[avep]
from RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople3)
where @NoRooms >= 3
) t3 on t1.sid = t3.sid
[/code]

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-13 : 00:54:04
There is no need to rewrite query. Just add an HAVING clause at the end
CREATE PROCEDURE dbo.RJ_PriceRequest
(
	@FromDate SMALLDATETIME
	@ToDate SMALLDATETIME,
	@ECity VARCHAR(30),
	@EwKind VARCHAR(40),
	@NoOfPeople1 INT,
	@NoOfPeople2 INT,
	@NoOfPeople3 INT,
	@NoOfRooms SMALLINT
)
AS

SET NOCOUNT ON

DECLARE	@Temp TABLE ([sid] INT, [avep] MONEY)

IF @NoRooms >= 1
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1)

IF @NoRooms >= 2
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople2)

IF @NoRooms >= 3
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople3)

SELECT		sid,
		SUM(AVEp) as price
FROM		@Temp
GROUP BY	sid
HAVING		COUNT(*) >= CASE WHEN @NoOfRooms > 3 THEN 3 ELSE @NoOfRooms END


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-02-13 : 01:33:29
Peter, you are God-like programmer. You are really awesome~~!!

If there is chance, do you accept out-sourcing case?

**Jonathan**
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-13 : 02:20:04
quote:
Originally posted by khtan

select	*
from
...
inner join
...
inner join
Khtan, I think this will be troublesome if @NoOfRooms is less than 3.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-13 : 02:55:15
quote:
Originally posted by Peso

quote:
Originally posted by khtan

select	*
from
...
inner join
...
inner join
Khtan, I think this will be troublesome if @NoOfRooms is less than 3.


Peter Larsson
Helsingborg, Sweden


Oh yeah. Missed that.

KH
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-03-01 : 05:49:31
quote:
Originally posted by Peso

And you don't need dynamic sql for this.
CREATE PROCEDURE dbo.RJ_PriceRequest
(
	@fromdate SMALLdatetime
	@todate SMALLdatetime,
	@ecity varchar(30),
	@ewkind varchar(40),
	@NoOfPeople1 int,
	@NoOfPeople2 int,
	@NoOfPeople3 int,
	@NoOfRooms smallint
)
AS

SET NOCOUNT ON

DECLARE	@Temp TABLE ([sid] INT, [avep] VARCHAR)

IF @NoRooms >= 1
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople1)

IF @NoRooms >= 2
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople2)

IF @NoRooms >= 3
	INSERT	@Temp
	select	[sid],
		[avep]
	from	RJ_Pricerequest_code(@fromdate, @todate, @ecity, @ewkind, @NoOfPeople3)

select		sid,
		SUM(AVEp) as price
from		@temp
group by	sid

Peter Larsson
Helsingborg, Sweden


Dear Peter,
Scenerio A:Say I have another SP(caller) and it makes use of the result from the above SP(callee).
Scenerio B:Say I convert the stored procedure above to UDF(callee). And Now I write an UDF(caller) and make use of the data from the callee UDF.

Which Scenerio will perform better?

Thanks


**Jonathan**
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-03-01 : 12:14:37
It's a matter of taste.
Performance wise I think they will be equal.

But functions are more useful. You can use them a any other table, with SPs you can't.
And with SQL Server 2005 you can even use CROSS APPLY with functions!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

wangyc77
Yak Posting Veteran

65 Posts
Posted - 2007-03-01 : 20:37:25
Thanks a lot

**Jonathan**
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site