1

I'm trying to run a dynamic script to then return the variable so I can pass in to the rest of my script. I've a couple of ways with the help of Google, but I think I still haven't got the syntax correct, therefore getting error or null value returned.

Can someone please advise where I've gone wrong.

For example: To return the value for variable @table_name ASIA is the database and this is set as variable which is appended to the table name that is retrieved from the table and T5148 is the id from the table to turn the table name as is so a variable. I have set this a variables as this script sits when other scripts which loops

Thank you

declare @table_name nvarchar(50)
declare @database nvarchar(50)
declare @id nvarchar(50)
declare @sql nvarchar(max)

set @database = 'ASIA'
set @id = 'T5178'

set @sql = N'SELECT @table_name = ''@database''+table_name 
    FROM ''@database''+tables (NOLOCK)
    WHERE id = ''@id'''

exec sp_executesql @sql, N'@table_name nvarchar(50) output', @table_name   output

select @TRAN_TABLE
Silvia Doomra
  • 947
  • 8
  • 16
HL8
  • 1,369
  • 17
  • 35
  • 49

1 Answers1

2

If I am not wrong, this is what you need :

DECLARE @table_name NVARCHAR(50)
DECLARE @database NVARCHAR(50)
DECLARE @id NVARCHAR(50)
DECLARE @sql NVARCHAR(MAX)

SET @database = 'ASIA'
SET @id = 'T5178'

SET @sql = N'SELECT @table_name = table_name 
    FROM ' + @database+'.dbo.tables (NOLOCK)
    WHERE id = @id'

EXEC SP_EXECUTESQL  @sql, N'@id nvarchar(50),@table_name nvarchar(50) output', 
                    @id = @id, @table_name = @table_name OUTPUT

SET @table_name = @database + @table_name

Note : change dbo as you schema name.

Deep
  • 3,162
  • 1
  • 12
  • 21
  • Thank you. Unfortunately it gives me the following error message: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. – HL8 Jul 02 '15 at 07:04
  • Sorry, I using SQL Server 2012...is it the same? – HL8 Jul 02 '15 at 07:18
  • Works perfect on SS 2014 Expres – CeOnSql Jul 02 '15 at 07:19
  • it should work on SQL Server 2008 and above versions. – Deep Jul 02 '15 at 07:24
  • Can I please ask how do I get the @id variable into single quotes as this field is a varchar type? – HL8 Jul 02 '15 at 07:40
  • I didn't declare @sql as nvarchar(max) – HL8 Jul 02 '15 at 07:40
  • the value of @id is passed as varchar(50) so no need to pt it in ''. – Deep Jul 02 '15 at 08:41
  • So the script will show, where id = 'T5178'? Cause I can't seem to return any value for @table_name. – HL8 Jul 03 '15 at 01:08
  • I had to enter in the following WHERE id = ''' + @id + '''' and it now works. thanks again for all your help, much appreciated. – HL8 Jul 03 '15 at 01:23