locked
Create a Stored Procedure to pass in NewDateOfBirth and MemberID As Parameters which Update the age, DateofBirth columns..... RRS feed

  • Question

  • Hello Team,

    This is what I have:

    Create Procedure #USP_MyUpdate(@MemberID int, @NewDateOfBirth Date, @Age Int Output, @DateofBrith Date Output, @DateofBirthAsText Varchar(20) Output)

    AS

    begin

    Update Mytable

    SET @DateOfBirth = @NewDateOfBirth, @Age=DateDiff(yy,@NewDateOfBirth,GetDate()), @DateOfBirthAsText = Convert(Varchar(20), @NewDateOfBirth)

    WHERE MemberId = @MemberID

    End

    Is this syntax correct?

    =============================

    Now I want to execute it.

    Declare @MemberID Int

    Declare @NewDateofBirth Int

    Declare @DateOFBirth Int 

    Declare @DateofBirthAsText Varchar(20)

    Declcare @Age int

    Exec #USP_MyUpdate 1234, 12/11/2017, @Age Output, @DateOfBirth Output, @DateofBirthAsText Output

    ----------------------------------------------------------------

    Please let me know if this syntax is correct and how I can test it.

    Please comment

    Regards,

    Clouds


    CloudsInSky

    Friday, December 8, 2017 4:40 AM

