vivekj2004
asked on
SP parameters not evaluating
SP parameters are not evaluating in the following piece of code. In the print statement I can see that the parameters are not evaluating.
ALTER PROC TestProc
AS
DECLARE @sql NVARCHAR(4000),
@LocalDBTableName NVARCHAR(300),
@TelDBTableName NVARCHAR(300),
@LocalDBKeyName NVARCHAR(300),
@TelDBKeyName NVARCHAR(300),
@LocalDBFieldName NVARCHAR(300),
@TelDBFieldName NVARCHAR(300),
------------------------------
-- @LocalDBKeyVal
-- @TelDBKeyVal
-- @
@ErrorMsg NVARCHAR(2000)
/*
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' +
CAST(@EmpID AS NVARCHAR(10))
/* Execute Transact-SQL String */
EXECUTE(@SQLQuery) */
SET @LocalDBTableName='SDNTABLE'
SET @TelDBTableName='TelSDN'
SET @LocalDBKeyName='ListID'
SET @TelDBKeyName='SdnEntry_Id'
SET @LocalDBFieldName='FirstName'
SET @TelDBFieldName='FirstName'
SET @sql = 'INSERT INTO OFACRecon_Report (@LocalDBTableName, @TelDBTableName, @LocalDBKeyName, @TelDBKeyName,
@LocalDBFieldName , @TelDBFieldName,
@LocalDBKeyName, @TelDBKeyName, @LocalDBFieldName , @TelDBFieldName)
SELECT '+'''@LocalDBTableName'', ''@TelDBTableName'', ''@LocalDBKeyName'', ''@TelDBKeyName'', ''LocalDBFieldName'', ''TelDBFieldName'',
r.@LocalDBKeyName,
t.@TelDBKeyName,
ISNULL(r.@LocalDBFieldName,''''),
ISNULL(t.@TelDBFieldName,'''')
FROM TelSDN..@TelDBTableName t INNER JOIN OFAC..@LocalDBTableName r
ON (t.@TelDBKeyName=r.@LocalDBKeyName)
WHERE r.ListType=''OFAC''
AND ISNULL(t.@TelDBFieldName,'''')<>ISNULL(r.@LocalDBFieldName,'''')'
----EXECUTE(@sql)
----EXEC TestProc
PRINT @sql
One thing that I do when building large dynamic queries, is to use a variable for the single quote. It makes it easier to read and debug.
DECLARE @QT char(1)
SET @QT = CHAR(39)
DECLARE @MySearchString varchar(50)
SET @MySearchString = 'Test'
DECLARE @mySQL varchar(4000)
SET @MySQL = 'SELECT * FROM myTable where MyString = ' + @QT + @MySearchString + @QT + ' AND DateField BETWEEN ' + @QT + '2011-01-01' + @QT + ' AND ' + @QT + '2011-05-30' + @QT
PRINT @MySQL
SELECT * FROM myTable where MyString = 'Test' AND DateField BETWEEN '2011-01-01' AND '2011-05-30'
DECLARE @QT char(1)
SET @QT = CHAR(39)
DECLARE @MySearchString varchar(50)
SET @MySearchString = 'Test'
DECLARE @mySQL varchar(4000)
SET @MySQL = 'SELECT * FROM myTable where MyString = ' + @QT + @MySearchString + @QT + ' AND DateField BETWEEN ' + @QT + '2011-01-01' + @QT + ' AND ' + @QT + '2011-05-30' + @QT
PRINT @MySQL
SELECT * FROM myTable where MyString = 'Test' AND DateField BETWEEN '2011-01-01' AND '2011-05-30'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent. Exact solution. Thanks.
2. the insert column names are left as parameter variables is that correct (would need [] around them)
3. you don't have consistent quotes and + ....
what are you trying to do?
why are you using dynamic sql , which in general is a poor design decision?