use variables with a create view? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

use variables with a create view?

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2006-01-24 : 18:33:45

Hi all,

trying to do something like;

DECLARE @From Int, @To Int
Set @From = 200101
Set @To = 200512

Create View vw_Unique_Pds As
Select Distinct InvGroupNme, InvDivNme, DtePd_Unique
From Tbl_Txn_UPC,IDX_Master.dbo.Tbl_DtePd_Unique
Where DtePd_Unique >= @From and DtePd_Unique <= @To

with the Create view part it doesn't work..Can I use variables with the create view?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 19:18:43

No. Use Stored Procedure instead

----------------------------------
'KH'
I do work from home but I don't do homework

Job
Yak Posting Veteran

69 Posts

Posted - 2006-01-25 : 00:23:05

Not sure how to do this exactly...

Something like:

CREATE PROCEDURE Unique_Pds
(@From Int,
@To Int,
)
AS
Create View vw_Unique_Pds As
Select Distinct InvGroupNme, InvDivNme, DtePd_Unique
From Tbl_Txn_UPC,IDX_Master.dbo.Tbl_DtePd_Unique
Where DtePd_Unique >= @From and DtePd_Unique <= @To

But this doesn't work....

Thanks for the help..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-25 : 00:28:51

[code]CREATE PROCEDURE Unique_Pds
(
@From Int,
@To Int, -- you got an extra comma there
)
AS
~~Create View vw_Unique_Pds As~~
Select Distinct InvGroupNme, InvDivNme, DtePd_Unique
From Tbl_Txn_UPC,IDX_Master.dbo.Tbl_DtePd_Unique
Where DtePd_Unique >= @From and DtePd_Unique <= @To[/code]

----------------------------------
'KH'
Happy Chinese New Year

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-25 : 00:32:16

Remove create view statement..

try this

CREATE PROCEDURE Unique_Pds
(@From Int,
@To Int
)
AS
Select Distinct InvGroupNme, InvDivNme, DtePd_Unique
From Tbl_Txn_UPC,IDX_Master.dbo.Tbl_DtePd_Unique
Where DtePd_Unique >= @From and DtePd_Unique <= @To

go

u can execute it as
exec Unique_Pds 1,2

u can also create a user defined function as then u can use output in select statement

CREATE Function Unique_Pds (@From Int,@To Int)
RETURNS table
AS
return
(
Select Distinct InvGroupNme, InvDivNme, DtePd_Unique
From Tbl_Txn_UPC,IDX_Master.dbo.Tbl_DtePd_Unique
Where DtePd_Unique >= @From and DtePd_Unique <= @To
)
GO

and execute it as
select * from Unique_Pds(1,2)

Refer BOL for more info

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 01:36:12

You cant have a View having parameters. Stored Procedure is the one you should use as suggested

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources