April 26, 2015 at 1:04 pm
Hello guys!
What I am trying to do is create functions I can call sort of like programming, Ive researched stored procedures and functions, but I am not understanding the syntax, some help would be appreciated.
Here's the scenario..
I want to do something like this..
Call NewInsurancePolicy
and it runs a query.. here's what the query looks like without the CALL..
Select sum(InsurancePolicy) From #TEMPTABLE Where FLAG = 40
real simple.. is this possible?
April 26, 2015 at 1:46 pm
Hi and welcome to the forum.
Just a quick question, can you elaborate a little further on your request, i.e. describing the input and the results, how you are going to call the code etc.
😎
April 26, 2015 at 2:12 pm
I want to find out the sum of the total new insurance policies quickly, I dont want to have to navigate an entire long list of querys to find what I find.. I just want to highlight a function like..
Call Query 1
and it runs a select statement.. such as.. Select sum(NewInsurancePolicy) From Database where Flag = 40
April 26, 2015 at 7:21 pm
rourrourlogan (4/26/2015)
I want to find out the sum of the total new insurance policies quickly, I dont want to have to navigate an entire long list of querys to find what I find.. I just want to highlight a function like..Call Query 1
and it runs a select statement.. such as.. Select sum(NewInsurancePolicy) From Database where Flag = 40
Depending on how you use such a thing, it could be a recipe for disaster or it could be your best friend.
There are three things you should lookup and learn how to use because all three can do what you ask. Stored Procedures, Inline Table Valued Functions (iTVF for future reference), and Views. All have advantages and disadvantages. All of them, of course, have the advantage of code abstraction. All of them, of course, have the disadvantage of code obfuscation. You can seriously go overboard with abstraction in T-SQL just like you can in any language.
Although there are ways to call stored procedures, iTVFs, and Views in similar fashions, stored procedures are normally "called" using either the EXEC or EXECUTE keyword. iTVFs and Views are normally "called" by including them in the FROM clause of a SELECT, INSERT, UPDATE, DELETE, or MERGE statement of one form or another.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2015 at 3:21 am
Here's the documentation on CREATE PROCEDURE. That will get you started in putting your query into a procedure. Then, as Jeff said, it's a question of calling it using the EXECUTE command.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2015 at 4:30 am
It still isn't clear if you are designing an end user visual interface or creating reusable piece of SQL code which other code will call or need just to run some queries repeatedly using SSMS .
April 27, 2015 at 11:07 am
Hi there,
I'm not sure if you're clear on the purpose of stored procedures and functions. If you're used to common programming languages, you'll find out that stored procedures are very similar to methods. They're both designed to be a set of code that will create an action. Stored procedures might or might not return result sets.
Functions on the other side, work as in any other language. They're designed to return a value. In SQL they can return scalar values or result sets that can be used as tables.
Once you understand the difference, you'll know which one you should use.
You've received good advice on how to use them and where you can find more information. As we don't know where do you want to call the code from, we can't give a great advice.
April 29, 2015 at 6:27 pm
rourrourlogan (4/26/2015)
I want to find out the sum of the total new insurance policies quickly, I dont want to have to navigate an entire long list of querys to find what I find.. I just want to highlight a function like..Call Query 1
and it runs a select statement.. such as.. Select sum(NewInsurancePolicy) From Database where Flag = 40
There are several ways to do something like you indicate. When we say that it depends what is meant here is why do you need to do this?
(1) If this is a query that you have to run from time to time and it is not part of anything else then look at saving the query to a file and loading it into SSMS when you need to run it. There are other query tools that do something like that as well.
(2) If you are going to use the result of that calculation something else or if there are times that the flag might be something else besides 40 then look at creating a stored procedure (as suggested above) that takes the 40 as a parameter.
I'm assuming that 40 means a new policy. Let's also assume that 80 means cancelled. Since it is possible to have one stored procedure call another stored procedure you could have a procedure for each type of flag and they would call one common procedure to run the SELECT.
(3) You might be looking for something that would sum up the policies by flag value. If the meanings of the flag values are in another table then you might do well with a view that joins the two tables.
========
There are costs and benefits to every approach. We want to help you make the best decision but also help you learn how to decide this on your own. To that end knowing the why is important.
ATBCharles Kincaid
April 30, 2015 at 12:23 am
I am wondering if the "#TEMPTABLE" you mention is created inside your code, in which this would fall into the realm of a stored procedure. For a simple lookup as you mention ( I think by policy number) you could implement this by calling a stored procedure and passing a policy number as a argument. I don't see the need for a temp table in this situation as it indicates you would only be returning one line.
If on the other hand you wanted to do this for all policies in one go (on row per policy with poilicy # and sum of payments) then a table valued function may be the way to go. It is not overly complicated either way, you just need to devote a little bit of quiet reading time.
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply