Dynamic query error

Question

Dynamic query error

dva2007

SSCertifiable

Points: 7707
More actions
October 23, 2008 at 3:40 am

#383199

Dynamic query error
hi,
my query is below:
declare @myfield varchar(20)
declare @mysql nvarchar(100)
declare @myresult varchar(100)
set @myfield='CategoryName'
set @mysql='select ' + @myfield + ' from Categories where categoryid= 1'
set @myresult = exec(@mysql)
The problem is I'm not able to store the result to @myresult.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 1

dva2007 (10/23/2008)
Dynamic query error
hi,
my query is below:
declare @myfield varchar(20)
declare @mysql nvarchar(100)
declare @myresult varchar(100)
set @myfield='CategoryName'
set @mysql='select ' + @myfield + ' from Categories where categoryid= 1'
set @myresult = exec(@mysql)
The problem is I'm not able to store the result to @myresult.

What you are trying to achieve with this code is a bit confusing to me but still. As per your code, you are trying to insert the output of a query into a VARCHAR column. Why are you doing this dude? What if the query return more than one value? And even if it does, this is not the right way to fetch data from the dynamic SQL.

Dynamic SQL runs in its own scope, meaning any variable or temp table you define INSIDE the dynamic SQL, will not be accessible outside.

Considering your scenario, I AM ASSUMING THAT YOU ARE SURE THAT YOUR QUERY WILL RETURN ONLY ONE VALUE, you can do the following:

declare @myfield varchar(20)

declare @mysql nvarchar(100)

declare @myresult varchar(100)

create table #temp

(col1 varchar(100))

set @myfield='CategoryName'

set @mysql='insert into @temp select ' + @myfield + ' from Categories where categoryid= 1'

exec(@mysql)

select @myresult = col1 from #temp

I hope it helps.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Also read about sp_executesql in sql server help file

Madhivanan

Failing to plan is Planning to fail

sgmunson SSC Guru Points: 110640 More actions · Answer 3

I'm pretty sure there's a typo in the previously supplied solution...

Considering your scenario, I AM ASSUMING THAT YOU ARE SURE THAT YOUR QUERY WILL RETURN ONLY ONE VALUE, you can do the following:
declare @myfield varchar(20)
declare @mysql nvarchar(100)
declare @myresult varchar(100)
create table #temp
(col1 varchar(100))
set @myfield='CategoryName'
set @mysql='insert into #temp select ' + @myfield + ' from Categories where categoryid= 1'
exec(@mysql)
select @myresult = col1 from #temp

Take note of the change to a "#" from "@". Also, I'm not sure if the change in scope will disallow access to the temp table created in the previous code or not, so if anyone knows how that works, let me know.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

david.tyler SSCrazy Points: 2206 More actions · Answer 4

Another option is to use a table variable to store your results. As long as you know the structure of the resulting data, you can setup your table variable as follows. This method is an alternate to using temp table that consume space in tempdb.

DECLARE @myfield VARCHAR(20)

declare @mysql NVARCHAR(200)

SET @myfield='CategoryName'

SET @mysql=

'

DECLARE @myresult TABLE

(

Col1 VARCHAR(20)

);

INSERT INTO @myresult

SELECT ' + @myfield + ' FROM Categories WHERE categoryid = 1;

SELECT

Col1

FROM

@myresult;

'

EXEC sp_executesql @mysql