TSQL sp_executesql

Question

I have a simple table:

CREATE TABLE [dbo].[test]
(    
    [eins] [varchar](50) NOT NULL,    
    [zwei] [varchar](50) NULL, 

    CONSTRAINT [PK_test] 
    PRIMARY KEY CLUSTERED ([eins] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

with two columns eins und zwei, both varchar(50)

with the values

insert into test(eins, zwei) values(1,2)

1 and 2 in the corresponding columns.

The query

select eins from test

gives the correct result of 1

the following code also gives the correct result of 1 in the results window:

declare 
@in varchar(50),
@sql nvarchar(500)
set @in = 'eins'
set @sql = 'select ' + @in + ' from test'
Exec(@sql)

However, it doesn't make use of an output parameter and I need the result for further processing.

So, I try:

exec sp_executesql N' Select @1 from test where zwei = @2',N'@1 nvarchar(100),@2 nvarchar(100)',@1=N'eins',@2=N'2'

with an expected result of 1. However: the result is eins, i.e., the column name, not the value.

How can I query for something like Select @Variable from @Variable2 where @variabel3 = @Variable4?

The table and columns can be non-variable, if need be, what's primarily important is, the Select @Variable. I need this value for further processing.

I translated to English, so we can keep it alive, assuming @OliverNeis is okay with the translation. — DWright, Mar 23 '15 at 21:22
sp_executesql with parameters will, as you found, exactly _not_ do what you want here: there is no sql parameter datatype for 'the name of a column'. So building up the dynamic sql similar to your first attempt is probably the only realistic way to do it. — Chris F Carroll, Mar 23 '15 at 21:54

score 1 · Answer 1 · answered Mar 23 '15 at 21:44

Try something like this

DECLARE @result int
exec sp_executesql
    N'Select @1=eins from test where zwei = @2',
    N'@1   nvarchar(100) OUTPUT,@2 nvarchar(100)',
    @1=@result OUTPUT,@2=N'2'
SELECT @result

What that does is say that the @1 is an OUTPUT variable inside the EXECed query string. Then it binds @result to the @1, so you can retrieve it. I've never found OUTPUT parameters very intuitive to use.

score 0 · Answer 2 · answered Mar 24 '15 at 16:47

The Code from DWright in the last post has the correct result, but the main problem isn't solved. I dont know the name of the column while writing the code. The following code seems to be correct:

 Declare @result int
Declare @sql nvarchar(500)
Declare @columnname nvarchar(50)

set  @columnname = 'eins'
set @sql= N'Select @1= ' + @columnname +' from test1 where zwei = @2'

exec sp_executesql
    @sql,
    N'@1   nvarchar(100) OUTPUT,@2 nvarchar(100)',
    @1=@result OUTPUT,@2=N'2'
SELECT @result

And the result is the expectet 1

Thank you for helping

TSQL sp_executesql

2 Answers2