Retrieving data using uniqueidentifier

I have a table where one field is productid type uniqueidentifier

I made a stored procedure to select the record based on the productid, which is a parameter of the procedure:

create procedure getdata @PID uniqueidentifier

select * from tb_products where productid  = @PID

when I try to execute the procedure, it does not work, telling a message that there there is a sybtax error near the first part of uniqye identifier}

what is the format of PID data in the executable command

execute  getdata {0ABCD-WASD-.....-.....} or "0ABCD-........." or what
assaadrezkAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
So the entire proc will looks like this

Alter procedure getdata
  @PID uniqueidentifier,
  @fieldname varchar(20),
  @out_val int output
AS
declare @mysql nvarchar(500)

set @mysql = 'select @out_val =  NAME from tb_products where
productid = '''+ cast( @PID as varchar(56)) +''''


execute sp_executesql @mysql,N'@out_val int output' , @out_val output

select "Result" = @out_val
go
declare @outp int
execute getdata 'D106D827-29B6-45C8-B5D9-B49F01984D4D','var1',@outp output
select @outp
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
The productID must be enclosed in single Quotes

execute  getdata '0ABCD-WASD-.....-.....'
0
 
assaadrezkAuthor Commented:
I tried this too but it does not work too
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
here is an example




- create table tb_products ( name varchar(32), productid uniqueidentifier )

insert into tb_products(name,productid) SELECT 'prod1',newid()
insert into tb_products(name,productid) SELECT 'prod2',newid()
insert into tb_products(name,productid) SELECT 'prod3',newid()

go
create procedure getdata @PID uniqueidentifier
AS
select * from tb_products
where productid  = @PID
GO


select * from tb_products  

-- now copy one of the ProductIds and replace the ID given below

exec getdata 'DF12EA8E-957B-436B-876D-A35DB898B713'


Make sure that ur ProductID is not null
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SET NOCOUNT ON
declare @pid uniqueidentifier
set @pid = cast ( '0ABCD-WASD-.....-.....' as uniqueidentifier)
execute  getdata @pid
0
 
assaadrezkAuthor Commented:
The table contains 3 int fields (Var1, Var2, Var3)  + ProductID and name
I am entering the name of any of the 3 fields to get the result;
Here is the code;

Alter procedure getdata
  @PID uniqueidentifier,
  @fieldname varchar(20),
  @out_val int output
AS
declare @mysql nvarchar(500)

set @mysql = 'select @out_val = ' +@fieldname+ ' from tb_products where
productid = '+@PID

execute sp_executesql @mysql,N'@out_val int output' , @out_val output

select "Result" = @out_val
go
declare @outp int
execute getdata 'D106D827-29B6-45C8-B5D9-B49F01984D4D','var1',@outp output
0
 
assaadrezkAuthor Commented:
Sorry I forget to bring u the error msg
Server: Msg 403, Level 16, State 1, Procedure getdata, Line 4
Invalid operator for data type. Operator equals add, type equals uniqueidentifier.
Server: Msg 8144, Level 16, State 2, Procedure getdata, Line 0
Procedure or function getdata has too many arguments specified.

These are the rror messages, I get...
Thank you
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
assaadrezk,
> set @mysql = 'select @out_val = ' +@fieldname+ ' from tb_products where
> productid = '+@PID

Replace the above line with


set @mysql = 'select @out_val = ' +@fieldname+ ' from tb_products where
productid = '+CAST(@PID,varchar(60))


> select "Result" = @out_val
this line is also not needed





0
 
assaadrezkAuthor Commented:
The select "Result" = @out_val to make sure of the result

when I use this I get the same msg I had first when I submit the question:

Server: Msg 403, Level 16, State 1, Procedure getdata, Line 5
Invalid operator for data type. Operator equals add, type equals uniqueidentifier.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'B6'.

where B6 is a part of the entered key:D106D827-29B6-45C8-B5D9-B49F01984D4D
 
0
 
assaadrezkAuthor Commented:
Comment to AngelIII

I have the same problem with your solutions,,,

Thank you
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
replace the select mysql statement inside the procedure with this


set @mysql = 'select @out_val =  NAME from tb_products where
productid = '''+ cast( @PID as varchar(56)) +''''
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or like this:

Alter procedure getdata
  @PID uniqueidentifier,
  @fieldname varchar(20),
  @out_val int output
AS
declare @mysql nvarchar(500)

set @mysql = 'select @out_val =  NAME from tb_products where
productid = @pid '

execute sp_executesql @mysql,N'@out_val int output, @pid uniqueidentifier' , @out_val output, @pid
--this is for debugging only
--select "Result" = @out_val

go
declare @outp int
execute getdata 'D106D827-29B6-45C8-B5D9-B49F01984D4D','var1',@outp output
select @outp
0
 
assaadrezkAuthor Commented:
Thanks so much,,,,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.