Answers

  • Not sure what your stored procedure is doing. Here is the script to get the values from output parameters:

    Declare @MemberID int
    Declare @NewDateOfBirth Date
    Declare @Age int
    Declare @DateOfBirth Date
    Declare @DateofBirthAsText Varchar(20)
    
    Set @MemberID = 3
    Set @NewDateOfBirth = '01-08-1976'
    
    Exec USP_MyProcess, @MemberID, @NewDateOfBirth, @Age Output, @DateOfBirth Output, @DateOfBirthAsText Output
    
    SELECT @Age, @DateofBirth, @DateOfBirthAsText


    A Fan of SSIS, SSRS and SSAS

    Friday, December 8, 2017 10:08 PM
  • Hello Visakh,

    I declare variables

    and assign a value to them, I execute the store procedure and it gives null.

    This is what I have 

    Declare @MemberID int

    Declare @NewDateOfBirth Date

    Declare @DateOfBirth Date

    Declare @DateofBirthAsText Varchar(20)

    Set @MemberID =3

    Set @NewDateOfBirth ='01-08-1976'

    Exec USP_MyProcess, 3, '01-08-1976', @Age Output, @DateOfBirth Output, @DateOfBirthAsText Output

    SELECT @Age, @DateofBirth, @DateOfBirthAsText

    It returns null.

    I appreciate your help.

    Thanks,

    Blue Sky


    CloudsInSky

    Nope

    you're not doing any value assignments inside your procedure

    you've to do it the way I suggested if you want return values outside


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by cloudsInSky Sunday, March 22, 2020 10:58 AM
    Monday, December 11, 2017 9:00 AM
  • Your problem is not syntax. Is that you don’t know how to design a database! Procedure names should take the general form of “<verb>_<target>” and not be loaded with metadata. Identifiers are never numeric; what math were you going to do with the members ID? This is like wanting to take the square root of your credit card number :( finally, why are you storing computed columns in the base table? We never do that. You can put the computation and computed column. I suppose or in a view, but most of the time you find you want to do it in the presentation layer of your tiered architecture. 

    Since you failed to follow the basic netiquette of this forum and did not post DDL, I’m going to guess that your procedure should probably look like this:

    CREATE PROCEDURE Update_Membership_Birth_Date
     @in_member_id CHAR(10), 
     @in_birth_date DATE, 
    AS
    BEGIN
    UPDATE Something_Membership
       SET birth_date = @in_birth_date
    WHERE member_id = @in_member_id 
    END;

    Now we get in some dialect questions. The old Sybase SQL Server required the @ prefixes because the compiler was a simple one pass compiler. It was basically the sort of thing that 20 or 30 years ago. Graduate students wrote as part of their compiler class assignments. Currently, however, the SQL/PSM uses the keywords [IN | OUT | INOUT] in front of parameters to assure how their past. I found it a good idea to use “in_<proc name>” in SQL Server so that when the procedure has to be ported can be moved easily and so that someone who knows ANSI/ISO standard SQL can read your code.

    Even today SQL Server still uses @, @@, #, and ## from the decades-old primitive compiler they inherited from Sybase. You’re just sort of stuck with it. But it does give you a good warning about the how you write your code and not to get stuck in legacy stuff

    Dates are passed using the ISO 8601 display format in ANSI/ISO standard SQL. That means we would never use your ambiguous date format (which month did you intend? December? November?)

    EXEC Update_Membership_Birth_Date (‘1234567890’, ‘2017-12-11’);
    or maybe
    EXEC Update_Membership_Birth_Date (‘1234567890’, ‘2017-11-12your

    >> Please comment <I can reach you two years<

    Based on 30+ years of teaching SQL, I think are about two years away from being employable, if you can find a good shop with people that will teach you how to use the language.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by cloudsInSky Saturday, May 16, 2020 1:39 AM
    Monday, December 11, 2017 7:16 PM
  • Transact-SQL language uses @ for variables, so there is no way to avoid it. For the OUTPUT parameters you need to use the word OUTPUT.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, December 18, 2017 3:44 AM
  • Hi,

    I am not sure, there are lots of great SQL server related books. I think for beginners Pinal Dave books may be good. Several years ago he had a series of articles introducing different SQL Server topics (mostly for beginners) and after each article there were a few exercise questions and he randomly selected two winners (one from USA and one from all other countries) and sent them the books. I somehow managed to win many of them (but not all series of 5, I think).

    You may start to look at Pinal's website and read what you can find there. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Sunday, March 22, 2020 3:00 PM
    • Marked as answer by cloudsInSky Friday, March 27, 2020 8:00 AM
    Sunday, March 22, 2020 2:59 PM

All replies

  • Hi Clouds,

    You may test the procedure like this.

    create table Mytable
    (
    MemberId int,
    DateOfBirth date,
    Age int,
    DateOfBirthAsText varchar(20)
    )
    
    insert into Mytable(MemberId) values
    (3)
    
    select * from Mytable
    
    
    ---rename the procedure name to USP_MyUpdate
    Create Procedure USP_MyUpdate
    (
    @MemberID int, 
    @NewDateOfBirth Date, 
    @Age Int Output, 
    @DateofBrith Date Output, 
    @DateofBirthAsText Varchar(20) Output
    )
    
    AS
    
    begin
    
    Update Mytable
    --SET @DateOfBirth = @NewDateOfBirth, @Age=DateDiff(yy,@NewDateOfBirth,GetDate()), @DateOfBirthAsText = Convert(Varchar(20), @NewDateOfBirth)
    SET DateOfBirth = @NewDateOfBirth, Age=DateDiff(yy,@NewDateOfBirth,GetDate()), DateOfBirthAsText = Convert(Varchar(20), @NewDateOfBirth)
    WHERE MemberId = @MemberID
    
    set @Age=DateDiff(yy,@NewDateOfBirth,GetDate())
    set @DateofBrith=@NewDateOfBirth
    set @DateofBirthAsText=Convert(Varchar(20), @NewDateOfBirth)
    End
    
    -----Execute the command below
    --Declare @MemberID Int
    Declare @v_MemberID Int
    
    --Declare @NewDateofBirth Int
    Declare @v_NewDateofBirth Date
    
    --Declare @DateOFBirth Int
    Declare @v_DateOFBirth Date
    
    --Declare @DateofBirthAsText Varchar(20)
    Declare @v_DateofBirthAsText Varchar(20)
    
    --Declcare @Age int
    Declare @v_Age int
    
    set @v_MemberID=3
    set @v_NewDateofBirth='1976-8-1'
    
    --Exec #USP_MyUpdate 1234, 12/11/2017, @Age Output, @DateOfBirth Output, @DateofBirthAsText Output
    Exec USP_MyUpdate @v_MemberID, @v_NewDateofBirth, @v_Age Output, @v_DateOfBirth Output, @v_DateofBirthAsText Output
    
    select @v_Age,@v_DateOFBirth,@v_DateofBirthAsText
    
    
    --Or execute the command below
    ---------------
    --Declare @DateOFBirth Int
    Declare @v_DateOFBirth1 Date
    
    --Declare @DateofBirthAsText Varchar(20)
    Declare @v_DateofBirthAsText1 Varchar(20)
    
    --Declcare @Age int
    Declare @v_Age1 int
    
    Exec USP_MyUpdate 3,'1976-8-1', @v_Age1 Output, @v_DateOfBirth1 Output, @v_DateofBirthAsText1 Output
    
    select @v_DateOFBirth1,@v_DateofBirthAsText1,@v_Age1 

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 8, 2017 5:09 AM
  • Nope

    You're not setting any values for fields in the table

    perhaps you meant this?

    Create Procedure #USP_MyUpdate(@MemberID int, @NewDateOfBirth Date, @Age Int Output, @DateofBrith Date Output, @DateofBirthAsText Varchar(20) Output)
    
    AS
    
    begin
    
    Update Mytable
    
    SET DateOfBirth = @NewDateOfBirth
    
    WHERE MemberId = @MemberID
    
    SELECT @Age=DateDiff(yy,@NewDateOfBirth,GetDate()), 
    @DateOfBirth = @NewDateOfBirth,
    @DateOfBirthAsText = Convert(Varchar(20), @NewDateOfBirth)
    
    End


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Naomi N Monday, December 18, 2017 3:40 AM
    Friday, December 8, 2017 5:09 AM
  • Hello Will,

    Thank you so much!

    One question:

    Why did you create new variables?

    Declare @v_DateofBirthAsText1

    Can't we use Declare @DateOfBirth Date?

    --Or execute the command below --------------- --Declare @DateOFBirth Int Declare @v_DateOFBirth1 Date --Declare @DateofBirthAsText Varchar(20) Declare @v_DateofBirthAsText1 Varchar(20) --Declcare @Age int Declare @v_Age1 int Exec USP_MyUpdate 3,'1976-8-1', @v_Age1 Output, @v_DateOfBirth1 Output, @v_DateofBirthAsText1 Output select @v_DateOFBirth1,@v_DateofBirthAsText1,@v_Age1

    Best Regards,

    Dark Sky


    CloudsInSky

    Friday, December 8, 2017 7:07 AM
  • Hello Will,

    Thank you so much!

    One question:

    Why did you create new variables?

    Declare @v_DateofBirthAsText1

    Can't we use Declare @DateOfBirth Date?

    --Or execute the command below --------------- --Declare @DateOFBirth Int Declare @v_DateOFBirth1 Date --Declare @DateofBirthAsText Varchar(20) Declare @v_DateofBirthAsText1 Varchar(20) --Declcare @Age int Declare @v_Age1 int Exec USP_MyUpdate 3,'1976-8-1', @v_Age1 Output, @v_DateOfBirth1 Output, @v_DateofBirthAsText1 Output select @v_DateOFBirth1,@v_DateofBirthAsText1,@v_Age1

    Hi Sky,

    I just make the statement more clear. If you understand well, you could do it. I'm afraid that you would be confused by the parameters of the procedure, new declared variables, output parameters and input parameter parameters.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 8, 2017 7:29 AM
  • Hello Will_Kong,

    Why are you setting these statements two time?

    SET DateOfBirth = @NewDateOfBirth, Age=DateDiff(yy,@NewDateOfBirth,GetDate()), DateOfBirthAsText = Convert(Varchar(20), @NewDateOfBirth) WHERE MemberId = @MemberID 

    set @Age=DateDiff(yy,@NewDateOfBirth,GetDate())

    set @DateofBrith=@NewDateOfBirth

    set @DateofBirthAsText=Convert(Varchar(20), @NewDateOfBirth)

    ================================

    This is kind of confusing. I stick to this:

    SET DateOfBirth = @NewDateOfBirth, Age=DateDiff(yy,@NewDateOfBirth,GetDate()), DateOfBirthAsText = Convert(Varchar(20), @NewDateOfBirth) WHERE MemberId = @MemberID 

    =============================

    The stored procedure ran successfully, but when I execute the stored procedure, it gives me null and I don't know how to fix it.

    Regards,

    Sky


    CloudsInSky

    Friday, December 8, 2017 9:14 PM
  • Hello Visakh,

    I declare variables

    and assign a value to them, I execute the store procedure and it gives null.

    This is what I have 

    Declare @MemberID int

    Declare @NewDateOfBirth Date

    Declare @DateOfBirth Date

    Declare @DateofBirthAsText Varchar(20)

    Set @MemberID =3

    Set @NewDateOfBirth ='01-08-1976'

    Exec USP_MyProcess, 3, '01-08-1976', @Age Output, @DateOfBirth Output, @DateOfBirthAsText Output

    SELECT @Age, @DateofBirth, @DateOfBirthAsText

    It returns null.

    I appreciate your help.

    Thanks,

    Blue Sky


    CloudsInSky

    Friday, December 8, 2017 9:21 PM
  • Normally, you would not store a DOB and the age. You would calculate the age dynamically from the DOB and GETDATE.

    • Proposed as answer by Naomi N Monday, December 18, 2017 3:40 AM
    Friday, December 8, 2017 9:23 PM
  • Not sure what your stored procedure is doing. Here is the script to get the values from output parameters:

    Declare @MemberID int
    Declare @NewDateOfBirth Date
    Declare @Age int
    Declare @DateOfBirth Date
    Declare @DateofBirthAsText Varchar(20)
    
    Set @MemberID = 3
    Set @NewDateOfBirth = '01-08-1976'
    
    Exec USP_MyProcess, @MemberID, @NewDateOfBirth, @Age Output, @DateOfBirth Output, @DateOfBirthAsText Output
    
    SELECT @Age, @DateofBirth, @DateOfBirthAsText


    A Fan of SSIS, SSRS and SSAS

    Friday, December 8, 2017 10:08 PM
  • Hello Guoxiong,

    I assume that the original table has small date int, and the NewDateofBirth and DateofBirth have Date  data type, that might be the reason that is not reading it and it brings back null.

    Thanks for all!

    Sky


    CloudsInSky

    Monday, December 11, 2017 1:45 AM
  • Create procedure UpDOB
     @MemberId int,@DOB date
     , @Output varchar(500) OUTPUT
     AS
    BEGIN

    Declare @age int=0,
    @curyear date

    if NOT EXISTS (Select MemberID from MemberDetails where MemberId=@MemberId)
    BEGIN
    set @Output='A Member with memberId doesnt Exist'
    select @Output
    return 

    END
    ELSE
    BEGIN
    select @curyear=GETDATE()
    SET @age=DATEDIFF (YEAR,@dob,@curyear);
    update MemberDetails set DOB=@DOB,Age=@age where MemberId=1 
    END
    END

    Please Mark This As Answer if it solved your issue 
    Please Vote This As Helpful if it helps to solve your issue 
    Vin

    Monday, December 11, 2017 8:33 AM
  • Hello Visakh,

    I declare variables

    and assign a value to them, I execute the store procedure and it gives null.

    This is what I have 

    Declare @MemberID int

    Declare @NewDateOfBirth Date

    Declare @DateOfBirth Date

    Declare @DateofBirthAsText Varchar(20)

    Set @MemberID =3

    Set @NewDateOfBirth ='01-08-1976'

    Exec USP_MyProcess, 3, '01-08-1976', @Age Output, @DateOfBirth Output, @DateOfBirthAsText Output

    SELECT @Age, @DateofBirth, @DateOfBirthAsText

    It returns null.

    I appreciate your help.

    Thanks,

    Blue Sky


    CloudsInSky

    Nope

    you're not doing any value assignments inside your procedure

    you've to do it the way I suggested if you want return values outside


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by cloudsInSky Sunday, March 22, 2020 10:58 AM
    Monday, December 11, 2017 9:00 AM
  • Your problem is not syntax. Is that you don’t know how to design a database! Procedure names should take the general form of “<verb>_<target>” and not be loaded with metadata. Identifiers are never numeric; what math were you going to do with the members ID? This is like wanting to take the square root of your credit card number :( finally, why are you storing computed columns in the base table? We never do that. You can put the computation and computed column. I suppose or in a view, but most of the time you find you want to do it in the presentation layer of your tiered architecture. 

    Since you failed to follow the basic netiquette of this forum and did not post DDL, I’m going to guess that your procedure should probably look like this:

    CREATE PROCEDURE Update_Membership_Birth_Date
     @in_member_id CHAR(10), 
     @in_birth_date DATE, 
    AS
    BEGIN
    UPDATE Something_Membership
       SET birth_date = @in_birth_date
    WHERE member_id = @in_member_id 
    END;

    Now we get in some dialect questions. The old Sybase SQL Server required the @ prefixes because the compiler was a simple one pass compiler. It was basically the sort of thing that 20 or 30 years ago. Graduate students wrote as part of their compiler class assignments. Currently, however, the SQL/PSM uses the keywords [IN | OUT | INOUT] in front of parameters to assure how their past. I found it a good idea to use “in_<proc name>” in SQL Server so that when the procedure has to be ported can be moved easily and so that someone who knows ANSI/ISO standard SQL can read your code.

    Even today SQL Server still uses @, @@, #, and ## from the decades-old primitive compiler they inherited from Sybase. You’re just sort of stuck with it. But it does give you a good warning about the how you write your code and not to get stuck in legacy stuff

    Dates are passed using the ISO 8601 display format in ANSI/ISO standard SQL. That means we would never use your ambiguous date format (which month did you intend? December? November?)

    EXEC Update_Membership_Birth_Date (‘1234567890’, ‘2017-12-11’);
    or maybe
    EXEC Update_Membership_Birth_Date (‘1234567890’, ‘2017-11-12your

    >> Please comment <I can reach you two years<

    Based on 30+ years of teaching SQL, I think are about two years away from being employable, if you can find a good shop with people that will teach you how to use the language.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by cloudsInSky Saturday, May 16, 2020 1:39 AM
    Monday, December 11, 2017 7:16 PM
  • Hello Will,

    I am not confused by input and output parameter but it doesn't get what I want.

    The sp runs successfully, but It returns null when I execute it.

    Regards,

    Sky


    CloudsInSky

    Monday, December 18, 2017 2:57 AM
  • Hello Vin,

    update MemberDetails set DOB=@DOB,Age=@age where MemberId=1 

    I think we should say where MemberID = @MemberID

    or maybe reverse where @MemberID = MemberiD

    Regards,

    Sky


    CloudsInSky

    Monday, December 18, 2017 3:00 AM
  • Hello CELKO,

    Thanks for what you say:

    Actually, I have not designed the tables and database, I have no control over it.

    What you have put is exactly what I have written.

    Are you saying for a parameter that we want to be held in a variable, we shouldn't use @? 

    Should we use only in and out? Then what would be the syntax?

    All the programming languages are using @ and it is hard for me to stay away from it?

    Regards,

    CloudsInSky


    CloudsInSky

    Monday, December 18, 2017 3:09 AM
  • Transact-SQL language uses @ for variables, so there is no way to avoid it. For the OUTPUT parameters you need to use the word OUTPUT.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, December 18, 2017 3:44 AM
  • Hello Naomi,

    Do you know of a SQL programming book for beginners/

    Please advise me.

    Regards,

    CloudsInSky


    CloudsInSky

    Sunday, March 22, 2020 11:14 AM
  • Hi,

    I am not sure, there are lots of great SQL server related books. I think for beginners Pinal Dave books may be good. Several years ago he had a series of articles introducing different SQL Server topics (mostly for beginners) and after each article there were a few exercise questions and he randomly selected two winners (one from USA and one from all other countries) and sent them the books. I somehow managed to win many of them (but not all series of 5, I think).

    You may start to look at Pinal's website and read what you can find there. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Sunday, March 22, 2020 3:00 PM
    • Marked as answer by cloudsInSky Friday, March 27, 2020 8:00 AM
    Sunday, March 22, 2020 2:59 PM
  • Do you know of a SQL programming book for beginners/

    My friend Itzik Ben-Gan is known as one of the best T-SQL teachers there is. His Fundamentals books may be of interest to you. https://tsql.solidq.com/booksdisplay/tf3/


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, March 22, 2020 6:43 PM
  • Hello Naomi,

    Thanks for your help. I am looking for a book to does the queries with variables.

    I will find that out.

    Thanks for the response.

    CloudsInSky


    CloudsInSky

    Friday, March 27, 2020 7:59 AM
  • Hello Erland Sommarskog,

    I have that book and I have read it a couple of times. The book is more about writing static SQL Statements. It is not a programming book to have variables and so on.

    Respectfully,

    CloudsInSky


    CloudsInSky

    Friday, March 27, 2020 8:04 AM
  • Hello Naomi,

    I looked into his book, it doesn't show all the pages. I am looking to for using variables in SQL. 

    I appreciate your response.

    CloudsInSky


    CloudsInSky


    • Edited by cloudsInSky Thursday, April 2, 2020 7:37 PM
    Thursday, April 2, 2020 7:37 PM
  • Hello Will_Kong,

    Do you know of a programming book for SQL? I am looking for a book to use variables.

    Please advise me.

    Respectfully,

    CloudsInSky


    CloudsInSky

    Thursday, April 2, 2020 7:39 PM
  • Hello Visakh16,

    Do you know of any programming book for SQL?

    Regards,

    CloudsInSky


    CloudsInSky

    Thursday, April 2, 2020 7:40 PM