views with parameters involved

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 2005 Forums

Transact-SQL (2005)

views with parameters involved

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-02 : 12:46:20

Hi,

I have done a stored procedure but happend to know that people calling my database cannot access procedures only views. But my select query requires to parameters (two dates) in order to get the correct data.

How can I solve the problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 12:50:35

Give permissions to users to execute stored procedures in your db.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-04-02 : 13:00:25

What a table valued function instead of the view

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-02 : 13:23:26

Thanks for your input, but it seems that none of your mentioned solutions are allowed here at work. I will have tackle this with a webservice.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-02 : 13:24:28

this doesn't work?
select * from yourView where yourParamsCondition

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-24 : 09:15:42

spirit1,

How can it work when the parameter is in a nested select statement. I will try to shorten the query:


SELECT d.depositid
	FROM tbldeposit d
	WHERE NOT EXISTS
	(
	SELECT distinct A.depositid
	FROM
	 tblAccountTransaction T
	 INNER JOIN tblAccount A
		ON A.[AccountId] = T.[AccountId]
	WHERE D.depositid = A.depositid
	AND T.transactiondate BETWEEN isNull('2003-02-18',(SELECT MIN(OrderDate) FROM tblclientorder)) AND isNull('2008-03-25', (SELECT MAX(OrderDate) FROM tblclientorder))
	) 
	UNION
	(
	SELECT d.depositid
	FROM tbldeposit d
	WHERE NOT EXISTS
		(SELECT distinct depositid 
		FROM tblclientorder co
		WHERE d.depositid = co.depositid
		AND co.orderdate BETWEEN isNull('2003-02-18',(SELECT MIN(OrderDate) FROM tblclientorder)) AND isNull('2008-03-25', (SELECT MAX(OrderDate) FROM tblclientorder))
		) 
	)

The following dates should be possible to change to whatever date: '2003-02-18' and '2008-03-25'

How is it possible if I make a view called x out of the above select-statement and afterwards try to select the view with respect to a "where" for the dates ?

Select *
from view x
where ??

Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-24 : 09:41:46

nope. no can do.
you'll have to use a table valued function with parameteres for this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources