Need help with Description column in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with Description column in SP

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 14:47:52

HI All,

I am having trouble bringing "description" column in to my sp and them at the out put.
in the ppatient_status_mstr there is a column called “description” which has patient status such as “discharged” “termed collection” etc... How do I use the SP to bring this description into application? I am assigning it to @mytext. I am not sure how to declare “description” in the SP? Every time I use it I get errors like:

Msg 4104, Level 16, State 1, Procedure CheckStatus, Line 20
The multi-part identifier "pm.alerts_ind" could not be bound.
Msg 207, Level 16, State 1, Procedure CheckStatus, Line 40
Invalid column name 'description'.
Msg 207, Level 16, State 1, Procedure CheckStatus, Line 43
Invalid column name 'description'.

I want to say if user picks a description from application . show me that description in output. But I am having trouble how to use the description? I am not sure if my “IF” statements are correct either?

Appreciate any help! Thanks a bunch!!

Here is the procedure
ALTER PROCEDURE [dbo].[CheckStatus] (@enc_id varchar(36) OUTPUT, @Mytext varchar(50) OUTPUT, @data_ind Char(1) OUTPUT)

AS
BEGIN
DECLARE

@alerts_ind char(1)

select @alerts_ind =pm.alerts_ind
,[description] =pm.[description] ---> "I get erors red line"
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id

where pe.enc_id= @enc_id

and pm.patient_status_id in (select patient_status_id from patient_status_mstr pm where p.person_id = ps.person_id and
[description] = 'FFS PT TERMED COLLECTION ' or [description] = 'DISCHARGED FROM PRACTICE : NO meds')

IF @alerts_ind = 'Y'
select @data_ind= 'Y'
else
select @data_ind= 'N'

if [description] = 'FFS PT TERMED COLLECTION #'
select @Mytext = 'FFS PT TERMED COLLECTION # '

if [description] = 'DISCHARGED FROM PRACTICE : no meds'
select @Mytext = 'DISCHARGED FROM PRACTICE : NO meds'

else

select @mytext =''

SET NOCOUNT Off;

end

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 14:54:08

Use @description.

declare @description...

select @alerts_ind =pm.alerts_ind
,@description =pm.[description] ---> "I get erors red line"
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 14:55:17

Why is @enc_id listed as an OUTPUT variable? You aren't setting it to anything in the stored proc's body, so you should remove OUTPUT.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 15:01:24

Thnx Tara but Use @ description is not working. where do I put this before select? Thnx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:05:52

Yes before the select. It's a variable, so you have to declare it. I didn't know what size to put for it, so you'll need to replace "..." with the size you need. It should be set to at least the same size as the column.

And if you want to just output it, then don't declare it and instead put it in the parameter list with OUTPUT after it. There is no reason to use @mytext.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 15:06:20

NOt working:
here is how I am using it:

ALTER PROCEDURE [dbo].[GBCheckPatientStatus] (@enc_id varchar(36) OUTPUT, @Mytext varchar(50) OUTPUT, @data_ind Char(1) OUTPUT)
Use @description

AS
BEGIN
DECLARE

@alerts_ind char(1)

@description char(50)

select @alerts_ind =pm.alerts_ind
, @description =pm.[description]

quote:
Originally posted by tkizer

Use @description.

declare @description...

select @alerts_ind =pm.alerts_ind
,@description =pm.[description] ---> "I get erors red line"
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:09:17

You need a comma.

DECLARE @alerts_ind char(1), @description char(50)

Why is the description column set to char data type? It appears to be variable length data, so therefore varchar should have been used instead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 15:12:12

Sorry still not working?? :) can you give me example on my code? thnx

quote:
Originally posted by tkizer

Yes before the select. It's a variable, so you have to declare it. I didn't know what size to put for it, so you'll need to replace "..." with the size you need. It should be set to at least the same size as the column.

And if you want to just output it, then don't declare it and instead put it in the parameter list with OUTPUT after it. There is no reason to use @mytext.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:14:42

Please post the error and exactly what you have so far.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 15:15:25

Thnx used what u said but get these errors:?
ALTER PROCEDURE [dbo].[CheckStatus] (@enc_id varchar(36) OUTPUT, @Mytext varchar(50) OUTPUT, @data_ind Char(1) OUTPUT)

AS
BEGIN
DECLARE

@alerts_ind char(1)

,@description varchar(50)

select @alerts_ind =pm.alerts_ind
, @description =pm.[description]

Msg 207, Level 16, State 1, Procedure CheckStatus, Line 41
Invalid column name 'description'.
Msg 207, Level 16, State 1, Procedure CheckStatus, Line 44
Invalid column name 'description'.

quote:
Originally posted by tkizer

You need a comma.

DECLARE @alerts_ind char(1), @description char(50)

Why is the description column set to char data type? It appears to be variable length data, so therefore varchar should have been used instead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:15:40

Oh you need to remove "Use @description". That was a sentence in my post, not actual code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:16:18

Where is the rest of your code? You can't just chop it off.

from patient_status_mstr pm
yada yada yada

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:19:05

I changed the parameters as I don't see any reason for @mytext and @data_ind, use @description and @alerts_ind as output parameters.

ALTER PROCEDURE [dbo].[CheckStatus] (@enc_id varchar(36), @description char(50) OUTPUT, @alerts_ind Char(1) OUTPUT)
AS

