Link to home
Create AccountLog in
Avatar of vivekj2004
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

Open in new window

Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

1, the procedure doesn't have any parameters... they are all local...

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?
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'
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of vivekj2004
vivekj2004

ASKER

Excellent. Exact solution. Thanks.