Set Variable shows different result SQL

Question

I was trying to do a stored procedure declaring variable and setting its values, but it shows different values compare to the one I set.

CREATE PROCEDURE [dbo].[sp_InsertAccount]
    @OldAccount AS VARCHAR(25),
    @NewAccount AS VARCHAR(25),
    @TableName AS VARCHAR(25)
AS
    DECLARE @SQL VARCHAR(MAX)
    DECLARE @TableName_ sysname

    SET @OldAccount = '7006-0090-0280'
    SET @NewAccount ='5555-0090-0280'
    SET @TableName_ = 'cust'

    SELECT @NewAccount

    SELECT @SQL = COALESCE(@SQL + ', ', '') + Name 
    FROM sys.columns
    WHERE OBJECT_ID = OBJECT_ID(@TableName_)
      AND name NOT IN ('custnum');

    SELECT @SQL = 'SELECT '+ @NewAccount+','+@OldAccount+',' + @SQL + ' FROM '   + @TableName_+' where custnum ='''+@OldAccount+''''
    EXEC (@SQL)
GO

Here is a hint: `5555 - 90 - 280 = 5185` and `7006 - 90 - 280 = 6636`. — Erik Philips, Dec 12 '19 at 02:34
@ErikPhilips..Ohh it changed into a formula?...even though i declared it as varchar.... any solution you can offer?..Thanks — Jesun Bicar, Dec 12 '19 at 02:37
It's dynamic SQL, you don't have single quotes around it so it assumes it's math. Basically you said `Select 5555 - 90 - 280` not `Select '5555-0090-0280'`. That being said, it would be better to use a [parameterized query](https://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters/28481277). — Erik Philips, Dec 12 '19 at 02:39
`SELECT @SQL = 'SELECT '''+ @NewAccount+''', '''+@OldAccount+''',' + @SQL + ' FROM ' + @TableName_+' where custnum ='''+@OldAccount+''''` — Ed Bangga, Dec 12 '19 at 02:44
Got it now, Thanks..You can add your last comment on as answer. And ill upvote it Thanks — Jesun Bicar, Dec 12 '19 at 02:44

score 2 · Accepted Answer · answered Dec 12 '19 at 02:52

You're dynamic query does not have quotes around the values, so the SQL standard response is to use math.

example

'SELECT '+ @NewAccount

yields

SELECT 5555 - 90 - 280

The correct way so to

SELECT ''' + @NewAccount + ''''

That being said, you should be really careful with dynamic sql, allowing external sources to build their own query is asking for issues. One way to prevent some issues is to use Parameterized Queries.

score 0 · Answer 2 · answered Dec 12 '19 at 02:50

If you are going to print(@sql) this will give you.

SET @SQL = 'SELECT '+ @NewAccount+','+@OldAccount+',' + @SQL + ' FROM ' + @TableName_+' where custnum ='''+@OldAccount+''''
print @SQL


SELECT 5555-0090-0280,7006-0090-0280, .... FROM tname_cust where custnum ='7006-0090-0280'

so its will give you the difference, not as string.

Change your last @sql statement to:

SELECT @SQL = 'SELECT '''+ @NewAccount+''', '''+@OldAccount+''',' + @SQL + ' FROM ' + @TableName_+' where custnum ='''+@OldAccount+''''

Output:

SELECT '5555-0090-0280', '7006-0090-0280', .... FROM tname_cust where custnum ='7006-0090-0280'

To treat your variables as string not as arithmetic expression.

Set Variable shows different result SQL

2 Answers2