Post reply

can't print the value of a local variable

  • February 16, 2010 at 12:25 pm

    #230055

    Okay, I just don't get it. I'm trying to build a dynamic query. (I cut this out of a stored proc so there's some extra junk but I did run this, as is, in a query window.)

    I'm creating two string variables to hold two parts of a long query. The query executes but doesn't pick up any rows in this form so I'm trying to figure out what is actually set in the variables. I've tried PRINT and I've tried SELECT. I've also tried SELECT INTO a table name. No matter what I do, I can not get it to dump the values of two of the variables. @string2 and @SQLSubmit. Clearly I'm doing something ridiculously wrong but I just don't see it. I can't use sp_executesql because apparently, you can't pass an nvarchar value of more than 4000 characters even when you say nvarchar(max) so I'm trying to cheat. I know the values in the variables are being set because the code executes; however, it doesn't return any rows. Any help will be greatly appreciated.

    DECLARE @Report_From datetime

    DECLARE @Report_To datetime

    DECLARE @Jun30Date datetime

    DECLARE @Dec31Date datetime

    DECLARE @PAPLookbackDate datetime

    DECLARE @LMPLookbackDate datetime

    DECLARE @BestServed int

    DECLARE @Client_ID varchar(40)

    DECLARE @Database_ID varchar(40)

    DECLARE @EHR_Format_Class_ID int

    DECLARE @EHR_Active bit

    DECLARE @CPU_Count integer

    DECLARE @CPU_String varchar(2)

    DECLARE @String1 nvarchar(max)

    DECLARE @String2 nvarchar(max)

    DECLARE @SQLSubmit nvarchar(max)

    SET @Jun30Date = CAST('06/30/' + cast(year(@Report_From) as varchar(4)) as datetime)

    SET @Dec31Date = CAST('12/31/' + cast(year(@Report_From) as varchar(4)) as datetime)

    SET @PAPLookbackDate = DATEADD(year, -3, @Report_To)

    SET @LMPLookbackDate = DATEADD(week, -42, @Report_From)

    SET @CPU_Count = (SELECT cpu_count FROM sys.dm_os_sys_info)

    BEGIN TRANSACTION;

    SET @String1 = N'INSERT INTO dbo.DWUDS_Data_Mart_2009_Patients

    (Client_ID,

    Database_ID,

    Patient_ID,

    Patient_Number,

    Patient_Last_First_Initial,

    Age_Jun_30,

    DOB,

    Sex,

    Zip_Code,

    Ethnicity,

    Race,

    Language,

    Migrant,

    Seasonal,

    Homeless,

    Veteran,

    Pre_Existing_Patient,

    Percent_of_Poverty,

    Number_of_Family_Members,

    Income,

    Uninsured_Start_Date,

    LMP_Date,

    First_Prenatal_Visit_Date,

    Trimester_Entered_Prenatal_Care_With_Grantee,

    Trimester_Entered_Prenatal_Care_ExGrantee,

    Patient_Delivered_By_Grantee,

    Patient_Delivered_Flag,

    Documented_Live_Birthweight,

    HIV_Positive_Women_Flag,

    Pap_Eligible_Flag,

    Pap_Contraindicated_Flag,

    Pap_Conducted_Flag,

    Immunization_Eligible_Flag,

    Immunization_Contraindicated_Flag,

    Immunization_Complete_Flag,

    Hypertension_Flag,

    Hypertension_Controlled_Flag,

    Blood_Pressure_Value,

    Diabetes_Flag,

    Glucose_Test_Value,

    Patient_City,

    Patient_State,

    Public_Housing,

    Age_Dec_31,

    Homeless_Status,

    Primary_Policy_Insurance_Type,

    Best_Served_Non_English,

    EHR_Active,

    Primary_Policy_Carrier_Abbreviation,

    EHR_Patient_ID,

    EHR_Account_ID) --There are additional fields in this table which will be

    --initialized to NULL by default.

    SELECT DISTINCT

    pat.Client_ID

    ,pat.Database_ID

    ,pat.Patient_ID

    ,pat.Patient_Number

    ,Patient_LFI

    ,datediff(yy,Patient_Dob,@Jun30Date) -

    case when @Jun30Date < dateadd(yy, datediff(yy,Patient_Dob,@Jun30Date), Patient_Dob) then 1 else 0 end

    --,DATEDIFF(YEAR, Patient_DOB, @Jun30Date)-- as Age

    ,Patient_DOB

    ,Patient_Sex

    ,Patient_Zip_Code

    ,Patient_Ethnicity

    ,Patient_Race

    ,Patient_Language

    ,CAST(ISNULL(Migrantv.Control_Value1,''0'') as bit) --as Patient_Migrant,

    ,CAST(ISNULL(Seasonalv.Control_Value1,''0'') as bit) --as Patient_Seasonal,

    ,CAST(ISNULL(Homelessv.Control_Value1,''0'') as bit) --as Patient_Homeless,

    ,CAST(ISNULL(Veteranv.Control_Value1,''0'') as bit) --as Patient_Veteran,

    ,CAST(ISNULL(Pre_Existingv.Control_Value1,''0'') as bit) --as Pre_Existing_Patient,

    , 0

    , 0 --Number_Of_Family_Members --as Patient_Number_of_Family_Members,

    , 0 --Household_Income --as Patient_Income,

    , NULL --as Uninsured_Start_Date,

    ,NULL --AS LMP_Date,

    ,NULL --AS First_Prenatal_Visit_Date,

    , CAST(ISNULL(Trimesterv.Control_Value1,''0'') as int) --as Patient_Delivered_By_Grantee,

    ,0 --AS Trimester_Entered_Prenatal_Care_ExGrantee,

    ,0 --AS Patient_Delivered_By_Grantee,

    ,0 --AS Patient_Delivered_Flag,

    ,0 --AS Documented_Live_Birthweight,

    ,0 --AS HIV_Positive_Women_Flag,

    ,0 --AS Pap_Eligible_Flag,

    , 0 --AS Pap_Contraindicated_Flag

    ,0 --AS Pap_Conducted_Flag,

    ,0 --AS Immunization_Eligible_Flag,

    , 0 --AS Immunization_Contraindicated_Flag,

    ,0 --AS Immunization_Complete_Flag,

    ,0 --AS Hypertension_Flag,

    ,0 --AS Hypertension_Controlled_Flag,

    ,'' --AS Blood_Pressure_Value,

    ,0 --AS Diabetes_Flag,

    ,0 --AS Glucose_Test_Value

    , pat.Patient_City

    , pat.Patient_State

    , 0

    , datediff(yy,Patient_Dob,@Dec31Date) -

    case when @Dec31Date < dateadd(yy, datediff(yy,Patient_Dob,@Dec31Date), Patient_Dob) then 1 else 0 end

    , ISNULL(homelessv.Control_Value,''Not Entered'')

    , '' --as Primary_Policy_Insurance_Type

    , 0 --as Best_Served_Non_English

    , ctl.EHR_Active

    , ''

    , pat.EHR_Patient_ID

    , pat.EHR_Account_ID'

    SET @String2 = '

    FROM dbo.PM_Patients as pat

    JOIN dbo.UDS_Control_Table as CTL ON ctl.UDS_Control_ID = @UDS_Control_ID

    LEFT JOIN dbo.PM_Patient_AddlInfo as Seasonal ON pat.Client_ID = Seasonal.Client_ID AND pat.Database_ID = Seasonal.Database_ID AND pat.Patient_ID = Seasonal.Patient_ID AND Seasonal.patient_info_field_id = CTL.Patient_Seasonal

    LEFT JOIN dbo.UDS_Control_Values as Seasonalv ON pat.Client_ID = Seasonalv.Client_ID and pat.Database_ID = Seasonalv.Database_ID AND Seasonal.Field_Value = Seasonalv.Control_Value AND Seasonalv.Control_Type = 2

    LEFT JOIN dbo.PM_Patient_AddlInfo as Migrant ON pat.Client_ID = Migrant.Client_ID AND pat.Database_ID = Migrant.Database_ID AND pat.Patient_ID = Migrant.Patient_ID AND Migrant.patient_info_field_id = CTL.Patient_Migrant

    LEFT JOIN dbo.UDS_Control_Values as Migrantv ON pat.Client_ID = Migrantv.Client_ID and pat.Database_ID = Migrantv.Database_ID AND Migrant.Field_Value = Migrantv.Control_Value AND Migrantv.Control_Type = 3

    LEFT JOIN dbo.PM_Patient_AddlInfo as Homeless ON pat.Client_ID = Homeless.Client_ID AND pat.Database_ID = Homeless.Database_ID AND pat.Patient_ID = Homeless.Patient_ID AND Homeless.patient_info_field_id = CTL.Patient_Homeless

    LEFT JOIN dbo.UDS_Control_Values as Homelessv ON pat.Client_ID = Homelessv.Client_ID and pat.Database_ID = Homelessv.Database_ID AND Homeless.Field_Value = Homelessv.Control_Value AND Homelessv.Control_Type = 4

    LEFT JOIN dbo.PM_Patient_AddlInfo as Veteran ON pat.Client_ID = Veteran.Client_ID AND pat.Database_ID = Veteran.Database_ID AND pat.Patient_ID = Veteran.Patient_ID AND CTL.Patient_Veteran = Veteran.patient_info_field_id

    LEFT JOIN dbo.UDS_Control_Values as Veteranv ON pat.Client_ID = Veteranv.Client_ID and pat.Database_ID = Veteranv.Database_ID AND Veteran.Field_Value = Veteranv.Control_Value AND Veteranv.Control_Type = 5

    LEFT JOIN dbo.PM_Patient_AddlInfo as Pre_Existing ON pat.Client_ID = Pre_Existing.Client_ID AND pat.Database_ID = Pre_Existing.Database_ID AND pat.Patient_ID = Pre_Existing.Patient_ID AND Pre_Existing.patient_info_field_id = CTL.Pre_Existing_Patient

    LEFT JOIN dbo.UDS_Control_Values as Pre_Existingv ON pat.Client_ID = Pre_Existingv.Client_ID and pat.Database_ID = Pre_Existingv.Database_ID AND Pre_Existing.Field_Value = Pre_Existingv.Control_Value AND Pre_Existingv.Control_Type = 6

    LEFT JOIN dbo.PM_Patient_AddlInfo as Trimester ON pat.Client_ID = Trimester.Client_ID AND pat.Database_ID = Trimester.Database_ID AND pat.Patient_ID = Trimester.Patient_ID AND Trimester.patient_info_field_id = CTL.Trimester_Entered

    LEFT JOIN dbo.UDS_Control_Values as Trimesterv ON pat.Client_ID = Trimesterv.Client_ID and pat.Database_ID = Trimesterv.Database_ID AND Trimester.Field_Value = Trimesterv.Control_Value AND Trimesterv.Control_Type = 13

    LEFT JOIN dbo.PM_Patient_AddlInfo as PublicHousing ON pat.Client_ID = PublicHousing.Client_ID AND pat.Database_ID = PublicHousing.Database_ID AND pat.Patient_ID = PublicHousing.Patient_ID AND PublicHousing.patient_info_field_id = CTL.Public_Housing

    LEFT JOIN dbo.UDS_Control_Values as PublicHousingv ON pat.Client_ID = PublicHousingv.Client_ID and pat.Database_ID = PublicHousingv.Database_ID AND PublicHousing.Field_Value = PublicHousingv.Control_Value AND PublicHousingv.Control_Type = 13

    INNER JOIN dbo.DWUDS_Data_Mart_2009_Service s ON s.Client_ID = @Client_ID AND s.Database_ID = @Database_ID AND pat.Patient_ID = s.Patient_ID

    WHERE pat.Client_ID = @Client_ID AND pat.Database_ID = @Database_ID

    OPTION (MAXDOP ' + @CPU_String + ')'

    SET @SQLSubmit = (@String1 + @String2)

    EXEC (@SQLSubmit)

    COMMIT TRANSACTION;

    select 'Hello'

    select @string1

    select @string2

    select @SQLSubmit

    select 'Goodbye'

    "Beliefs" get in the way of learning.

  • February 16, 2010 at 12:41 pm

    #1119466

    There is no value assigned to variable @CPU_String.

    This will cause @String2 to be null (adding NULL to a value will result in NULL again).

    Therefore, your complete SQL statement @SQLSubmit will be changed to NULL as well.

    Solution: Assign a value to @CPU_String. If you don't want to set a value, change it to '' (string of length Zero) after declaration of the variable:

    SET @CPU_String = ''


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 16, 2010 at 12:47 pm

    #1119469

    Ah...there used to be a line that said

    SET @CPU_String = CAST(@CPU_Count as varchar(2))

    in there. I must have accidentally deleted it. Good catch. Thanks a million.

    "Beliefs" get in the way of learning.

  • February 16, 2010 at 12:48 pm

    #1119470

    Easy, the cause is the following lines:

    SET @Jun30Date = CAST('06/30/' + cast(year(@Report_From) as varchar(4)) as datetime)

    SET @Dec31Date = CAST('12/31/' + cast(year(@Report_From) as varchar(4)) as datetime)

    If @Report_From is null, @Jun30Date and @Dec31Date will also be null. Anything concatenated with null is null.

    To test this out, substitute getdate() for @Report_From in the above two lines.

  • February 16, 2010 at 1:01 pm

    #1119478

    Okay, that solves the problem of seeing the string.

    The whole point of this exercise is to set MAXDOP just for this query but the number of cpu's can be variable. Now, I just realized that I have to resolve the other local variables when I build the string as well. Don't know what I was thinking...

    Thanks for your help.

    "Beliefs" get in the way of learning.

  • February 16, 2010 at 1:07 pm

    #1119483

    Lynn Pettis (2/16/2010)

    Easy, the cause is the following lines:

    SET @Jun30Date = CAST('06/30/' + cast(year(@Report_From) as varchar(4)) as datetime)

    SET @Dec31Date = CAST('12/31/' + cast(year(@Report_From) as varchar(4)) as datetime)

    If @Report_From is null, @Jun30Date and @Dec31Date will also be null. Anything concatenated with null is null.

    To test this out, substitute getdate() for @Report_From in the above two lines.

    Looks like each of us found a different reason for string concatenation with NULL values... 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 16, 2010 at 1:50 pm

    #1119525

    lmu92 (2/16/2010)

    Lynn Pettis (2/16/2010)

    Easy, the cause is the following lines:

    SET @Jun30Date = CAST('06/30/' + cast(year(@Report_From) as varchar(4)) as datetime)

    SET @Dec31Date = CAST('12/31/' + cast(year(@Report_From) as varchar(4)) as datetime)

    If @Report_From is null, @Jun30Date and @Dec31Date will also be null. Anything concatenated with null is null.

    To test this out, substitute getdate() for @Report_From in the above two lines.

    Looks like each of us found a different reason for string concatenation with NULL values... 😉

    Just looks like you started at the bottom, and I started at the top. Looks like the OP realized that he needed to check that all the variables had valid values, not null.

  • February 17, 2010 at 4:56 am

    #1119962

    Robert Frasca (2/16/2010)

    The whole point of this exercise is to set MAXDOP just for this query but the number of cpu's can be variable. Now, I just realized that I have to resolve the other local variables when I build the string as well. Don't know what I was thinking...

    Robert,

    Why are you looking up the number of CPUs/sockets/logical processing units and setting this in the MAXDOP hint?

    I don't understand why you would want to do this.

    If you just want SQL Server to use all available processing units, OPTION (MAXDOP 0) will do that for you...

    Paul

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

Viewing 8 posts - 1 through 7 (of 7 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy