How do I initialise a sql variable to 0 in stored-procedure ?

  • Dear friends ,

    How to initialise a sql variable to 0 in stored-procedure which would work both in SQLServer 2005 & 2008 ?

  • For a variable inside the procedure use this

    😎

    DECLARE @MY_INT_VAR INT;

    SET @MY_INT_VAR = 0;

  • Interview question?

    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
  • Thanks for the post

  • Eirikur Eiriksson (9/22/2016)


    For a variable inside the procedure use this

    😎

    DECLARE @MY_INT_VAR INT;

    SET @MY_INT_VAR = 0;

    i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?

    I know DECLARE @MY_INT_VAR INT = 0; only works in 2008 and up but that's within a procedure.

    to initialize to 0 for a stored proc for 2005 and 2008 you would need to do it this way.

    alter procedure usp_mytestproc @MY_INT_VAR INT=0

    WITH EXECUTE AS OWNER

    AS

    BEGIN

    SELECT @MY_INT_VAR as InitilizedValue

    END

    exec usp_mytestproc

  • Smendle (9/22/2016)


    Eirikur Eiriksson (9/22/2016)


    For a variable inside the procedure use this

    😎

    DECLARE @MY_INT_VAR INT;

    SET @MY_INT_VAR = 0;

    i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?

    That is correct. The OP also stated it needs to work with both 2005 and 2008. In 2005 you cannot assign a default value to a local variable. I have to agree with Luis...this screams of an interview question.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Smendle (9/22/2016)


    Eirikur Eiriksson (9/22/2016)


    For a variable inside the procedure use this

    😎

    DECLARE @MY_INT_VAR INT;

    SET @MY_INT_VAR = 0;

    i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?

    I know DECLARE @MY_INT_VAR INT = 0; only works in 2008 and up but that's within a procedure.

    to initialize to 0 for a stored proc for 2005 and 2008 you would need to do it this way.

    alter procedure usp_mytestproc @MY_INT_VAR INT=0

    WITH EXECUTE AS OWNER

    AS

    BEGIN

    SELECT @MY_INT_VAR as InitilizedValue

    END

    exec usp_mytestproc

    The part I bolded above provides a default value for the stored procedure if you execute it without passing a value to the procedure. Totally different from what the OP was asking.

  • Luis Cazares (9/22/2016)


    Interview question?

    My thoughts as well. The follow up to that specific question could be interesting if someone doesn't know what would be different between the two.

  • How to initialise a sql variable to 0 in stored-procedure which would work both in SQLServer 2005 & 2008 ?

    The part I bolded above provides a default value for the stored procedure if you execute it without passing a value to the procedure. Totally different from what the OP was asking.

    So then the answer is it cant be done in 2005?

  • Smendle (9/22/2016)


    How to initialise a sql variable to 0 in stored-procedure which would work both in SQLServer 2005 & 2008 ?

    The part I bolded above provides a default value for the stored procedure if you execute it without passing a value to the procedure. Totally different from what the OP was asking.

    So then the answer is it cant be done in 2005?

    If you mean can you assign a value to your variable during your declaration then that is correct, you can't do it in 2005...assigning a value to a variable once it's declared, well that's trivial.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Sue_H (9/22/2016)


    Luis Cazares (9/22/2016)


    Interview question?

    My thoughts as well. The follow up to that specific question could be interesting if someone doesn't know what would be different between the two.

    The answer to this interview question is, "Why are you asking questions about a version that is after it's end-of-life?"

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (9/22/2016)


    Sue_H (9/22/2016)


    Luis Cazares (9/22/2016)


    Interview question?

    My thoughts as well. The follow up to that specific question could be interesting if someone doesn't know what would be different between the two.

    The answer to this interview question is, "Why are you asking questions about a version that is after it's end-of-life?"

    Drew

    Because we're planning a migration of our system on SQL Server 2000 and those are the versions we have available. :hehe:

    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
  • Luis Cazares (9/22/2016)


    drew.allen (9/22/2016)


    Sue_H (9/22/2016)


    Luis Cazares (9/22/2016)


    Interview question?

    My thoughts as well. The follow up to that specific question could be interesting if someone doesn't know what would be different between the two.

    The answer to this interview question is, "Why are you asking questions about a version that is after it's end-of-life?"

    Drew

    Because we're planning a migration of our system on SQL Server 2000 and those are the versions we have available. :hehe:

    Sadly, you'd be surprise how often that is true.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Smendle (9/22/2016)


    Eirikur Eiriksson (9/22/2016)


    For a variable inside the procedure use this

    😎

    DECLARE @MY_INT_VAR INT;

    SET @MY_INT_VAR = 0;

    i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?

    I know DECLARE @MY_INT_VAR INT = 0; only works in 2008 and up but that's within a procedure.

    to initialize to 0 for a stored proc for 2005 and 2008 you would need to do it this way.

    alter procedure usp_mytestproc @MY_INT_VAR INT=0

    WITH EXECUTE AS OWNER

    AS

    BEGIN

    SELECT @MY_INT_VAR as InitilizedValue

    END

    exec usp_mytestproc

    As mentioned, you're assigning a default to a parameter which, technically, isn't a variable.

    For the method you mentioned that works for SQL Server 2008+, it's actually initializing the variable to NULL and then setting it to 0. That's because T-SQL is a declarative language. The variables need to be created during compile time, and assigned during execution time. Check the following code that demonstrates it.

    DECLARE @N int = 0;

    WHILE @N < 5

    BEGIN

    DECLARE @What int = 0;

    SELECT @What, @N;

    SET @N += 1 ;

    END;

    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
  • Y.B. (9/22/2016)


    Sadly, you'd be surprise how often that is true.

    No, I won't. That's why I mentioned it. 🙁

    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

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply