need help with store proc - SQL Server Forums

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 store proc

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-14 : 16:49:01

Hi Team,

I have an application for person medical record. Once I bring up a patient/person in the application I want to get that patient Id and pass it on to a SP and populate a table called "dbo.ngweb_bulk_enrollments". What I am trying to do is to auto populate this table from the application using SP. Here is my code so far but not sure how to do this , I mean getting the person id from the application and pass it on to the table? the table has 12 columns that must be populated from a table called "person". for now When I run the SP it only populates on person and that person is not in the application that I opened? I am not sure where this person comes from? Much appreciate any help!

my SP:
begin
declare
@Person_ID uniqueidentifier,
@row_id varchar(36),
@user_id varchar(36)
select @person_id =p.person_id,
@row_id=newid(),
@user_id=user_id
from person p
inner join user_mstr um on um.practice_id=p.practice_id

-- begin

INSERT INTO dbo.ngweb_bulk_enrollments(row_id, person_id,practice_id, user_name,password,security_answer,forgot_password_question,forgot_password_answer,created_by,create_timestamp,
modified_by,modified_timestamp)
VALUES (newid(),@person_id,'0001','','','','','',@user_id,current_timestamp,'',current_timestamp)
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-14 : 17:16:33

You are missing a WHERE clause for your SELECT statement.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-14 : 17:31:20

Thanks but I am still not getting the results I am looking for.
Pasi

quote:
Originally posted by tkizer

You are missing a WHERE clause for your SELECT statement.

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-14 : 17:36:16

What results are you looking for? We can't see your database so it makes it pretty tough for us to guess at what result your code is producing. If you want help you can post sample data so we can run your query against it. Then you can tell us what you want for a result and we can help you get there.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-14 : 17:53:08

quote:
Originally posted by Pasi

Thanks but I am still not getting the results I am looking for.
Pasi

quote:
Originally posted by tkizer

You are missing a WHERE clause for your SELECT statement.

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

You still need to add the appropriate WHERE clause. That's why it's not working right.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-14 : 18:08:11

Its kind of hard to show you and I understand your point. because I have to join few tables to extract data and the final data should go to bulk_enrollment. Not sure how to do this?
Pasi

quote:
Originally posted by Lamprey

What results are you looking for? We can't see your database so it makes it pretty tough for us to guess at what result your code is producing. If you want help you can post sample data so we can run your query against it. Then you can tell us what you want for a result and we can help you get there.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-14 : 18:11:41

select @person_id =p.person_id,
@row_id=newid(),
@user_id=user_id
from person p
inner join user_mstr um on um.practice_id=p.practice_id
YOU NEED A WHERE CLAUSE HERE.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-14 : 18:29:33

I put in the where clause but getting error:

from person p
inner join user_mstr um on um.practice_id=p.practice_id
inner join nxmd_demographics_ nd on nd.person_id= p.person_id
where p.person_id = @Person_ID

error is:

Msg 515, Level 16, State 2, Line 16
Cannot insert the value NULL into column 'person_id', table 'NGDevl.dbo.ngweb_bulk_enrollments'; column does not allow nulls. INSERT fails.
The statement has been terminated.

quote:
Originally posted by tkizer

select @person_id =p.person_id,
@row_id=newid(),
@user_id=user_id
from person p
inner join user_mstr um on um.practice_id=p.practice_id
YOU NEED A WHERE CLAUSE HERE.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-14 : 18:31:17

Your code doesn't make sense. You are putting the person_id value into the parameter and then also filtering on it in the where clause.

You are going to need to post some sample data for us to help you.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-14 : 18:33:39

HOw do I provide sample data or jpg?

quote:
Originally posted by tkizer

Your code doesn't make sense. You are putting the person_id value into the parameter and then also filtering on it in the where clause.

You are going to need to post some sample data for us to help you.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-14 : 18:38:11

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-14 : 19:03:51

Thanks how do you post file/jpg on this thing?

quote:
Originally posted by tkizer

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-14 : 19:19:39

quote:
Originally posted by Pasi

Thanks how do you post file/jpg on this thing?

quote:
Originally posted by tkizer

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

You'd have to upload it somewhere and then share the link with us. We'd rather you followed the instructions in the link I posted.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-15 : 12:19:13

Tara,

I read the instruction and its vague and not sure how to post sample that I don't have? I am not sure what I have to send you, I am trying to come up with this SP to populate table which only has headers no data yet?

Like I said there are bunch of tables that I need to pull info to construct this SP.

Pasi.

quote:
Originally posted by tkizer

quote:
Originally posted by Pasi

Thanks how do you post file/jpg on this thing?

quote:
Originally posted by tkizer

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

You'd have to upload it somewhere and then share the link with us. We'd rather you followed the instructions in the link I posted.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-16 : 16:57:27

HI Tara

