At a loss with stored procedure for SQL 2005

Question

5.00/5 (1 vote)

See more:

I'm not familar with SQL, I get around with the basics which usually works fine for me.
However, I've come up with a situation where the code will no not run from a SQLDatasource object in VS.

I have, however gotten it to run properly from SSMSE so I want to write it as a stored procedure. But I really have no idea how to even begin. I'm sure easy to do, I just have never done it before.

Here is the SQL that I got to run properly.

SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,
dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,
dbo.Listings.TimesViewed
FROM dbo.Listings INNER JOIN
dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID INNER JOIN
Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID INNER JOIN
Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID
WHERE (dbo.Listings.MemberID = @MemberID)

Any help with this would be greatly appreciated.

modified on Tuesday, August 26, 2008 8:19 PM

Posted 26-Aug-08 13:58pm

Sunset Towers

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

Mycroft Holmes · Answer 1 · 2008-08-26T14:25:00

you are right, it is very simple. Read BOL.

CREATE PROC MyProc<br />--Declare <br />	@MemberID INT<br />	<br />As<br /><br />SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,<br />dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,<br />dbo.Listings.TimesViewed<br />FROM dbo.Listings <br />	INNER JOIN dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID <br />	INNER JOIN Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID <br />	INNER JOIN Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID<br />WHERE (dbo.Listings.MemberID = @MemberID)