how to call dynamic query stored procedure in select statement

Question

how to call dynamic query stored procedure in select statement

shah_khan_1289

SSC Enthusiast

Points: 149
More actions
July 23, 2014 at 11:18 am

#397104

Hello,
i have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
now i want to call stored procedure in select statement because stored procedure return a single value.
i search on google and i find openrowset but this generate a meta data error
so how i can resolve it
please reply fast

Viewing 8 posts - 1 through 7 (of 7 total)

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

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 1

shah_khan_1289 (7/23/2014)
Hello,
i have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
now i want to call stored procedure in select statement because stored procedure return a single value.
i search on google and i find openrowset but this generate a meta data error
so how i can resolve it
please reply fast

Quick thought, use exec into a temp table/table variable and select from there.

😎

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Can you provide more detailed information with sample DML?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

shah_khan_1289 SSC Enthusiast Points: 149 More actions · Answer 3

Eirikur Eiriksson thanx for quick reply.

can you please provide me example.

shah_khan_1289 SSC Enthusiast Points: 149 More actions · Answer 4

this is my first sp

create proc createSp1(@acc varchar(20),@columnname varchar(25))

as

declare @value flaot;

set @query='select top 1 @value='+@columnname+' distict where DISTRICT='+@District;

-- select @query;

EXEC sp_executesql @query,N'@value nvarchar OUTPUT',@value OUTPUT;

select @value +100;

end

//---------------

this is my second sp

create proc sp2(@data varchar(20))

set @data='select distinct CURRENT_YR_RATE,exec sp1(acc,j.columname) as estimatedrate from dist j

where DISTRICT IN ('''+@data+''')';

EXEC sp_executesql @data

end

this is my DML

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

In short, you can't do that.

You'll need to either run the proc within a cursor, store the results into a temp table (insert into <table name> exec <proc>) or identify what the statements within the proc are and use those within the select (depending what they are)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

shah_khan_1289 (7/23/2014)
this is my first sp
create proc createSp1(@acc varchar(20),@columnname varchar(25))
as
declare @value flaot;
set @query='select top 1 @value='+@columnname+' distict where DISTRICT='+@District;
-- select @query;
EXEC sp_executesql @query,N'@value nvarchar OUTPUT',@value OUTPUT;
select @value +100;
end
//---------------
this is my second sp
create proc sp2(@data varchar(20))
set @data='select distinct CURRENT_YR_RATE,exec sp1(acc,j.columname) as estimatedrate from dist j
where DISTRICT IN ('''+@data+''')';
EXEC sp_executesql @data
end
this is my DML

It may just be me but are you sure that the first stored procedure actually works?? I don't even see a FROM clause in the dynamic SQL, so where is it pulling data? Also, not too sure about your call to sp_executesql as it doesn't fully resemble anything I have written.

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 7

Slightly puzzled how you got those procedure to work, are you certain that you posted the right code? Quickly looking at the first procedure:

1. Typo, flaot should be FLOAT, this will never work.

2. Missing variable declaration for the @query variable, will error.

3. 'distinct' directive in a wrong place, this will never work.

4. Missing FROM clause, this will never work.

No need to go any further until this is fixed. My suggestion to you is to describe as accurately WHAT you are trying to achieve and we will help you with HOW to do it.

😎