Calling Stored SQL Functions from Visual Basic
-
Hi Everyone, Wasn't sure if this was a VB or SQL question and where to post so I am trying here. I read it is better to use stored procedures and functions on the SQL server to centralise and re-use code, as well as allowing us to restrict rights to EXECUTE rather than full rights. I wanted to retrieve (and maybe in another function SET), some data so I wrote the following basic function and stored it on the server. My problem is that no matter how I try, I can't seem to find a way to call the function from my VB.NET 2005 application. Please help ALTER FUNCTION dbo.GetEmployeeFont ( @EmployeeID int ) RETURNS TABLE AS RETURN (SELECT FontName, FontSize FROM tblEmployees WHERE (EmployeeID = @EmployeeID))
-
Hi Everyone, Wasn't sure if this was a VB or SQL question and where to post so I am trying here. I read it is better to use stored procedures and functions on the SQL server to centralise and re-use code, as well as allowing us to restrict rights to EXECUTE rather than full rights. I wanted to retrieve (and maybe in another function SET), some data so I wrote the following basic function and stored it on the server. My problem is that no matter how I try, I can't seem to find a way to call the function from my VB.NET 2005 application. Please help ALTER FUNCTION dbo.GetEmployeeFont ( @EmployeeID int ) RETURNS TABLE AS RETURN (SELECT FontName, FontSize FROM tblEmployees WHERE (EmployeeID = @EmployeeID))
That would be better as a stored procedure if you are going to call it from VB
Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
Hi Everyone, Wasn't sure if this was a VB or SQL question and where to post so I am trying here. I read it is better to use stored procedures and functions on the SQL server to centralise and re-use code, as well as allowing us to restrict rights to EXECUTE rather than full rights. I wanted to retrieve (and maybe in another function SET), some data so I wrote the following basic function and stored it on the server. My problem is that no matter how I try, I can't seem to find a way to call the function from my VB.NET 2005 application. Please help ALTER FUNCTION dbo.GetEmployeeFont ( @EmployeeID int ) RETURNS TABLE AS RETURN (SELECT FontName, FontSize FROM tblEmployees WHERE (EmployeeID = @EmployeeID))
To call a function in T-SQL you would do e.g. : SELECT * FROM dbo.GetEmployeeFont(2100) To call a stored prcoedure: EXECUTE dbo.GetEmployeeFont 2100 So first you need to know whether you are intending to use a function or a stored proc...
'--8<------------------------ Ex Datis: Duncan Jones Merrion Computing Ltd
-
To call a function in T-SQL you would do e.g. : SELECT * FROM dbo.GetEmployeeFont(2100) To call a stored prcoedure: EXECUTE dbo.GetEmployeeFont 2100 So first you need to know whether you are intending to use a function or a stored proc...
'--8<------------------------ Ex Datis: Duncan Jones Merrion Computing Ltd
Thanks Duncan. I need to return some values and use them so I think the function would be best. Excuse my ignorance but my previous coding was for MSAcess so I"m trying to learn SQL server issues, and move from VBA to VB.NET. I have put in the SELECT statement you sugessted but the compiler tells me "Select CASE must end with a matching END SELECT", and hovering over FROM in the statement gives me "name not declared" . Obviously I need more code to setup the SQL statement - could you kindly give me an example please. Many thanks Mark