Hi, first post! Interested in "Calls"

  • 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?

  • 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.

    😎

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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 .

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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