Solved

Returning rows with a stored procedure

Posted on 2012-03-22
3
177 Views
Last Modified: 2012-03-29
Hi

I hope somebody can help me with the following.

I need to retrieve some records using a SELECT but also using a stored procedure.

Suppose the table (myTable) I have has the following format:

ID      NAME     DOB     SCORE

I have a stored procedure myStoredProcedure(NAME, X, Y, Z)

where NAME and SCORE are values passed in and X, Y and Z are returned values.

This is the pseudo code I'm trying to do

SELECT * FROM myTable WHERE myTable.SCORE = X "using" myStoredProcedure(myTable.NAME, X, Y, Z)

where X is an output parameter from the stored procedure using values from myTable.

Can anybody help?

Thanks in advance

John
0
Comment
Question by:IssacJones
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 300 total points
Comment Utility
Create a user defined scalar function to do the same work myStoredProcedure and return the required value

CREATE FUNCTION ......

Then
SELECT * FROM myTable WHERE myTable.SCORE = dbo.udfYourFunction(NAME, X, Y, Z)
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
The function will have just return a single value - Why are you returning 3 different values - What is the business logic?
May have to look at using a table valued function and cross apply
0
 

Author Comment

by:IssacJones
Comment Utility
the 3 different values are used in other cases. I'll try your first idea.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now