July 17, 2012 at 5:32 am
Hey guys, how can I declare a variable of datatype int that only can be assigned the values 1,2,3,4,5?
July 17, 2012 at 5:45 am
You would need to do a check on the variable to see if it is within the range of numbers allowed
Something like
DECLARE @variable INT = 8
IF @variable >=1 AND @variable <=5
BEGIN
Do what I need to do
END
ELSE
PRINT 'Variable out of scope range'
[/code]
July 17, 2012 at 5:49 am
You can even do a case statement whle assigning the data to the variable.
July 17, 2012 at 5:55 am
Is there a way to do it in the declaration?
July 17, 2012 at 6:02 am
Not so easy restriction at variable declaration in my knowledge. Lets wait for experts to comment on it.
July 17, 2012 at 7:33 am
dndaughtery (7/17/2012)
Is there a way to do it in the declaration?
Not sure what you mean here. Are you asking if you can declare a variable in such a way as to limit the allowed values? I have to say that sounds a bit oddball. The idea of validation is prevent invalid data. Trying to prevent this at the time of declaration just doesn't make sense. You would be asking a developer to write some sort of complicated code to prevent them from entering a number outside of a given range.
What is the goal of what you are trying to accomplish and maybe there is another way to go about it.
_______________________________________________________________
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/
July 17, 2012 at 8:49 am
I was told someone interviewing me would ask this. In my 7 years of DB Development Ive never had a need like this but thought I could vaguely remember some sort of variable declaration like this. Maybe I was just remembering the array declarations in my college programming classes. But I will go with the answers of using conditional logic. THanks for your help guys.
July 17, 2012 at 8:58 am
dndaughtery (7/17/2012)
I was told someone interviewing me would ask this. In my 7 years of DB Development Ive never had a need like this but thought I could vaguely remember some sort of variable declaration like this. Maybe I was just remembering the array declarations in my college programming classes. But I will go with the answers of using conditional logic. THanks for your help guys.
Well that sounds like they are going to ask you about user defined datatypes and/or rules. You could do this type of validation with user defined datatypes and rules.
http://msdn.microsoft.com/en-us/library/ms188064.aspx
_______________________________________________________________
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/
July 17, 2012 at 9:19 am
dndaughtery (7/17/2012)
Is there a way to do it in the declaration?I was told someone interviewing me would ask this. In my 7 years of DB Development Ive never had a need like this but thought I could vaguely remember some sort of variable declaration like this. Maybe I was just remembering the array declarations in my college programming classes. But I will go with the answers of using conditional logic. THanks for your help guys.
Nope. That would be my answer in an interview.
I have been on both sides of the interview table for SQL-related work since 1999. I have never asked someone that nor have I ever been asked that. If I were my answer would be "No". If I were asking this, "No" would be that answer I would be expecting.
You can, however, do this before the variable is passed depending on how it is being passed. For example: if you are using SSRS 2008 and that parameter was being fed to a stored procedure from an SSRS report then, "YES" you can set any restriction(s) you wish there; the options are endless. But now we're talking about something which is better discussed in a BI forum.
-- Itzik Ben-Gan 2001
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply