SQLTeam.com | Weblogs | Forums

Questions about variables

#1

Hello!

I am very confused with the use of variables in SQL.

  1. Do I absolutely need to specify the type when I declare a variable?
  2. When I specify a date variable, do I include it in '' or not?
  3. Do I need to know the type of the field I want to filter using a variable? E.g. for a field that contains dates stored as text, do I need to convert my date variable into text with CONVERT, before I can use it to filter that field?

Thanks!

#2

Yes to all. Why would you declare a variable and not assign a type to it?

2 Likes
#3

So that it can be flexibly used as text or date, or text or number, without having to convert it each time.

#4

This is an Indication of design flaw. Hence your need to parse varchar columns into dates and vise versa in your previous questions in fact most of your questions. Eventually you are going to hit a wall or trip up and maybe even get wrong results and or write really hard to maintain code base. There is no duality of type in sql,
A variable or a table column cannot be explicitly 2 types. You might start thinking about redesigning your approach

2 Likes
#5

Thanks, I think there may be an easy fix:

Is it possible to put a function that will return the data type of the field I want to filter, and put that function at the point where I need to specify the data type of the variable I am declaring and I am trying to filter the field on?

Thanks!

#6

you can use SQL_Variant, but again, WHY?? is it that hard to type declare @i int, @d date, @v varchar(10)? Not sure why you're trying to create a catchall for assiginng data types

1 Like
#7

There is a never an easy fix unless you deal with the underlying issue of the design in my opinion and experience . otherwise it will just be Hodge podge of parsed and converted and cast strings and dates.

:face_with_monocle: come again? Maybe you can create a view that takes the column you want and create other columns that have different flavors of the types you want..again a hack

2 Likes
#8

here is one hack

use sqlteam
go

create table sqlor(id int, date_imposter varchar(7))

;with src
as
(
select distinct top 10000 column_id as id, DATEADD(dd,column_id, GETDATE()) as date_imposter
  from sys.all_columns
)
--2020-12-19 08:12:40.037
insert into sqlor
select id, 
       case when month(date_imposter) < 10 then 
        concat(cast(year(date_imposter) as varchar(4)), '/0' ,cast(month(date_imposter) as varchar(2)) )
		else concat(cast(year(date_imposter) as varchar(4)), '/' ,cast(month(date_imposter) as varchar(2)) ) 
		end
  from src 

select * from sqlor
go

create view vw_sqlor
as
select id, 
       date_imposter, 
	   left(date_imposter, 4) as imposter_year_string,
	   substring(date_imposter, PATINDEX('%/%', date_imposter)+1, len(date_imposter) ) as imposter_month_string,
	   cast(left(date_imposter, 4) as int) as imposter_year_int,
	   cast(substring(date_imposter, PATINDEX('%/%', date_imposter)+1, len(date_imposter) ) as int) as imposter_month_int
from sqlor
go

select * from vw_sqlor

drop view vw_sqlor

drop table sqlor
1 Like
#9

For #1 and #2 - yes...and it isn't hard to figure out what data type you need...intellisense will tell you the column data type or you can use Object Explorer and expand the column list under the table definition.

For #3 - this is very important. If your column data type is varchar - and your variable is defined as a date...then SQL Server will implicitly convert your column to a date data type for the comparison. This will eliminate any value to any index on that column as well as the implicit convert.

This is controlled by data type precedence: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15

Another example would be a column defined as date and a variable defined as datetime...based on precedence the column will be converted to a datetime before it is compared to the variable. Switch those around so the column is a datetime - and the variable is a date - and the variable will be converted first.

SQL Server does not have a similar concept to Oracles %TYPE function for declaring variables.

1 Like
#10
  1. Yes
  2. Yes, because date values are literals in SQL Server, for example, '20201207' or '2020-12-07' is today. But 20201207 would not be.
  3. Whenever possible, a variable compared against a column table should be exactly the same type as the table column. For example, if the column is date, the variable should be date. If the column is decimal(9, 3), the variable should be decimal(9, 3). In most cases SQL Server will automatically implicitly convert to a varchar when needed, but it's not a good idea to use that for column comparisons in the WHERE clause, because it can performance issues in the query. Conversion is not an issue in the SELECT list, it does not cause performance issues there.
2 Likes