CODEWITHSUNDEEP
sqlsql-server
Jesun Bicar
Jesun Bicar

Reputation: 41

Set Variable shows different result SQL

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

enter image description here

Upvotes: 0

Views: 200

Answers (2)

Erik Philips
Erik Philips

Reputation: 54638

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.

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

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.

Upvotes: 0

Related Questions