CODEWITHSUNDEEP
mysqlsqlsql-server
Alex Finelt
Alex Finelt

Reputation: 113

Incorrect syntax near 'REPLACE'

the following SQL statement shows solid results with PRINT but with EXEC, gives me Incorrect syntax near 'REPLACE' 

USE EPDB

DECLARE @Table as nvarchar(100)
DECLARE @Column as nvarchar(100)
DECLARE @Select as nvarchar(375)
DECLARE @Where as nvarchar(275)

SET @Table = 'TableABC'
SET @Column = 'ColumnABC'`enter code here`
SET @Select = 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS'
SET @Where = 'CRITERIA = ''VALUE'''


-- EXEC ('SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERETABLE_NAME = ' + @Table + '''')

PRINT @Select + ' WHERE ' + REPLACE(REPLACE(@Where,'CRITERIA','TABLE_NAME'),'VALUE',@Table)

EXEC (@Select + ' WHERE ' + REPLACE(REPLACE(@Where,'CRITERIA','TABLE_NAME'),'VALUE',@Table))

not sure what is going on here

Upvotes: 0

Views: 1997

Answers (1)

suff trek
suff trek

Reputation: 39767

Try this:

DECLARE @sSQL varchar(1000)

SET @sSQL = @Select + ' WHERE ' + REPLACE(REPLACE(@Where,'CRITERIA','TABLE_NAME'),'VALUE',@Table)

EXEC (@sSQL)

I believe EXEC() accepts either string variable or a string constant, but not combination.

Upvotes: 2

Related Questions