Use SQL Parameter in a Dynamic SQL Query

jeffreyjseaman
jeffreyjseaman used Ask the Experts™
on
I'm trying to write a dynamic query that sets a parameter = a query value below. But it doesn't work in dynamic sql.
Declare @Name NVarchar(50),
		@TableName NVarchar(255),
		@SQL Varchar(4000)

SET @TableName = '##_TempTable'

SET @SQL = '
	SELECT' + @Name + ' = Country
	FROM' 
	  + Convert(NVarchar(50), @TableName) + '
	WHERE 
	  Country NOT IN
				(
					SELECT DISTINCT
						country 
					FROM
						Countries WITH (NoLock)
					WHERE
						Country IS NOT NULL )
				
  '	  
  PRINT @SQL
  EXEC sp_executesql @SQL

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you are missing a space after FROM:FROM'         + Convert(NVarchar(50), @TableName) + '      WHERE must be:FROM ['         + Convert(NVarchar(50), @TableName) + ']       WHERE the [ ] are to ensure it will also work if the tablename contains spaces...to get the value back, you need to change a bit more:
Declare @Name NVarchar(50),
		@TableName NVarchar(255),
		@SQL Varchar(4000)

SET @TableName = '##_TempTable'

SET @SQL = '
	SELECT @RES = Country
	FROM [' 
	  + Convert(NVarchar(50), @TableName) + ']
	WHERE 
	  Country NOT IN
				(
					SELECT DISTINCT
						country 
					FROM
						Countries WITH (NoLock)
					WHERE
						Country IS NOT NULL )
				
  '	  
  PRINT @SQL
  EXEC sp_executesql @SQL, N'@res nvarchar(50) OUTPUT', @name OUTPUT
  select @name

Open in new window

Author

Commented:
I'll try that. Thanks

Author

Commented:
Thx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial