September 22, 2016 at 12:47 am
Dear friends ,
How to initialise a sql variable to 0 in stored-procedure which would work both in SQLServer 2005 & 2008 ?
September 22, 2016 at 2:18 am
For a variable inside the procedure use this
😎
DECLARE @MY_INT_VAR INT;
SET @MY_INT_VAR = 0;
September 22, 2016 at 6:38 am
September 22, 2016 at 6:47 am
Thanks for the post
September 22, 2016 at 10:41 am
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
September 22, 2016 at 10:52 am
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/
September 22, 2016 at 11:14 am
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.
September 22, 2016 at 11:23 am
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.
September 22, 2016 at 11:23 am
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?
September 22, 2016 at 11:27 am
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.
September 22, 2016 at 11:34 am
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
September 22, 2016 at 11:36 am
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:
September 22, 2016 at 11:39 am
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.
September 22, 2016 at 11:44 am
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;
September 22, 2016 at 11:45 am
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. 🙁
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply