Post reply

Why DECLARE doesn't work?

  • May 1, 2017 at 9:41 am

    #336705

    declare @scorerecordtype__c varchar
    set @scorerecordtype__c='batch';
    select * from table 1
    where [SCORE_RECORD_TYPE]=@scorerecordtype__c

    Why above DECLARE sentence doesn't work, got nothing returned? But it works when I replace it as:

    select * from table 1
    where [SCORE_RECORD_TYPE]='batch'

  • May 1, 2017 at 9:58 am

    #1940572

    When you do not specify a length for char or varchar in variable declaration, the variable will have a length of 1.

    See https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql

    If you run a select of your variable, you will see that its value is just the string 'b'

    You just need to specify an appropriate length for the variable.

    Cheers!

  • May 1, 2017 at 9:59 am

    #1940573

    kkbear - Monday, May 1, 2017 9:41 AM

    declare @scorerecordtype__c varchar
    set @scorerecordtype__c='batch';
    select * from table 1
    where [SCORE_RECORD_TYPE]=@scorerecordtype__c

    Why above DECLARE sentence doesn't work, got nothing returned? But it works when I replace it as:

    select * from table 1
    where [SCORE_RECORD_TYPE]='batch'

    you havent defined the length of the varchar.
    example

    declare @scorerecordtype__c varchar
    set @scorerecordtype__c='batch'
    print @scorerecordtype__c;

    declare @scorerecordtype__c varchar(5)
    set @scorerecordtype__c='batch'
    print @scorerecordtype__c

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • May 1, 2017 at 10:00 am

    #1940574

    kkbear - Monday, May 1, 2017 9:41 AM

    declare @scorerecordtype__c varchar
    set @scorerecordtype__c='batch';
    select * from table 1
    where [SCORE_RECORD_TYPE]=@scorerecordtype__c

    Why above DECLARE sentence doesn't work, got nothing returned? But it works when I replace it as:

    select * from table 1
    where [SCORE_RECORD_TYPE]='batch'

    The DECLARE works fine. Your data type definition doesn't work that great.
    You're defining a varchar without length which defaults to length 1. To prevent this errors, always define the length of your strings.
    To test, print or select your variables to check their values before using them.

    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

  • May 1, 2017 at 10:19 am

    #1940577

    Just piling on, since you are on 2008+ then you can combine the declaration and the assignment into one statement.
    😎
    declare @scorerecordtype__c varchar(50) = 'batch';
    select * from table 1
    where [SCORE_RECORD_TYPE]=@scorerecordtype__c

  • May 1, 2017 at 10:23 am

    #1940578

    Thank you, all! It's helpful!!!

Viewing 6 posts - 1 through 5 (of 5 total)

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