As for this SP I am working on, I wanted to see if you can just direct me to the point. What I am trying to do with SP is I have an application called EPM which is patient registration. once opened and a patient chart is brought up in EPM, the person/patient has a person_id within dB. Which resides in a table called “person” Now I want to pass this patient ( person_id) that I have currently open in EPM to the store procedure to populate a table called dbo.ngweb_bulk_enrollments".

How do I tell the store proc that each time I open the application EPM, and bring up a patient, get that patient/person_id and pass it on to store proc Here is my SP: I am not sure if the code is correct?

Thanks a lot!
Pasi

Note the person_id is a uniqidentifier.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALter PROCEDURE PatientAutoEnroll (@person_id AS uniqueidentifier)

AS

BEGIN

declare
--@Person_ID uniqueidentifier,
@row_id varchar(36),
@user_id varchar(36),
@email_address varchar(36),
@practice_id char(5)

select
--p.person_id ,
@row_id=newid(),
@user_id=user_id,
@practice_id=pe.practice_id

from person p
inner join user_mstr um on um.practice_id=p.practice_id
inner join nxmd_demographics_ nd on nd.person_id= p.person_id
inner join patient_encounter pe on pe.person_id =nd.person_id
inner join patient d on d.person_id =p.person_id
where p.person_id =@person_id
and email_address <>''

--where nd.Person_ID=
-- begin

INSERT INTO dbo.ngweb_bulk_enrollments(row_id, person_id,practice_id, user_name,password,security_answer,forgot_password_question,forgot_password_answer,created_by,create_timestamp,
modified_by,modified_timestamp)
VALUES (newid(),@Person_ID,'0001','gbile','ABc23','Red','color','Red',@user_id,current_timestamp,'',current_timestamp)

SET NOCOUNT off;

END
GO

quote:
Originally posted by tkizer

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-16 : 17:24:54

quote:

How do I tell the store proc that each time I open the application EPM, and bring up a patient, get that patient/person_id and pass it on to store proc

That would be handled in your application. I am not an application developer, so I can't specifically help with that. If you posted what language you are using for the application, then maybe someone else that has experience with that language can help.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-16 : 17:27:46

I would probably change it to this as there doesn't seem to be a need to save the select data into variables when you can just use insert/select.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:	 <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALter PROCEDURE PatientAutoEnroll (@person_id AS uniqueidentifier)

AS	

BEGIN

SET NOCOUNT ON

INSERT INTO dbo.ngweb_bulk_enrollments(row_id, person_id,practice_id, user_name,password,security_answer,forgot_password_question,forgot_password_answer,created_by,create_timestamp,
modified_by,modified_timestamp)
SELECT newid(),p.person_id,'0001','gbile','ABc23','Red','color','Red',[user_id],current_timestamp,'',current_timestamp
from person p
inner join user_mstr um on um.practice_id=p.practice_id
inner join nxmd_demographics_ nd on nd.person_id= p.person_id
inner join patient_encounter pe on pe.person_id =nd.person_id
inner join patient d on d.person_id =p.person_id
where p.person_id =@person_id
and email_address <>''

END
GO

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-16 : 17:51:42

Thanks for modifying it! I get errors "saying the procedure, expecting parameters @person_id which was not supplied" I did get the same error with my code..? do you know why?
Thanks.

quote:

Originally posted by tkizer

I would probably change it to this as there doesn't seem to be a need to save the select data into variables when you can just use insert/select.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:	 <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALter PROCEDURE PatientAutoEnroll (@person_id AS uniqueidentifier)

AS	

BEGIN

SET NOCOUNT ON

INSERT INTO dbo.ngweb_bulk_enrollments(row_id, person_id,practice_id, user_name,password,security_answer,forgot_password_question,forgot_password_answer,created_by,create_timestamp,
modified_by,modified_timestamp)
SELECT newid(),p.person_id,'0001','gbile','ABc23','Red','color','Red',[user_id],current_timestamp,'',current_timestamp
from person p
inner join user_mstr um on um.practice_id=p.practice_id
inner join nxmd_demographics_ nd on nd.person_id= p.person_id
inner join patient_encounter pe on pe.person_id =nd.person_id
inner join patient d on d.person_id =p.person_id
where p.person_id =@person_id
and email_address <>''

END
GO

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-16 : 18:04:55

It's because your stored procedure has an input parameter defined. You need to pass it a value.

EXEC StoredProcName @var1 = 'value1', @var2 = 'value2'

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-16 : 18:06:14

Thanx How do I include this in SP? Sorry I am just a bit confused.
Pasi

quote:
Originally posted by tkizer

It's because your stored procedure has an input parameter defined. You need to pass it a value.

EXEC StoredProcName @var1 = 'value1', @var2 = 'value2'

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-16 : 19:05:22

You don't include it inside the stored procedure. If you are just testing it, then you'd run it in a new query window in Management Studio using my example as a template. If you want to test it inside your application, then you need to make the appropriate code changes to call a stored procedure that has an input parameter (I can't help with that part as I am not a developer).

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