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))
-
You can't call the function directly. You should write a stored procedure that acts as a wrapper for the function to call it from your VB application. Paul
-
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))
I'm not sure what these people are talking about, but it's just the same as executing any other bit of sql:
SELECT FontName, FontSize FROM dbo.GetEmployeeFont(@EmployeeId)Just run this with a sql command and return a reader (or whatever suits your code). (Although, that said, it seems to be a more useful thing to have as a stored procedure. Functions are generally more useful when you will re-use them in other bits of sql.) -
I'm not sure what these people are talking about, but it's just the same as executing any other bit of sql:
SELECT FontName, FontSize FROM dbo.GetEmployeeFont(@EmployeeId)Just run this with a sql command and return a reader (or whatever suits your code). (Although, that said, it seems to be a more useful thing to have as a stored procedure. Functions are generally more useful when you will re-use them in other bits of sql.) -
My statement is still correct. A function cannot be called directly. The call must be made from either a stored procedure or inline SQL statement. Paul
Yes, but what benefit is gained from wrapping the function in a stored procedure? It's just another level of code to go wrong...
-
Yes, but what benefit is gained from wrapping the function in a stored procedure? It's just another level of code to go wrong...
-
I never use inline SQL statements. They may be quicker to code, but the advantages of using stored procedures (maintainability, robustness, security) more than outweigh the extra code overhead.
I've been through this discussion a few times with different people and I think that it's more of a religious type decision than anything else. We may just have to agree to differ.
-
I'm not sure what these people are talking about, but it's just the same as executing any other bit of sql:
SELECT FontName, FontSize FROM dbo.GetEmployeeFont(@EmployeeId)Just run this with a sql command and return a reader (or whatever suits your code). (Although, that said, it seems to be a more useful thing to have as a stored procedure. Functions are generally more useful when you will re-use them in other bits of sql.)Thanks Paddy. I need to return some values and use them so I think the function would be best, but there seems to be some debate here if it can be achieved. 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
-
Thanks Paddy. I need to return some values and use them so I think the function would be best, but there seems to be some debate here if it can be achieved. 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
Do you have a case statement in your sql somewhere?
-
Thanks Paddy. I need to return some values and use them so I think the function would be best, but there seems to be some debate here if it can be achieved. 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
No, and I don't want to either. It issues these errors when I hover over the "select fontname, fontsize....." In the old VBA I used to call the functions like: Dim Fontsize as integer fontsize = GetFontSize(strCurrentUser) ---- Function GetFontSize(CurentUser as string) as Integer ...... ...... ----- This meant I was able to use a single like to get my values and use them in lots of places easily. When I read I should store these server-side I was very happy. I just need to call the function and return it to my current sub easily without having to do too much coding to setup and call the function otherwise it defeats the purpose FYI I spent all weekend playing with the new error provider - very nice but it took me the longest time to work it out nicely....MS documentation is horrible....I found Google far better than MS own doccu... Mark