Calling Stored Procedures in SQL Statement

How can we call Stored Procedure inside any SQL Statement

For Example.

If we have procedure name sprocCurrentPriority

select * from tablename where colmunname = exec sprocCurrentPriority

Try with this:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
SqlCommand command = conn.CreateCommand())
conn.Open();
command.CommandText = "sprocCurrentPriority";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@.colmunname", SqlDbType.DateTime).Value = DateTime.Now;
command.ExecuteNonQuery();
conn.Close();

|||

What can of output the stored procedure will return?

If it is an OUTPUT parameter, then the statment will be:

1EXEC MyStoredProcedure @.MyOuputParameterint23SELECT *FROM MyTableWhere MyColumn = @.MyOutputParameter

If the output is a result set, the you can load it into a table then do what you want to do with it :)

1Insert into MyTable2exec MyStoerdProcedure

 Note: The returned result set must match the "MyTable" strcture.

Good luck.

|||

EXEC MyStoredProcedure @.MyOuputParameterint

SELECT *FROM MyTableWhere MyColumn = @.MyOutputParameter

Ok.

But when this query will change to

EXEC MyStoredProcedure @.MyOuputParameterint

SELECT top 3 FROM MyTableWhere MyColumn = @.MyOutputParameter

Now for all 3 rows the value of @.MyOutputParameter will same.

But I want that for every row it will re execute the procedure and get the new value.

|||

zeeshanuddinkhan@.hotmail.com:

EXEC MyStoredProcedure @.MyOuputParameterint

SELECT *FROM MyTableWhere MyColumn = @.MyOutputParameter

Ok.

But when this query will change to

EXEC MyStoredProcedure @.MyOuputParameterint

SELECT top 3 FROM MyTableWhere MyColumn = @.MyOutputParameter

Now for all 3 rows the value of @.MyOutputParameter will same.

But I want that for every row it will re execute the procedure and get the new value.

So, lets your stored procedure return the output as a result set.
Then load (or insert) the result set in a table (must both the result set and the table have the same strcture).
Then your query will be:

1SELECT top 3FROM MyTableWhere MyColumnin2 (3Select MyColumnValue4From MyTableHavingTheResultSet5 )6GO

This will do the job; getting top 3 records that match the criteria (condition) you got from the stored procedures [more than a value]

Good luck..

|||

CS4Ever, does a stored procedure be able to return a result set? i thoght it can only return integer value. Could you please show me an example? I would really appreciate your help :)

|||

Bo Chen – MSFT:

CS4Ever, does a stored procedure be able to return a result set? i thoght it can only return integer value. Could you please show me an example? I would really appreciate your help :)

Hi Bo Chen,
Actually once I read your post I become surprised, then when I think again I though you become cofused when I wrote in my last post "stored procedure return result set".
What I meant by return (in my last post) is NOT the Return statment in stored procedure, but this (see the example):

1CREATE PROCEDURE MyStoredProcedure23AS45SET NOCOUNT ON67SELECT EmpID, EmpName, EmpDep8FROM EmployeeTable910SET NOCOUNT OFF

I hope things become clear now :)

Good luck.

|||

Got it . thanks :)