Stored procedure does not return value in column

I have this problem. I have this query in which I used to model a stored procedure and I want to get the value in the column clr_bal_amt. I want it to return as a string from the database column.

However it does not return it. The query executes, but it does not get the value in that column. The stored procedure looks like this

CREATE PROCEDURE FindBranchVaultBalance
    @sol_id varchar(50),
    @acct_crncy_code varchar(50)
AS

DECLARE @query nvarchar(max) = N'
select foracid, acct_crncy_code, clr_bal_amt 
from dummy_table  
where bacid = ''1010000001'' and sol_id = ' + QUOTENAME(@sol_id, '''') + N' and
  acct_crncy_code = ' + QUOTENAME(@acct_crncy_code, '''') + N' and
  del_flg = ''N'' and acct_cls_flg = ''N''
';

DECLARE @sql nvarchar(max) = N'
SELECT * 
FROM OPENQUERY (LINKEDSERVER, N' + QUOTENAME(@query, '''') + N'  )
';

EXEC sp_executesql @sql;

GO

I need some form of clarification. Why did this not return that value in that clr_bal_amt column?

Edited

The SQL is looking like this now

ALTER PROCEDURE [dbo].[FindBranchVaultBalance]
    @sol_id varchar(50),
    @acct_crncy_code varchar(50)
AS

DECLARE @query nvarchar(max) = N'
select foracid, acct_crncy_code, clr_bal_amt 
from dummy_table  
where bacid = ''1010000001'' and sol_id = ' + QUOTENAME(@sol_id, '''') + N' and
  acct_crncy_code = ' + QUOTENAME(@acct_crncy_code, '''') + N' and
  del_flg = ''N'' and acct_cls_flg = ''N''
';

DECLARE @sql nvarchar(max) = N'
SELECT * 
FROM OPENQUERY (LINKEDSERVER, N' + REPLACE(@query, '''','''''') + N'  )
';

EXEC sp_executesql @sql;

Edits2

ALTER PROCEDURE [dbo].[FindAFINACLEBranchVaultBalance]
    @sol_id varchar(50),
    @acct_crncy_code varchar(50)
AS

DECLARE @query nvarchar(max) = N'
select foracid, acct_crncy_code, clr_bal_amt 
from dummy_table
where bacid = ''1010000001'' and sol_id = ' + QUOTENAME(@sol_id, '''') + N' and
  acct_crncy_code = ' + QUOTENAME(@acct_crncy_code, '''') + N' and
  del_flg = ''N'' and acct_cls_flg = ''N''
';

DECLARE @sql nvarchar(max) = N'
SELECT * 
FROM OPENQUERY (LINKEDSERVER, N' + '... N''' + REPLACE(@query, '''','''''') + N''' ...'+ N'  )
';

EXEC sp_executesql @sql;

Is there something I am missing?

Answer

As I mentioned QUOTENAME accepts a sysname as a parameter (a synonym of nvarchar(128) NOT NULL), and your variable @query is defined as an nvarchar(MAX). As such if it’s 129 or more characters long it’ll be truncated; your value is clearly more than 128 characters.

Just use REPLACE:

N'... N''' + REPLACE(@query, '''','''''') + N''' ...'

As a bit of shameless self promotion, I explain this scenario more in-depth here.

To add emphasis on the OP’s attempt to implement this, your attempt is not the same:

N'... N' + REPLACE(@query, '''','''''') + N' ...'

Notice the lack of trailing and leading single quotes ('). The single quotes are needed. REPLACE replaces characters, it doesn’t delimit identify them like QUOTENAME does.

Advertisement

Answer