Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to call dynamic query stored procedure in select statement Expand / Collapse
Author
Message
Posted Wednesday, July 23, 2014 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:47 PM
Points: 3, Visits: 8
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
Post #1595609
Posted Wednesday, July 23, 2014 12:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 2,400, Visits: 6,655
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.
Post #1595645
Posted Wednesday, July 23, 2014 12:13 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 17,948, Visits: 15,948
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
Post #1595648
Posted Wednesday, July 23, 2014 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:47 PM
Points: 3, Visits: 8

Eirikur Eiriksson thanx for quick reply.


can you please provide me example.
Post #1595658
Posted Wednesday, July 23, 2014 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:47 PM
Points: 3, Visits: 8
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
Post #1595662
Posted Wednesday, July 23, 2014 3:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 40,404, Visits: 36,848
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 2008, MVP
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

Post #1595730
Posted Wednesday, July 23, 2014 3:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 20,801, Visits: 32,725
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1595738
Posted Wednesday, July 23, 2014 10:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 2,400, Visits: 6,655
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.

Post #1595792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse