Dynamic query

Question

Post reply

Dynamic query

htavarez

SSC Rookie

Points: 42
More actions
February 3, 2003 at 11:02 am

#101372

How can I get the results from a dynamic query.
For example,
I know of creating a temp table, exec dynamic query, and read result from temp table. Now this solution does not work on a trigger where you cannot create a table.
Now, the questions still remains.
Is there anything like?
@Result = EXEC sp_executesql @query
FETCH @Result
Hendrix

Viewing 10 posts - 1 through 9 (of 9 total)

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

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

Consider creating a user-defined function that returns a table. Alsao, you can do this:

INSERT #temp_table ( <column list> )

sp_executesql @query

htavarez SSC Rookie Points: 42 More actions · Answer 2

The client is using SQL 7, not support for UDF. I couldn't understand what you mean by

INSERT #temp_table ( <column list> )

sp_executesql @query

quote:
Consider creating a user-defined function that returns a table. Alsao, you can do this:
INSERT #temp_table ( <column list> )
sp_executesql @query

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 3

I meant something like this:

create table #aaa( <table definition> )

insert #aaa( col1, col2, col3 )

execute sp_executesql @query

htavarez SSC Rookie Points: 42 More actions · Answer 4

Remember we cannot create table inside a trigger on SQL 7 🙁 I do the same thing on other scenarios.

quote:
I meant something like this:
create table #aaa( <table definition> )
insert #aaa( col1, col2, col3 )
execute sp_executesql @query

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 5

Are you sue you must have that logic in a trigger, not in a stored procedure?

Are you positive you have to use dynamic SQL?

Antares686 SSC Guru Points: 125444 More actions · Answer 6

In this situation what does @query represent (actual example please) and what are you hoping for @Result to return?

htavarez SSC Rookie Points: 42 More actions · Answer 7

Is there anything like?

@Result = EXEC sp_executesql @query

FETCH @Result

what I mean by that is that it will be cool if for example the "sp_executesql" instead of returning 1 or 0 you could pass a flag to tell it to return a CURSOR or maybe a output CURSOR parameter...

It was not an actual example.

Hendrix

quote:
In this situation what does @query represent (actual example please) and what are you hoping for @Result to return?

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 8

No, you can return only an integer with the RETURN statement from a SP.

tjaybelt SSCrazy Points: 2604 More actions · Answer 9

here's one of my favorite examples of using dynamic sql to get something, and get the result of it in a proc.

create procedure spGetCount

@TableName varchar(100) = null,

@Count integer output

with encryption

as

set NoCount on

--try

declare

@Result integer,

@execStr NVarChar(1024)

select @execStr =

' SELECT @P1 = count(*) from '+@TableName

exec @Result = sp_executesql @execStr, N'@P1 Integer output', @Count output

if @@Error <> 0 or @Result <> 0 goto ErrorProc

-- select @Count as [Count]

--finally

SuccessProc:

return 0/* success */

--except

ErrorProc:

return 1 /* failure */

--end

go

simply by doing an output on the params being passed into the dynamic sql, i can get that value out. This is one of my favorite procedures, and I use this to determine which tables have a lot of data in a new database, so i can concentrate my efforts on learning it there, and not waste time with lookuptables with few records.

I hope this helps.