Setting a variable using select with other variables in the select statement

Question

Setting a variable using select with other variables in the select statement

bswhipp

Ten Centuries

Points: 1117
More actions
December 26, 2017 at 6:07 am

#342178

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

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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.
😎

bswhipp Ten Centuries Points: 1117 More actions · Answer 2

Eirikur Eiriksson - Tuesday, December 26, 2017 6:33 AM
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.
😎

Thanks for the quick reply. I added the specs but still get an error "The parameterized query '(@OpenCNFTotal Numeric(15,5) output)Select Coalesce(sum(BackOAmt' expects the parameter '@OpenCNFTotal', which was not supplied."

This in a report so I really don't want to change databases then change back if I don't have to. The report is run in the database or XXX01 or XXX02 and so on. Each XXX database has an alternate Auxiliary database such as XXXAUX01 goes with XXX01. and so on. The report is run from one central application and needs to be able to select Which XXXAUX database it needs depending on which database it is run from.
Thanks

bswhipp Ten Centuries Points: 1117 More actions · Answer 3

bswhipp - Tuesday, December 26, 2017 6:54 AM
Eirikur Eiriksson - Tuesday, December 26, 2017 6:33 AM
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.
😎
Thanks for the quick reply. I added the specs but still get an error "The parameterized query '(@OpenCNFTotal Numeric(15,5) output)Select Coalesce(sum(BackOAmt' expects the parameter '@OpenCNFTotal', which was not supplied."
This in a report so I really don't want to change databases then change back if I don't have to. The report is run in the database or XXX01 or XXX02 and so on. Each XXX database has an alternate Auxiliary database such as XXXAUX01 goes with XXX01. and so on. The report is run from one central application and needs to be able to select Which XXXAUX database it needs depending on which database it is run from.
Thanks

GOT IT!!

Declare @total Numeric(15,5)
Declare @sql nvarchar(2500)
Declare @param nvarchar(500)
Declare @co char(2) = '01'
declare @date datetime = Cast('01/01/2018' as datetime)
Set @sql = N'Select @totalOut = Coalesce(sum(BackOAmt), 0)
    FRom M2MAux'+@co+'..GetBackOrderDetails_View
    where 1=1 and (fduedate < Cast('''+Convert(varchar(50), @date)+''' as datetime))
    and (Upper(fStatus) = ''OPEN'') and (ShipdtCnfm = 1)'
Set @param = N'@totalOUT numeric(15,2) OUTPUT'
Execute sp_executesql
    @sql,
    @param,
    @totalOUT = @total OUTPUT

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

bswhipp - Tuesday, December 26, 2017 7:12 AM
bswhipp - Tuesday, December 26, 2017 6:54 AM
Eirikur Eiriksson - Tuesday, December 26, 2017 6:33 AM
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.
😎
Thanks for the quick reply. I added the specs but still get an error "The parameterized query '(@OpenCNFTotal Numeric(15,5) output)Select Coalesce(sum(BackOAmt' expects the parameter '@OpenCNFTotal', which was not supplied."
This in a report so I really don't want to change databases then change back if I don't have to. The report is run in the database or XXX01 or XXX02 and so on. Each XXX database has an alternate Auxiliary database such as XXXAUX01 goes with XXX01. and so on. The report is run from one central application and needs to be able to select Which XXXAUX database it needs depending on which database it is run from.
Thanks
GOT IT!!
Declare @total Numeric(15,5)
Declare @sql nvarchar(2500)
Declare @param nvarchar(500)
Declare @co char(2) = '01'
declare @date datetime = Cast('01/01/2018' as datetime)
Set @sql = N'Select @totalOut = Coalesce(sum(BackOAmt), 0)
    FRom M2MAux'+@co+'..GetBackOrderDetails_View
    where 1=1 and (fduedate < Cast('''+Convert(varchar(50), @date)+''' as datetime))
    and (Upper(fStatus) = ''OPEN'') and (ShipdtCnfm = 1)'
Set @param = N'@totalOUT numeric(15,2) OUTPUT'
Execute sp_executesql
    @sql,
    @param,
    @totalOUT = @total OUTPUT

Good stuff!
😎