UDF in Stored Procedure where ..

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

General SQL Server Forums

New to SQL Server Programming

UDF in Stored Procedure where ..

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-06 : 17:17:08

I have a stored procedure. Part of stored procedure's nested query must get a value from another function (UDF ) { dbo.maxproc } But it wont run ..I need insert the integer values for the between part of the where clause.

WHERE (dbo.Customer.Mode = 0) AND (dbo.Customer.something BETWEEN 3000 AND 3140)

Something like below ... but it wont run .... ideas ??

WHERE (dbo.Customer.Mode = 0) AND (dbo.Customer.amount BETWEEN (dbo.maxproc-25) and dbo.maxproc

Thanks !!!

andrewcw

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-06 : 19:44:28

OK - seems like I need to use dbo.maxproc() - add parenthesis.
In a simple test case dbo.maxproc() returns 3140 - tested from VSudio pane.
And very rapidly
But in simple query like this, SQL times out !!!

SELECT Customer, customerNo
FROM dbo.Customer
WHERE (customerNo = 3140)

BUT ... this times out... Is this expected ???

SELECT Customer, customerNo
FROM dbo.Customer
WHERE (customerNo = dbo.maxproc())

Ideas .... Thanks !

andrewcw

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 02:31:52

May be its happening, because dbo.maxproc() ie getting called for each record.
try some thing like this.
SELECT Customer, customerNo
FROM dbo.Customer, (select dbo.maxproc() as m) maxp
WHERE (customerNo = maxp.m)
--where customerNo between maxp.m-25 and maxp.m

Rahul Shinde

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-08 : 12:08:31

I dont know why it timed out, but Monday & today the server did not timeout and the response is very quick

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources