Howto: Calling Sp_executesql From A Function ?
Jul 20, 2005
I want to execute a dynamically generated sql-statement
from inside an user-defined-function. Calling functions and
extended stored-procs is allowed so I tried sp_executesql
as well as sp_prepare/sp_execute ....
but both fail with an error 'only functions and extended stored-procs
may be called from inside a function.'
any idea where I might be wrong ?
thx in advance,
Joerg
--
************************************************** ***********
Joerg Clausmeyer
Medizinische Informatik und Datenmanagement
CHARITE - Universitätsmedizin Berlin
************************************************** ***********
View 3 Replies
ADVERTISEMENT
Mar 31, 2004
something like max,min?
help please!!!
View 5 Replies
View Related
Jul 23, 2005
This is driving me crazy.I need to create a UDF that would return a TRUE/FALSE (bit) value basedon a comparison it does.CREATE FUNCTION dbo.SelectedByApplication(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint)RETURNS BIT ASBEGINDECLARE @SQLNVARCHAR(1000)DECLARE @Param NVARCHAR(500)DECLARE @Result intSET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + '] '+'WHERE [' + @ColumnToCompare + '] = @ValueInTable ANDApplicationID = @ApplicationID'SET @Param = N'@result int out, @ValueInTable int, @ApplicationIDint'EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,@ApplicationIDif @result > 0return 1return 0ENDAll I need the function to do is fill in a column based on whetherthere is a relation between a list of data and the item. I'm trying touse it in the following query:SELECT *, EXEC dbo.SelectedByApplication(4, 'IPM_Application_DataType','DataTypeID', DataTypeID)FROM IPM_DataType DTThe idea is to make this call and then be able to populate a list ofcheckboxes based on the information it returns. It should returnsomething similiar to:Column1 Column2 UDFColumn1 SomeValue 02 OtherValue 13 DifferentValue 04 LastValue 1After reading some of the posts and discovering you can't executedynamic SQL in a UDF I decided to split the function into a functionand stored procedure:CREATE FUNCTION dbo.SelectedByApplication(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint)RETURNS BIT ASBEGINDeclare @Result INTEXEC DynamicCompare @ApplicationID, @TableToCheck, @ColumnToCompare,@ValueInTable, @Resultif(@Result > 0)return 1return 0ENDCREATE PROCEDURE dbo.DynamicCompare(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint,@Resultint out)ASDECLARE @SQLNVARCHAR(1000)DECLARE @Param NVARCHAR(500)SET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + ']' +'WHERE [' + @ColumnToCompare + '] = @ValueInTable ANDApplicationID = @ApplicationID'SET @Param = N'@result int out, @ValueInTable int, @ApplicationIDint'EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,@ApplicationIDI get the same error message about only being able to execute functionsand extended stored procedures in a user defined function.Does anyone have any ideas as to how I can dynamically execute thisquery? The reason I say dynamic is I need this same comparison forabout 25 different tables. Thanks!
View 2 Replies
View Related
Feb 22, 2007
Hello,
I am trying to call a SQL Function in C#, as I want to get the value binded back to the column in a datagrid.
Any answers will be appreciated ...............Urgent.
:)
View 4 Replies
View Related
Feb 26, 2008
I'm calling a function with its full qualifiers.
That means servername.dbname.schemaname.functionname
I'm getting an error like this
Invalid column name 'servername'.
Is it possible to call a function name with its server name?
Thanks
Somu
View 4 Replies
View Related
Aug 30, 2006
Can I call a Stored Procedure in a function?
I created a SP:
CREATE PROCEDURE dbo.test_sp AS
BEGIN
select * from STOCK
END
GO
and I tried to create a function like that:
CREATE FUNCTION dbo.test_fn ()
RETURNS int AS
BEGIN
declare @a int
select @a = sto_id from dbo.test_sp
return @a
END
as seen the function uses the test_sp.but that gives syntax error. How can I do this?
thanks.
View 4 Replies
View Related
Feb 19, 2003
I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.
View 2 Replies
View Related
Mar 10, 2004
Does anyone know if you can call an Access function from DTS?
I'm trying to delete data from an Access database, Compact the database, and load new data. My snag is calling a function in Access to compact the database.
Suggestions?
View 2 Replies
View Related
Jun 23, 2008
Hi
I have created a user function
the only way i can use this function is by specifying dbo. as prefix of the function
example
SELECT column1, dbo.MyFunc(column2)
FROM mytable
what i want is something like this
SELECT column1, MyFunc(column2)
FROM mytable
is this possible?
View 4 Replies
View Related
Mar 11, 2008
I have the next Function:
CREATE FUNCTION F_StoreName
(
@strTienda as varchar(3)--Numero de Tienda
)
RETURNS VARCHAR(200)
AS
BEGIN
declare
@strStoreName as varchar(30),
@strSQLString nvarchar(500),
@strParmDefinition nvarchar(500)
--set @strTienda='003'
SET @strSQLString = N'select @StoreName = max(nombre) ' +
' from ' + master.dbo.fGetServerName('bdSupport') + 'bdSupport..tbTiendas with (noLock) ' +
' where notienda= ' + @strTienda
SET @strParmDefinition = N'@StoreName varchar(30) OUTPUT';
--print @strSQL
EXECUTE sp_executesql @strSQLString, @strParmDefinition, @StoreName=@strStoreName OUTPUT;
RETURN @strStoreName
END
When I call this function:
select dbo.F_StoreName('002') as x
It sent me next error:
Only functions and extended stored procedures can be executed from within a function.
View 1 Replies
View Related
Jul 23, 2005
I'm faced with a situation where I will need to calculate a column fora resultset by calling a component written as a VB6 DLL, passingparameters from the resultset to the component and setting (orupdating) a column with the result. I thought that perhaps the bestway out would be to create a UDF that passes the parameters to the VBcomponent using the sp_oa* OLE stored procs.For a test, I created an ActiveX DLL in VB6 (TestDLL) with someproperties and methods. I then created a function that creates theobject, sets the required properties and returns a result. I usesp_oaDestroy at the end of the function to remove the objectreference. The function seems to work surprisingly well except for asmall problem; when I use the function to calculate a column for aresultset with more that one row, the DLL appears to stay locked up("the file is being used by another person or program"). This leavesme with the impression that the object reference is not beingdestroyed. I have to stop/restart the SQL Server in order to free theDLL.Question:Is the UDF approach the best way? I don't like the idea of creatingand destroying the object at every pass which is what the UDF does.As an alternative, I suppose that I could have a single SP where Icreate the OLE object once, loop through the result set with a cursorand do my processing/updating, then close the OLE object. I must saythat I'm not too fond of that approach either.Thanks for your help,Bill E.Hollywood, FL(code is below)___________________________--Test the functionCreate Table #TestTable(Field1 int)INSERT INTO #TestTable VALUES (1)INSERT INTO #TestTable VALUES (2)SELECT Field1, dbo.fnTest(Field1,4) AS CalcColFROM #TestTableDrop Table #TestTable___________________________CREATE FUNCTION dbo.fnTest/*This function calls a VB DLL*/(--input variables@intValue1 smallint,@intValue2 smallint)RETURNS integerASBEGIN--Define the return variable and the counterDeclare @intReturnValue smallintSet @intReturnValue=0--Define other variablesDeclare @intObject intDeclare @intResult intDeclare @intError intSet @intError=0If @intError = 0exec @intError=sp_oaCreate 'TestDLL.Convert', @intObject OUTPUTIf @intError = 0exec @intError = sp_OASetProperty @intObject,'Input1', @intValue1If @intError = 0exec @intError = sp_OASetProperty @intObject,'Input2', @intValue2If @intError = 0exec @intError = sp_oamethod @intObject, 'Multiply'If @intError = 0exec @intError = sp_oagetproperty @intObject,'Output',@intReturnValue OutputIf @intError = 0exec @intError = sp_oadestroy @intObjectRETURN @intReturnValueEND
View 8 Replies
View Related
Oct 17, 2006
I was wondering if anyone knows the best way to call a function that is located in database on a different server then the database that is calling the function. Is it possible to do this with out linked servers? I am running SQL Server 2000. Any help in this matter would be greatly appreciated.
View 1 Replies
View Related
Nov 27, 2007
I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running.
I need help on how can I capture insert trigger event VS 2003 console application?
View 2 Replies
View Related
Aug 28, 2007
hai,
the problem is - I have created a userdefined function using SQL 2000
create function getfulldate (@date varchar(10))returns datetimeasbegindeclare @getfulldate datetime set @getfulldate = dateadd (mi,55,@date)
return @getfulldateend
and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp
This works fine and what I need was, I need to invoke the user-defined function like
select *, getfulldate('2006-05-03') from emp that is, without using "dbo".
If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.
So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.
anyone guide me, thanks!
View 3 Replies
View Related
Oct 26, 2007
Hello all,
I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions. My function works fine, but when I try to call the function from the stored procedure I get an error.
I'm going to try explain the thought process behind what I'm doing. Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool. The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor). So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.
Example of tool SELECT statement:SELECT tool_number, tool_description
FROM tool
WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string)
This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name.
Function code (removed some wrapping code for simplicity):SELECT manufacturer_id
FROM manufacturer
WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT *
FROM UDFmanufacturer_SearchName('mol') SELECT *
FROM tool
WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT *
FROM ato_tool
WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol'))
I'm stuck. I haven't been able to find anything that shows me where I'm going wrong. Any thoughts or suggestions are appreciated. Thanks,Jay
View 4 Replies
View Related
May 9, 2008
This is an issue with calling a stored procedure, which calls a function.this input parameter of the function is a list nvarchar.where i am giving the input like : 1,2,3,4,8 here for the corresponding id's 1,2,3,4,8i wanna take all the details and displaying it ina crystal report........ CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX)) RETURNS @tbl TABLE (number int NOT NULL) ASBEGIN DECLARE @pos int, @nextpos int, @valuelen int SELECT @pos = 0, @nextpos = 1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (number) VALUES (convert(int, substring(@list, @pos + 1, @valuelen))) SELECT @pos = @nextpos END RETURNEND create proc [dbo].[Comparison](@ProductVersionID VarChar(50))asbeginselect PV.Productversionname, F.FeatureID, F.Title, F.description, F.Modifieddate,PVF.IsPresent, FG.Title from features F,ProductVersionFeatures PVF, productversion PV, Featuregroup FG where F.FeatureID = PVF.FeatureID and PVF.productversionid = PV.ProductVersionID and iter$simple_intlist_to_tbl(@ProductVersionID) i ON PVF.productversionid = i.numberendThis is my Storeprocedure, where i am calling a function in this stored procedure but when i am trying to execute the Sp, i am facing an error liek this :Msg 195, Level 15, State 10, Procedure Comparison, Line 4'iter$simple_intlist_to_tbl' is not a recognized built-in function name. can any body please help me why this is hapenig, how can i solve this issue
View 11 Replies
View Related
Nov 7, 2007
I need to write SSIS package with 5 script task. I have one function which need to be called from each SSIS one by one. I wrote the that function at first SSIS task. For example:
Public Function Add() As Integer
Dim i, j As Integer
i = 10
j = 20
Return (i + j)
End Function
and I can call this function inside 1st SSIS task but how can I call this function on rest of 4 script task?
Thanks
Sanjeev
View 2 Replies
View Related
Jul 20, 2005
Is it possible to call a user-defined function without prefixing itwith 'dbo.' within a SELECT clause somehow? Just curious; it's not abig issue but just a stylistic one for me.Thanks!Joel Thornton ~ <groups@joelpt.eml.cc>
View 1 Replies
View Related
May 25, 2006
I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000
Call to the following function returns an error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'srv2000'.
select [srv2000].db_test.dbo.F_TEST()
View 4 Replies
View Related
Apr 11, 2008
Hi All
Yesterday Peso was gracious enough to help me with creating function/views/sp's
I took those examples and extended what had from excel into function in SQL
however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?
Here are excerpts two functions I have:
We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)
and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)
As you can see the first part of the both query are simlar. In particular the:
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
and also the variables @StartDate and @EndDate.
In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?
Thank you!
View 2 Replies
View Related
Apr 19, 2006
I have the following procedure, that calls a Padding function to pad characters to a field.
Here is what the procedure looks like
Code:
CREATE PROCEDURE [dbo].[Pad_Left]
@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT
AS
DECLARE @Query Varchar(5000)
SET @Query = 'UPDATE ' + @Table + '
SET ' + @Column + ' = dbo.Function_PadLeft(' + @Column + ', ''' + @PadChar + ''', ' + @PadToLen + ')'
EXECUTE(@Query)
GO
When I run this I get the error
Server: Msg 245, Level 16, State 1, Procedure Pad_Left, Line 13
Syntax error converting the varchar value 'UPDATE Lincoln
SET baths = dbo.Function_PadLeft(baths, '0', ' to a column of data type int.
But when I just run this query, it works
Code:
CREATE PROCEDURE [dbo].[Pad_Left]
@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT
AS
UPDATE Lincoln
SET Baths = dbo.Function_PadLeft(Baths, '0', 4)
GO
Why would one work but not the other? I don't understand, as they are the same thing, just one calls the function dynamically?
I must be missing something very obvious
Thanks for any help!
View 2 Replies
View Related
Sep 29, 2007
I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)
Short list of process flow:
1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER
2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient
3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient
4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page
5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.
<FLOW WITH CODE AND FUNCTIONS :>
This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.
This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.
This is code in Form.asp
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check
Postback Type
'We need to load up vendors associated with the current client.
'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------
'If the call returns 1, then the employee has access.
'Otherwise, just write out "Access to this client is denied."
'CALL SP - Not sure what parameters need to go with it or its syntax
Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)
'When it returns can check it here........
if ab_HasAccessToClient result is a 1 then
'boolean would be 1 so show panel
Else
'boolean would be 0 so show access denied
'allow them to go back to the original page.
end if
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
ON SQL SERVER: Stored Procedure
----------------------------------------------------------
--------------------------------
rx_sp_HasAccessToClient
CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]
@EmployeeID INT,
@ClientSSN varchar(50),
@ReturnBitValue = OUTPUT
/*
' Parameters here passed via call from Form.asp - not sure what is passed
yet.
*/
AS
set nocount on
/*
Written by Mike Belcher 9/27/2007 for Form.asp
'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.
'Gets return bit and passes that back to the call from Form.asp
*/
GO
----------------------------------------------------------
--------------------------------
ON SQL SERVER: User-Defined Function
----------------------------------------------------------
--------------------------------
ab_HasAccessToClient
CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))
@ClientSSN varchar(50),
@EmployeeID,
@ReturnBitValue = OUTPUT
AS
SELECT 1
FROM tblEmployeesClients ec
INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN
INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName
WHERE e.EmployeeID= @EmployeeID
AND c.InActiveClient=0
AND c.ClientSSN = @ClientSSN
'Some Code here to save result bit ..
RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient
----------------------------------------------------------
--------------------------------
</FLOW WITH CODE AND FUNCTIONS :>
View 5 Replies
View Related
Sep 29, 2007
I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)
Short list of process flow:
1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER
2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient
3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient
4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page
5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.
<FLOW WITH CODE AND FUNCTIONS :>
This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.
This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.
This is code in Form.asp
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check
Postback Type
'We need to load up vendors associated with the current client.
'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------
'If the call returns 1, then the employee has access.
'Otherwise, just write out "Access to this client is denied."
'CALL SP - Not sure what parameters need to go with it or its syntax
Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)
'When it returns can check it here........
if ab_HasAccessToClient result is a 1 then
'boolean would be 1 so show panel
Else
'boolean would be 0 so show access denied
'allow them to go back to the original page.
end if
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
ON SQL SERVER: Stored Procedure
----------------------------------------------------------
--------------------------------
rx_sp_HasAccessToClient
CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]
@EmployeeID INT,
@ClientSSN varchar(50),
@ReturnBitValue = OUTPUT
/*
' Parameters here passed via call from Form.asp - not sure what is passed
yet.
*/
AS
set nocount on
/*
Written by Mike Belcher 9/27/2007 for Form.asp
'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.
'Gets return bit and passes that back to the call from Form.asp
*/
GO
----------------------------------------------------------
--------------------------------
ON SQL SERVER: User-Defined Function
----------------------------------------------------------
--------------------------------
ab_HasAccessToClient
CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))
@ClientSSN varchar(50),
@EmployeeID,
@ReturnBitValue = OUTPUT
AS
SELECT 1
FROM tblEmployeesClients ec
INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN
INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName
WHERE e.EmployeeID= @EmployeeID
AND c.InActiveClient=0
AND c.ClientSSN = @ClientSSN
'Some Code here to save result bit ..
RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient
----------------------------------------------------------
--------------------------------
</FLOW WITH CODE AND FUNCTIONS :>
View 1 Replies
View Related
Jul 23, 2005
I use a database that has user names stored in Encrypted format usingthe following API.Declare Sub Encrypt2 Lib "QPRO32.DLL" (ByVal Work As String, ByValPASSWORD As String)Every time i require the user name i have to again decrypt the nameusing the same function.My problem is that when i fetch a large number of records i have toloop through every record and call the encrypt function for eachrecord.Instead of binding the recordset to my control i need to loopthrough and fill my controlA MSHFlexGrid in Vb6.0.Is there a way out to this problem that will make my record populatiogfaster withoutout changing the current Encrypted users.Thanx in Advance
View 2 Replies
View Related
Jul 20, 2005
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View 4 Replies
View Related
Jul 30, 2007
When i call user defined function from stored procedure, i am getting an error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.MyFunction'.
why this error is happening?.
i am calling this way... dbo.MyFunction(param1,param2)
View 6 Replies
View Related
May 21, 2008
Hi guys,
I am trying to call a function from a remote sql server (linked server) using the following syntax:
select [ServerName].[dbName].dbo.CLRHelloWorld('SomeMessage'). I am getting the following error:
The object name '[ServerName].[dbName].dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I need to call this function remotely and using this syntax; I don't want to use OPENQUERY...
Any hints?
Yousef
View 4 Replies
View Related
Jul 23, 2005
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies
View Related
Apr 29, 2004
Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)
Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec spTest @from, @to
GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec master..xp_msver
GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
I want to know if it's possible or not..
View 13 Replies
View Related
May 15, 2008
I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.
I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.
I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.
I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.
However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.
Any advice on how to do this?
Thanks,
Bruce
View 1 Replies
View Related
Aug 13, 2007
How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?
Following Function populates a field (with concat list of related titles) with other required fields:
Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))
Returns Varchar(8000) as
Begin
Declare @Lists as varchar(8000);
Select @Lists = '';
Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier
Where @qcParent_ID = qcParent_ID;
Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));
End
works perfect against single table select (returning 54 distinct rows by qcParent_ID):
Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix
From dbo.a2_qcEntryForm a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
This VIEW returns ALL (82) rows in table:
CREATE VIEW z_QClocate_openAll AS
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Calling like this returns ALL 82 rows (negating the functions distinct):
Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix
From z_QClocate_openAll a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
View 3 Replies
View Related
Apr 6, 2007
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.
Code Snippet
[SqlFunction ( FillRowMethodName = "FillRow",
TableDefinition = "CustomerID nvarchar(MAX)",
SystemDataAccess = SystemDataAccessKind.Read,
DataAccess = DataAccessKind.Read,
IsDeterministic=false)]
public static IEnumerable GetWishlist () {
using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {
List<string> myList = new List<string> ();
conn.Open ();
SqlCommand command = conn.CreateCommand ();
command.CommandText = "GetObject";
command.Parameters.AddWithValue ( "@map", "Item" );
command.CommandType = System.Data.CommandType.StoredProcedure;
using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {
if (reader.Read ()) {
myList.Add ( reader[0] as string );
}
}
return (IEnumerable)myList;
}
}
When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.
Code SnippetEXEC GetObject 'Item'
Is there some sorf of trick I am missing?
Thank you!
View 3 Replies
View Related
Mar 2, 2007
Hi There,
I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..
'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.
I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.
Thanks a lot!!
View 8 Replies
View Related
