stored proc start with 1 parameter then change?

[jmj]

I think this should be relatively straight forward.
I need to make a series of select statements. The 1st select statement pulls all the main goals for a person. The sub queries of that pull all the different data relavent to that goal.

So the first sql statement may be
create procedure getallgoals
@personid Integer
AS
Select GoalID, Goal, personid
From tbl_goal
where personid=@personid
NOw what I need to do is take that GoalID and use it for the rest of my sql statements....
Select timeid, Timeframe, months, weeks
from tbl_GoalTimetable
where GoalId=@GoalID
SELECT healthID, healthgoal
from tbl_GoalHealth
where GoalID=@GoalID
etc.
so there can be many Main goals, and each main goal can have multiple subgoals (so a person can have multiple health goals under the main goal of "Adopt a healthy lifestyle"- exercise daily/eat more fruit/etc

 

[hneal98]

just put in your Declare statement however many times you need it. Each time you declare, it resets your @Goalid.

The other way to do it would be to declare a @Goalid1, @Goalid2, @Goalid3, etc.

Declare @Goalid char(15)

 

[jmj]

Ok I must not be putting it in the right place? Each time I try I get no records for any of the sub select statements. I've tried to set the delcare statement
1. right after the first select statement (because that is the query that gives the goalid)
2. Right before the select statement (right after the As statement) to declare it before the first select.

 

[hneal98]

Can you paste your select statement here?

 

[jmj]

Here's part of it. All the queries rely on the GoalID so I didn't want to overload with lots of "sameness". The original variable- clientid is passed in from a web app.

CREATE PROCEDURE prGetClientGoal
@ClientID INTEGER
AS
Select LastName, FirstName, GoalID as GoalID, Goal
from tbl_Client c inner join tbl_Goal g
on c.ClientID=g.ClientID
where c.ClientID=@ClientID

DECLARE @GoalID integer

SELECT gs.GoalStepID, gs.StepNum, gs.SteptoGoal, gt.timeline
From tbl_GoalStep gs inner join tbl_GoalTimeline gt
on gs.GoalStepID=gt.GoalStepID
where gs.GoalID=@GoalID
order by StepNum

SELECT GoalResourceID, Resource, Priority
from tbl_GoalResource
where GoalID=@GoalID

 

[hneal98]

I steared you wrong a bit. My apolgies. Here is how it should be:

CREATE PROCEDURE prGetClientGoal
@ClientID INTEGER
AS

DECLARE @GoalID integer

Select
LastName,
FirstName,
GoalID as GoalID,
Goal
into #Goal
from
tbl_Client c
inner join tbl_Goal g on c.ClientID=g.ClientID
where
c.ClientID=@ClientID


select @Goalid = g.GoalID from #Goal


SELECT
gs.GoalStepID,
gs.StepNum,
gs.SteptoGoal,
gt.timeline
From
tbl_GoalStep gs
inner join tbl_GoalTimeline gt on gs.GoalStepID=gt.GoalStepID
where
gs.GoalID=@GoalID
order by StepNum


SELECT
GoalResourceID,
Resource,
Priority
from
tbl_GoalResource
where
GoalID=@GoalID

 

[jmj]

That gave me some weird results, in fact no results show.
If I run the stored procedure from the query analyzer it won't show any grid data (ie: won't show the goal/goalid/it), but on the messages tab the correct number of records found for the 1st and only 1st sql statement. The other queries state 0 rows affected.
ie:
(2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Any ideas? Thanks for your continued help.

 

[hneal98]

is the GoalID integer or some other data type?

 

[jmj]

GoalID is an integer.

 

[hneal98]

Let me explain to you what is happening in your procedure.

1st. We are getting the clientid and putting it in the @ClientID
2nd. We create a temp table called #Goal in which we get the GoalID based on the @ClientID
3rd. We assign the GoalID to @Goalid so it can be used in the other queries
4th. We use it in the other queries.

The only reason I can see that this would not work is if your goalid is not integer, but maybe string.
IF that is the case, change the datatype in the declaration.

And you could actually change your procedure to look like this

CREATE PROCEDURE prGetClientGoal
@ClientID INTEGER
AS

DECLARE @GoalID integer

Select
@GoalID = g.GoalID
from
tbl_Client c
inner join tbl_Goal g on c.ClientID=g.ClientID
where
c.ClientID=@ClientID

SELECT
gs.GoalStepID,
gs.StepNum,
gs.SteptoGoal,
gt.timeline
From
tbl_GoalStep gs
inner join tbl_GoalTimeline gt on gs.GoalStepID=gt.GoalStepID
where
gs.GoalID=@GoalID
order by StepNum


SELECT
GoalResourceID,
Resource,
Priority
from
tbl_GoalResource
where
GoalID=@GoalID

 

[jmj]

I do understand what it's suppose to do. However, everytime I add the Goalid into #Goal my stored proc quits working.
I tried the other one you just sent, no luck there either.
I even stripped all but the needed variables, to make sure I wasn't making a mistake somewhere else.

 

[hneal98]

What are the data types of the id fields?

 

[jmj]

ClientID varchar(19)
GoalID integer and all other ids are integers.
I figured it wouldn't matter what the data type would be of ID in the 1st query. Am I wrong? Is it a problem to have ClientID as a varchar? If so, why?

 

[hneal98]

If your client ID is varchar, you should declare it as such in the procedure. Varchar means it can take any type, but that does not mean it can put out that type. In onther words, if you have something like this:

Declare @ClientID integer

Select * from tbl_Client where @ClientID = ClientID

That would not work, because you are comparing two different datatypes. You could change it to this and make it work:

Select * from tbl_Client where @ClientID = convert(integer,ClientID)

 

[jmj]

Actually it is correct in my stored procedure. Still no change.

 

[eveCalypso]

Dear All,

I am delving into dynamic stored procedures. I think what I want to do is pretty straight forward. I have 5 possible paramters of which only 2 will always be present (dates).
So, for example:
@anIDColumn int
@toDate datetime
@fromDate datetime
@anInvoiceID int
@BinID char(12)

I want a dynamic stored procedure which will decide which parameters are needed based on what comes into the procedure.
I learnt about EXEC @sqlStr
which is a String I can build, but because I need to convert all the fields to varChar(x), my dates are in the wrong format and the query fails!!

So I then tried sp_executeSql, but I can only set up the SQL statement once - which means that, for example, excluding the invoice number OR setting the invoice to null or setting it to a specific value requires different SQL wording every time - based on the incoming value...

Does anyone have any idea what I could do to make this selection work?? What I am doing is asking the user for search inputs to narrow down which transactions they want to see for an Edit. (between dates, specific invoice, specific item etc).

Any advice would be appreciated.
Regards,
EvE

 

[hneal98]

jmj, what is the exact messages, or responses you get when you run the stored procedure? As I have out lined above, should work. I am not understanding why it is not.

 

[jmj]

Ok it's sort of working.
When I explain this please remember I'm running the stored procedure from my query analyzer.
There is no error message.
Problems that I'm having:
1. The query that has the INTO #TempTable statement does not show any results in the grid. The message shows there’s 2 records and passes on the goalid. (Originally I wanted this query to give me the Main goal info. Guess I can pull that from a different query? ) I guess I’m wondering it that’s normal behavior when you add a temp table into a select query.
2. Is there a way for it to pull multiple goals. The analyzer says there 2 goals for this person. All the subqueries only show the results for 1 goal. Do I need to add a loop here?
Thanks for your perseverance with this problem!

 

[hneal98]

Ok. I see what you are doing. Do it this way instead:

CREATE PROCEDURE prGetClientGoal
@ClientID INTEGER
AS


SELECT
gs.GoalStepID,
gs.StepNum,
gs.SteptoGoal,
gt.timeline
From
tbl_GoalStep gs
inner join tbl_GoalTimeline gt on gs.GoalStepID=gt.GoalStepID
where
gs.GoalID in(Select distinct g.GoalID
from tbl_Client c inner join tbl_Goal g on c.ClientID=g.ClientID
where c.ClientID=@ClientID)
order by StepNum


SELECT
GoalResourceID,
Resource,
Priority
from
tbl_GoalResource
where
GoalID in(Select distinct g.GoalID
from tbl_Client c inner join tbl_Goal g on c.ClientID=g.ClientID
where c.ClientID=@ClientID)