passing GUID to a stored procedure

Question

Post reply

passing GUID to a stored procedure

sandygem2k

Old Hand

Points: 300
More actions
December 19, 2007 at 12:22 am

#184837

Hi All,
I am trying to pass a GUID to a stored proc.
USE [dbsccct]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[usp_dfjk_get_usr_pol_qly_lst]
@app_id = {500CC3FA-4CFC-4FF9-ACA0-AE7C21DF65CE},
@usr_id = N'00008647'
SELECT'Return Value' = @return_value
GO
data type of app_id is uniqueidentifier. I am getting the following error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '575'.
I have tried to replace curly brackets with singles quotes too, but it did nt work.
Please let me know where am I going wrong.
Thanks a lot,
Sandy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Oana Schuster SSCommitted Points: 1642 More actions · Answer 1

Hi,

I have a question : the @return_value parameter is it an OUTPUT parameter ? and what data type does it have?

When passing the value for the @app_id paramter you have to use '' instead of {}.

Maybe if you post the code of the stored procedure would help more.

Regards,

Oana.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The guid should be in single quotes, but other than that, I can't see anything wrong. I also can't see a '575' where the error could be near.

Can you post the stored proc please?

This passes a syntax check, but without the proc, I can't test further

DECLARE @return_value int

EXEC @return_value = [dbo].[usp_dfjk_get_usr_pol_qly_lst]

@app_id = '500CC3FA-4CFC-4FF9-ACA0-AE7C21DF65CE',

@usr_id = N'00008647'

SELECT 'Return Value' = @return_value

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mauve SSChampion Points: 11316 More actions · Answer 3

Note that in SQL Server 2005, the curly braces ( {, }) are allowed as part of a GUID (uniqueidentifier) parameter value. However, the GUID value must be enclosed within single quotes. I.e., a string value.

E.g.:

exec psp_role_add 'EN', '{0A5E5813-7F84-4D31-955C-79FCAF2E6C20}', 'new role'

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Good to know. Thanks for that.

I tested with brackets or with quotes, not with both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

junk5 1969 SSC Enthusiast Points: 134 More actions · Answer 5

I know this is an old post but am adding this for clarity

In Visual Studio 2013

The key to getting this to work is not using that “Query Builder” Wizard

The true query builder won’t let you use these TSQL Statements

Declare

Convert

or

Cast

You have to go into the “Query Builder” but then you click on the button "Edit as Text" at the top left of the toolbar that will let you enter code with a Declare Statement in it and execute code like

Declare @GuidtoPass uniqueidentifier = Convert(uniqueidentifier, @GuidText )

EXECUTE [dbo].[SimpleGuidTest] @GuidtoPass

Can’t say if this work in other version of Visual Studio but I would suspect that it does

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You don't need DECLARE, CONVERT or CAST.

EXEC SimpleGUIDTest '00000000-0000-0000-0000-000000000000'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

junk5 1969 SSC Enthusiast Points: 134 More actions · Answer 7

Yep that works, Good to know didn’t realize that Thanks

Prometheus112 SSC-Addicted Points: 453 More actions · Answer 8

GilaMonster - Monday, February 1, 2016 3:31 PM
You don't need DECLARE, CONVERT or CAST.EXEC SimpleGUIDTest '00000000-0000-0000-0000-000000000000'

Hi, I know this is a bit old, and I have seen this often, but it doesn't help users who are trying to pass a parameter to a stored proc. E.g. if I pull the JOB_ID from MSDB and try to pass it to sp_start_job:

I as admin would execute the following:

CREATE PROC dbo.usp_some_backup
@backupJob uniqueidentifier
WITH EXECUTE AS 'someUserWithPermissions'
BEGIN
EXEC msdb.dbo.sp_start_job @job_id = [@backupJob]
END

I would grant access to the above stored proc, and then hand the customer the following to run:

DECLARE @backupJob uniqueidentifier
SET @backupJob = (select job_id from msdb.dbo.sysjobs WHERE name like '%someJobNameThatMightFluctuate%')

EXEC dbo.usp_some_backup @backupJob

But this errors out (Error converting data type nvarchar to uniqueidentifier).