BEGIN

select @alerts_ind =pm.alerts_ind
,@description =pm.[description] ---> "I get erors red line"
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id
where pe.enc_id= @enc_id
and pm.patient_status_id in (select patient_status_id from patient_status_mstr pm where p.person_id = ps.person_id and
[description] = 'FFS PT TERMED COLLECTION ' or [description] = 'DISCHARGED FROM PRACTICE : NO meds')

end

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 15:19:17

Ok here it is: sorry I thought you have everything:

ALTER PROCEDURE [dbo].[CheckStatus] (@enc_id varchar(36) OUTPUT, @Mytext varchar(50) OUTPUT, @data_ind Char(1) OUTPUT)

AS
BEGIN
DECLARE

@alerts_ind char(1)

,@description varchar(50)

select @alerts_ind =pm.alerts_ind
, @description =pm.[description]
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id

where pe.enc_id= @enc_id
---and [description]= @Mytext

and pm.patient_status_id in (select patient_status_id from patient_status_mstr pm where p.person_id = ps.person_id and
[description] = 'FFS PT TERMED COLLECTION #' or [description] = 'DISCHARGED FROM PRACTICE : NO/MEDS')

IF @alerts_ind = 'Y'
select @data_ind= 'Y'
else
select @data_ind= 'N'

if [description] = 'FFS PT TERMED COLLECTION '
select @Mytext = 'FFS PT TERMED COLLECTION '

if [description] = 'DISCHARGED FROM PRACTICE : NO /MEDS'
select @Mytext = 'DISCHARGED FROM PRACTICE : NO /MEDS'

else

select @mytext =''

SET NOCOUNT Off;

end

GO

quote:
Originally posted by tkizer

Where is the rest of your code? You can't just chop it off.

from patient_status_mstr pm
yada yada yada

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:22:28

See my last reply.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 15:34:53

Thnx its a bit better but if I change the description in the application to something else it always come up with "'DISCHARGED FROM PRACTICE : NO/MEDS' ??? it does not see the first description always defaults to last?

Thnx

quote:
Originally posted by tkizer

I changed the parameters as I don't see any reason for @mytext and @data_ind, use @description and @alerts_ind as output parameters.

ALTER PROCEDURE [dbo].[CheckStatus] (@enc_id varchar(36), @description char(50) OUTPUT, @alerts_ind Char(1) OUTPUT)
AS

BEGIN

select @alerts_ind =pm.alerts_ind
,@description =pm.[description] ---> "I get erors red line"
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id
where pe.enc_id= @enc_id
and pm.patient_status_id in (select patient_status_id from patient_status_mstr pm where p.person_id = ps.person_id and
[description] = 'FFS PT TERMED COLLECTION ' or [description] = 'DISCHARGED FROM PRACTICE : NO meds')

end

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:40:02

I'm not understanding what you are trying to do. I was just fixing the code so that it doesn't error. Does this query return more than one row?

select pm.alerts_ind
,=pm.[description] ---> "I get erors red line"
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id
where pe.enc_id= 'change this to whatever value you are passing in'
and pm.patient_status_id in (select patient_status_id from patient_status_mstr pm where p.person_id = ps.person_id and
[description] = 'FFS PT TERMED COLLECTION ' or [description] = 'DISCHARGED FROM PRACTICE : NO meds')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 16:11:42

Tara, here is what I am trying to do:
1- get the input from the user when they put in the patient status "description"
2-pass that description to application ( what ever that description is)
3- don't worry about enc_id I can set it.
4- right now the code is set to 2 descriptions only:'FFS PT TERMED COLLECTION ' or [description] = 'DISCHARGED FROM PRACTICE : NO meds' and only returns the last one always" 'DISCHARGED FROM PRACTICE : NO meds' "

Is there any ways to set the code to whatever the description is and the SP will pass that description to application?

Tables:
patient_status_mstr --> has the description.
patient_status --->has person_id
person--> has person Id
patient_encounter has person id as well and encounter_id called "enc_id" column.

Hope this helps

Thanks.

quote:
Originally posted by tkizer

I'm not understanding what you are trying to do. I was just fixing the code so that it doesn't error. Does this query return more than one row?

select pm.alerts_ind
,=pm.[description] ---> "I get erors red line"
from patient_status_mstr pm
inner join patient_status ps on ps.patient_status_id = pm.patient_status_id
INNER JOIN patient_encounter pe ON pe.person_id = ps.person_id
INNER JOIN person p ON p.person_id = ps.person_id
where pe.enc_id= 'change this to whatever value you are passing in'
and pm.patient_status_id in (select patient_status_id from patient_status_mstr pm where p.person_id = ps.person_id and
[description] = 'FFS PT TERMED COLLECTION ' or [description] = 'DISCHARGED FROM PRACTICE : NO meds')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 16:14:08

We need to know if the query I posted returns more than one row (fix the part in bold to the enc_id you are testing with).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2013-12-17 : 16:18:39

I get errors" there is an "=" sign in your code?

select pm.alerts_ind
,=pm.[description]

quote:
Originally posted by tkizer

We need to know if the query I posted returns more than one row (fix the part in bold to the enc_id you are testing with).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 16:19:28

Sorry remove the equal sign.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources