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

Microsoft SQL Server 2005

Avatar of undefined
Last Comment
vivekj2004

8/22/2022 - Mon
Lowfatspread

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?
lludden

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
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
vivekj2004

ASKER
Excellent. Exact solution. Thanks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy