Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Setting a variable using select with other variables in the select statement RE: Setting a variable using select with other variables in the select statement

  • December 26, 2017 at 6:33 am

    #1973414

    bswhipp - Tuesday, December 26, 2017 6:07 AM

    To all:. Happy Holidays. I  need some help on this statement.

    Currently this works but I need to change it to allow for the ability to change a database name.
    @nextmonth is determined above and is  set to the first day of the next month.

    Declare @OpenCNFtotal numeric(15,5)
    Set @OpenCNFtotal = (Select Coalesce(sum(BackOAmt), 0) as OCO
        From M2MAux01..GetBackOrderDetails_View
        where 1=1 and (fduedate < @nextmonth) and (Upper(fStatus) = 'OPEN') and (ShipdtCnfm = 1))

    What I want to be able to do is change M2MAux01 to M2MAux +  a number based on what database they are in.

    I have tried but for some reason can't get the syntax correct. Any help would be much appreciated. Thanks

    Declare @sql nvarchar(2500)
    Declare @co char(2) = '01'

    SET @sql = 'Select Coalesce(sum(BackOAmt), 0)
        FRom M2MAux'+@co+'..GetBackOrderDetails_View
        where 1=1 and (fduedate < Cast('''+Convert(varchar(50), @nextmonth)+''' as datetime))
        and (Upper(fStatus) = ''OPEN'') and (ShipdtCnfm = 1)'

    Exec sp_executesql
        @query = @sql,
        @params = N'@OpenCNFTotal Numeric output',
        @OpenCNFtotalOUT = @OpenCNFtotal OUTPUT

    First of all you are missing the specs of the numeric (highlighted above), secondly, simply add a USE [DATABASE_NAME] in front of the statement to execute it in another database.
    😎