[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

passing parameters to FUNCTION

Posted on 2005-05-03
13
Medium Priority
?
2,747 Views
Last Modified: 2011-10-03
i have a sql statements and in that sql i have to call a function and function expects 2 parameters but how do i pass parameters to function in my sql statements?
0
Comment
Question by:allday
13 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13922399
To pass parameters to functions:

dbo.YourFunction ( 'Parameter1', 1000 )

So in your SQL statement:

SELECT dbo.YourFunction (Field1, Field2) as FunctionOut, name, Address
FROM YourTable
0
 
LVL 9

Expert Comment

by:paelo
ID: 13922402
SELECT y.*, dbo.udfSomeFunction(y.id, ...)
FROM dbo.yourtable y

-Paul.
0
 

Author Comment

by:allday
ID: 13922414
i did tried passing it but im getting this error:

Server: Msg 155, Level 15, State 1, Line 8
'con_id' is not a recognized OPTIMIZER LOCK HINTS option.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13922426
Don't you hate it when that happens.  

Now can you post something useful, like the function and how you are callling it.
0
 

Author Comment

by:allday
ID: 13922437
SELECT    
dbo.view1.id1, dbo.view1.sub_id, dbo.view2.name,
dbo.view1.holdername, dbo.view1.status,
dbo.view1.service_type, myfunction2.admin_name
FROM        
dbo.myfunction2() myfunction2
INNER JOIN
dbo.myfunction1(id1,sub_id)
myfunction1 ON myfunction2.admin_id = myfunction1.admin_id
INNER JOIN
dbo.view1
INNER JOIN
dbo.view2 ON
dbo.view1.id1 = dbo.view2.id1
AND
dbo.view1.sub_id = dbo.view2.sub_id ON
myfunction1.id1 = dbo.view2.id1
AND
myfunction1.sub_id = dbo.view2.sub_id
0
 

Author Comment

by:allday
ID: 13922439
dbo.myfunction1(id1,sub_id)   <<<<< function passing values
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13922493
Can we have the whole function (CREATE FUNCTION dbo.myfunction1 etc.) including the parameters?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13922503
This will also tell us if you are using a scalar function or whether it is returning a table.
0
 

Author Comment

by:allday
ID: 13922504
as long as its fast and work i don't mind :)
0
 

Author Comment

by:allday
ID: 13922507
it will return only 1 row which is what im looking
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13922528
Ok, let me explain to you where I am coming from.  

You post an error message that reads:
Server: Msg 155, Level 15, State 1, Line 8
'con_id' is not a recognized OPTIMIZER LOCK HINTS option.

Now I cannot find any trace of con_id in your code, nor have you explained what it is and since it is pretty late in the day here, my mind reading capabilities are somewhat diminished.  That is why I need to see the function "myfunction1" (it also would not hurt if you also posted the function myfunction2)

Does that help you understand what I need and why?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13926837
You cannot join to a function that yields a result based on the value of a column in some table.  Think about it: each value fed into the function could return a different result set, and there could be (theoretically) millions of input rows, so you could be trying to do millions of joins.  For example:

SELECT a.col1
FROM tableA a
INNER JOIN dbo.someTableFunction(a.col2) AS b ON a.col2 = b.col2

For *every* value in a.col2, SQL would generate a new result set.


If it returns only a single result, you can use a subquery in the main SELECT:

SELECT a.col1, (SELECT TOP 1 col3 FROM dbo.someTableFunction(a.col2)) AS funcValue
FROM tableA a

--"TOP 1" is just to make sure that only 1 value is returned, and that SQL "knows" that,
--so it can end the function as quickly as possible.
0
 
LVL 7

Accepted Solution

by:
nisarkhan earned 2000 total points
ID: 13930805
i would do like this:

declare @id1 as integer
declare @sub_id as integer

select @id = select id from view1
select @sub_id = select sub_id from view1

SELECT    
dbo.view1.id1, dbo.view1.sub_id, dbo.view2.name,
dbo.view1.holdername, dbo.view1.status,
dbo.view1.service_type, myfunction2.admin_name
FROM        
dbo.myfunction2() myfunction2
INNER JOIN
dbo.myfunction1(@id1,@sub_id)
myfunction1 ON myfunction2.admin_id = myfunction1.admin_id
INNER JOIN
dbo.view1
INNER JOIN
dbo.view2 ON
dbo.view1.id1 = dbo.view2.id1
AND
dbo.view1.sub_id = dbo.view2.sub_id ON
myfunction1.id1 = dbo.view2.id1
AND
myfunction1.sub_id = dbo.view2.sub_id
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question