none
How to call Stored procedure in Select statement.

    Question

  • Hello all,

    I have a stored procedure which four input parameters, this stored procedure returns a unique number every time it is executed.

    Now, i have to used this stored procedure in my select statement as shown below

    Select ID,EMP_NAME,Emp_CODE

    from Organization

    This query returns 10 rows, in this the ID column entry should be come from stored procedure.

    Also the ID will be unique?

    Please help me in this scenario.....

    Thanks in advance..


    Pankaj Kumar Yadav-

    Thursday, May 10, 2012 11:27 AM

Answers

All replies

  • Hi Pankaj,

    Your choice is such approach:

    DECLARE @id INT;
    EXEC @id = dbo.usp_yourProc;
    
    SELECT @id AS ID, EMP_NAME,Emp_CODE .. FROM ..


    Of course, your proc should RETURN correctly values 


    Also the ID will be unique?

    If you need to have unique values you should call procedure per row, which is pretty unrecomended way.

    Inline functions like RANDOM(), CHECKSUM() or ROW_NUMBER() are better choices

    Thursday, May 10, 2012 11:32 AM
  • Create a temporary table within a stored procedure and populate it with the a data
    Then the last statement in the stored procedure will be select * from #t so just exec SP and get the data back

    BTW you cannot use Stored procedure within a SELECT (except using OPENQUERY command) 

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, May 10, 2012 11:35 AM
  • If its returning a single unique number use the output paramater to get the results in a variable.

    declare @var int

    exec yrproc @input1,@input2,@input2,@input4,@output=@var

    select @var,* from Organization

    but I am pretty sure this can be easliy doable like this:

    select EMp_code,Emp_Name,row_number()over(order by Emp_code) from Organization


    Thanks and regards, Rishabh K

    Thursday, May 10, 2012 11:40 AM
  • Your choice is such approach:

    DECLARE @id INT;
    EXEC @id = dbo.usp_yourProc;
    
    SELECT @id AS ID, EMP_NAME,Emp_CODE .. FROM ..

    I tried this approach but the ID which i am getting is same in every column rather than unique....

    Any other workaround?


    Pankaj Kumar Yadav-

    Thursday, May 10, 2012 11:47 AM
  • but I am pretty sure this can be easliy doable like this:

    select EMp_code,Emp_Name,row_number()over(order by Emp_code) from Organization

    Stored procedure is necessary as per the design of the module. 

    Pankaj Kumar Yadav-

    Thursday, May 10, 2012 11:49 AM
  • Your choice is such approach:

    DECLARE @id INT;
    EXEC @id = dbo.usp_yourProc;
    
    SELECT @id AS ID, EMP_NAME,Emp_CODE .. FROM ..

    I tried this approach but the ID which i am getting is same in every column rather than unique....

    Any other workaround?


    Pankaj Kumar Yadav-

    As the last resort, if you really need to use logic of the stored procedure and you need unique execution per row, just try to rewrite stored procedure to scalar user defined function.

    Your query will look like:

    SELECT dbo.udf_yourCode(param1, param2, param3, param4) AS ID, EMP_NAME,Emp_CODE .. FROM ..

    But, as warning, scalar UDF has horrible performance on the large set of rows, since it is executed per row.

    There are exists limitations during UDF development, like impossibility to use temp tables or perform any DML operations


    • Edited by Alex Volok Thursday, May 10, 2012 11:54 AM
    Thursday, May 10, 2012 11:53 AM
  • If it could be rewritten to a table value function, then you can "cross apply" with that.
    Thursday, May 10, 2012 12:08 PM
  • You cannot call stored procedures in a SELECT statement.

    Please explain what you really want to achieve. That is, what IDs are to be computed? Why cannot the row_number() function serve you?

    You said in another post Stored procedure is necessary as per the design of the module. What design? Could you clarify?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 10, 2012 10:19 PM
  • Hello Erland,

    As per my application their is an stored procedure which returns a unique 7 digit number 

    for eg: 5000123,5000124,5000125......etc

    Now this number is always incremented by 1 and this works as primary key in other table, where i have to use the select statement 

    for eg: Select ID, EMP_NAME, Emp_CODE from Organization (where ID use the primary key).

    Now as per the requirement i have to generate each unique key in the table by calling stored procedure for each row.

    So, please suggest if their is an appropriate solution to this problem...?


    Pankaj Kumar Yadav-


    • Edited by Pankaj067 Friday, May 11, 2012 4:18 AM
    Friday, May 11, 2012 4:17 AM
  • You can run the stored proc 10 times and store the result in a temp table and then join it while inserting.

    or 

    change your stored proc which generates a unique number to return a series of unique numbers 

    Exec usp_getuniqueID(parameterlist, @id_to_return=10) should return 10 unique IDs and this IDs can be later used in the insert

    Regards
    Satheesh

    Friday, May 11, 2012 4:38 AM
  • You can run the stored proc 10 times and store the result in a temp table and then join it while inserting.

    or 

    change your stored proc which generates a unique number to return a series of unique numbers 

    Exec usp_getuniqueID(parameterlist, @id_to_return=10) should return 10 unique IDs and this IDs can be later used in the insert

    Regards
    Satheesh

    It is not like that, the table will always will return 10 row , It can be dynamic rows also.....sometimes 100 , sometimes 100000,sometimes 0

    ....


    Pankaj Kumar Yadav-

    Friday, May 11, 2012 5:28 AM
  • Hi,

    As I said earlier , you may modify your stored proc which generates the unique IDs to include one more input parameter and this parameter can specify how many number of unique ids to be created(can be 1, 100 or 1000 )

    Later those unique IDs returned from stored procedure can be stored in a temp table and used for inserting using a join

    so your query can look like this

    Select ID,EMP_NAME,Emp_CODE

    from Organization

    if @@rowcount<>0
    begin
    insert into #tempId
    usp_getuniqueID(parameterlist, @id_to_return=@@rowcount) 

    /*now here you can implement your logic to insert the data and join the #temp to have the unique ID
    */
    end

    Regards
    Satheesh


    Friday, May 11, 2012 5:42 AM
  • Call the stored procedure before the SELECT to get an ID. Then do:

    SELECT @id + row_number() OVER(ORDER BY (SELECT 1)) - 1,EMP_NAME,Emp_CODE
    from Organization

    Then you need a provision to say how many numbers you grabbed.

    Or save the result of

    SELECT row_number() OVER(ORDER BY (SELECT 1)) - 1,EMP_NAME,Emp_CODE
    from Organization

    Into a temp table, and then call the procedure to grab the id. Modify the procedure to say how many numbers you want at once.

    Or upgrade to SQL 2012, where sequences is a built-in feature, and you don't need to mimick it with stored procedures.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 11, 2012 7:10 AM
  • remove stored procedure and create function with same same code just need few changes and you can use it in select.

    Regards,

    nishant


    nishantcomp2512

    Friday, May 11, 2012 7:30 AM
  • Store procedure cannot be used in select statement.You can re-write this SP in Function and call this function in Select statement as you need.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, December 05, 2012 4:29 AM
  • Not able to use sp in a select statement 

    kinldy change that Sp into a UDF and try again 

    once u feel the performance issue .

    try to use the select query in your sp and return a single result set as need.


    ------------ Samith C Valsalan There is no Wrong time to do Right thing :) Please mark it as an answer/helpful if you find it as useful.

    Wednesday, December 05, 2012 9:28 AM
  • You cannot call SP in select using t-sql

    Pankaj Kumar Yadav-

    • Marked as answer by Pankaj067 Monday, April 01, 2013 6:49 AM
    Monday, April 01, 2013 6:49 AM