database call syntax error - 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

database call syntax error

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-22 : 18:36:26

HI All,

after executing my SP from the application I get error for person id as follows:

A database call has used incorrect syntax resulting in a error.
SQL=PatientAutoenroll 324BF032-4849-462C-AA6D-CEBF197A9C
ERROR MESSAGE=Incorrect syntax near 'BF032'. ?? its very weird? I am running sql 2008. The person_id is a varchar(36) or uniqueidentifier.

any thoughts would be great! could this be a compatibility issue? my compatibility is set at 100 for sql 2008 which is correct.

Thanks!

my code below:

ALter PROCEDURE PatientAutoEnroll -- @person_id varchar(36)

AS
DECLARE @Person_ID varchar(36)
DECLARE @row_id varchar(36)
DECLARE @practice_id char(4)
DECLARE @user_id int
--@email_address varchar(36)
DECLARE @enc_id uniqueidentifier

--SET @enc_id = CONVERT(uniqueidentifier, @EncounterId)
--SET @person_id =convert(uniqueidentifier, @person_id)
--set @person_id =''

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

select
@person_id =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 =p.person_id
inner join patient d on d.person_id =p.person_id

where p.person_id = @person_id
and email_address <>''
and pe.enc_id=@enc_id

begin

INSERT INTO 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','ltest5','ABB232','red','color','red',@user_id,current_timestamp,'',current_timestamp)

SET NOCOUNT off;

END
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-22 : 18:58:00

I think you need to wrap your GUID in single quotes:

PatientAutoenroll '324BF032-4849-462C-AA6D-CEBF197A9C'

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-22 : 19:01:15

Thanks lamprey, but the person_id comes from @person_id how do I do this?
Pasi

quote:
Originally posted by Lamprey

I think you need to wrap your GUID in single quotes:
PatientAutoenroll '324BF032-4849-462C-AA6D-CEBF197A9C'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-22 : 19:09:36

How are you calling the stored procedure?

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-22 : 19:13:38

from an application. I open up a patient which has associated person_id, and there is a menu in application that is tied to this SP, once I click on the menue item, it fires up the SP and suppose to grab the current person_id and pass it on to SP., this is where I get the error, unless they way I setup the SP is not correct?

It looks like its is grabbing the correct person_id but gives me syntax error at the same time??
Pasi

quote:
Originally posted by Lamprey

How are you calling the stored procedure?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-22 : 20:32:24

I'm confused by your code. You've commented out the input parameter and then have a DECLARE. They are identical parameters if your collation is case insensitive. Yet you are relying on the parameter to have a value in the select query. I don't see how that issue would generate that error, but it's a bug in the code as far as I can tell.

Here's the issue, bolded the problem areas:
ALter PROCEDURE PatientAutoEnroll -- @person_id varchar(36)

AS
DECLARE @Person_ID varchar(36)

...snip...
select
@person_id =p.person_id ,

...snip...

where p.person_id = @person_id

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

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-05-22 : 23:55:35

Hi,

You should active person_id and input parameter and comment it in Declare statement.
i.e.
ALter PROCEDURE PatientAutoEnroll @person_id varchar(36)

AS
-- DECLARE @Person_ID varchar(36)

For wrap your GUID please try below code:

where p.person_id = ''+ @person_id +'' (it(') is a single quote)

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 11:53:29

Thanks Gourav,

I added the Where clause but , do I need the latter part ? (it(') is a single quote) cause its not working and I get errors?

quote:
Originally posted by GouravSaxena1987

Hi,

You should active person_id and input parameter and comment it in Declare statement.
i.e.
ALter PROCEDURE PatientAutoEnroll @person_id varchar(36)

AS
-- DECLARE @Person_ID varchar(36)

For wrap your GUID please try below code:

where p.person_id = ''+ @person_id +'' (it(') is a single quote)

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 11:56:34

Gourav is incorrect about that WHERE clause. Single quotes are not needed since you are using a parameter defined as varchar.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 11:57:38

HI Tara,

I know what you saying but I was trying out different ways to see if it works. I understand your point. still no luck.
Pasi.

quote:
Originally posted by tkizer

I'm confused by your code. You've commented out the input parameter and then have a DECLARE. They are identical parameters if your collation is case insensitive. Yet you are relying on the parameter to have a value in the select query. I don't see how that issue would generate that error, but it's a bug in the code as far as I can tell.

Here's the issue, bolded the problem areas:
ALter PROCEDURE PatientAutoEnroll -- @person_id varchar(36)

AS
DECLARE @Person_ID varchar(36)

...snip...
select
@person_id =p.person_id ,

...snip...

where p.person_id = @person_id

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 12:01:36

Yah, I tried it and its not working.
Pasi.

quote:
Originally posted by tkizer

Gourav is incorrect about that WHERE clause. Single quotes are not needed since you are using a parameter defined as varchar.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 12:19:54

You need to test out the stored procedure within Management Studio and not within your application code. You need to get it working properly in Management Studio first. Comment out the DECLARE for the person id parameter since you are passing it in as an input parameter.

EXEC PatientAutoEnroll @person_id 'some test value goes here'

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 12:25:17

I did and it works fine when I execute it but not from apps? Not sure why?
Pasi.

quote:
Originally posted by tkizer

You need to test out the stored procedure within Management Studio and not within your application code. You need to get it working properly in Management Studio first. Comment out the DECLARE for the person id parameter since you are passing it in as an input parameter.

EXEC PatientAutoEnroll @person_id 'some test value goes here'

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 12:27:45

Then it's due to your application code and not due to the stored procedure code or the compatibility level. You've got a bug in your application code.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 12:36:33

I think that's what it is. though may be ask you guys and see if you've seen this issue?
Thanks anyway!
Is it possible I can have your email?

quote:
Originally posted by tkizer

Then it's due to your application code and not due to the stored procedure code or the compatibility level. You've got a bug in your application code.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 12:38:15

I am accessible on SQLTeam. If you would like to hire me as a contractor in my off-hours, then yes.

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

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 12:42:28

Ok I'll be in touch if I like to go that route,
Thanks!

quote:
Originally posted by tkizer

I am accessible on SQLTeam. If you would like to hire me as a contractor in my off-hours, then yes.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources