February 1, 2006 at 7:17 am
Tried searching on this. The site is still agonizingly slow for me. It took me 20 minutes to run the search and look at 2 result items.
I have to write an SP to be used by a Crystal Report. The base query was provided by the vendor of the software package, I just took his query and where he had "insert criteria here" I coded a parameter (@srch). Problem is the table field against which he's comparing the parameter is a GUID field. I defined the parameter as "uniqueidentifier" and the SP built OK, but I have no idea how to run this in QA to test it. I tried @srch='GUID from table' and got back nothing. Is this the right way to specify GUID fields in parameters or should it be specified another way and translated in the SP before using it in the WHERE clause?
Here's my SP:
CREATE PROCEDURE dbo.Instant_Status
(
@srch uniqueidentifier
)
AS
BEGIN
SELECT tSearch.[Description], SCompany.CompanyName as SCompanyName, tPeople.LastName, tPeople.FirstName,
tExperience.OfficialTitle,
CurCompanyName = CASE WHEN tExperience.GUID = tPeople.CurrentExperienceGUID THEN ExpCompany.CompanyName ELSE NULL END,
PriorCompanyName = CASE WHEN tExperience.GUID <> tPeople.CurrentExperienceGUID THEN ExpCompany.CompanyName ELSE NULL END,
tExperience.StartDisplay, tExperience.EndDisplay, tPeopleEducation.Education, tPeopleEducation.GradYear,
tInstantStatus.InstantStatusNotes
FROM tInstantStatus
INNER JOIN tSearch ON tInstantStatus.SearchGUID = tSearch.GUID
INNER JOIN tCompany SCompany ON tSearch.CompanyGUID = SCompany.GUID
INNER JOIN tPeople ON tInstantStatus.PeopleGUID = tPeople.GUID
LEFT JOIN tExperience ON tPeople.GUID = tExperience.PeopleGUID
LEFT JOIN tCompany ExpCompany ON tExperience.CompanyGUID = ExpCompany.GUID
LEFT JOIN tPeopleEducation ON tPeople.GUID = tPeopleEducation.PeopleGUID
WHERE tInstantStatus.SearchGUID = @srch
END
February 1, 2006 at 2:46 pm
Check your data, check datatypes, etc.
You definitely do something wrong.
I tested this on real table:
CREATE PROC dbo.test_Address @RowId uniqueidentifier
AS
select * from address where rowguid = @rowid
go
declare @RowId uniqueidentifier
select @Rowid = rowguid from address where id = 2
select @rowid
exec dbo.test_Address @RowId
drop proc dbo.test_Address
I've got both returns right.
Find out what you are doing wrong.
Regading performance. Do you have indexes on all GUID columns used in the query?
_____________
Code for TallyGenerator
February 2, 2006 at 5:23 am
Thanks Sergiy,
I guess I could use your technique to pull the GUID of a row from a table and execute my SP. I was hoping someone would chime in on how to just manually code a GUID in QA.
Performance isn't really and issue and I'm not touching the schema anyway since this is a vendor package and any changes I make I have to maintain through their quarterly updates.
February 2, 2006 at 6:33 am
If I understand you correctly you are just looking for a way to manually craft a guid in QA? If so, you just write it as a string and set it to a uniqueidentifier variable (or parameter). Here is an example:
USE tempdb
GO
CREATE TABLE foo (bar UNIQUEIDENTIFIER)
GO
INSERT INTO foo (bar) VALUES ('57B3BB87-440E-45A0-B4CA-3DCBC3CDD420')
INSERT INTO foo (bar) VALUES ('2DFFCCB5-6F41-4BBE-9700-1CDA5D4D1DBC')
DECLARE @theguid UNIQUEIDENTIFIER
SET @theguid = '57B3BB87-440E-45A0-B4CA-3DCBC3CDD420'
SELECT * FROM foo WHERE bar = @theguid
DROP TABLE foo
February 2, 2006 at 8:31 am
I was entering the string as you did above with my SP:
Exec dbo.Instant_Status 'BF33FA65-D558-43F5-BE47-0F0D20AB0028'
but I was getting no return. I went back and found I'd copied the GUID from the wrong field. When I used the correct field the SP worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply