Solved

Error converting data type varchar to uniqueidentifier

Posted on 2013-11-18
11
1,909 Views
Last Modified: 2013-11-24
I am trying to run a stored procedure. This is the line I am having problems with....

, @CustId uniqueidentifier

Right now I have it as.....

,'6B6708EE-E2BC-5C6F-AB9C-97D4D2F9CB11'

I do not want it to be fixed. I would like it to run on all customers, not just a specified Unique ID.

I tried the following......

,'%%'
,'%-%
,' LIKE '%%'
, 'Null'

Now I am stuck.

Please advise.....
0
Comment
Question by:Newbi22
  • 6
  • 5
11 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39658248
Can't you just omit it from the WHERE clause? I.e. suppose you have a query like this:
SELECT custName, custAddress FROM customer
WHERE custID = @custID

Open in new window

If you want to select all customers use this:
SELECT custName, custAddress FROM customer

Open in new window

0
 

Author Comment

by:Newbi22
ID: 39661135
It is actually as part of a store procedure.  

Below is a section of the stored procedure.

@XMLDataSelection text
, @ReportType smallint
, @RankBy smallint
, @RankValue smallint
, @FromDate1 datetime
, @ToDate1 datetime
, @FromDate2 datetime
, @ToDate2 datetime
, @CoType varchar(5)
, @LOBCat smallint
, @TranReporting smallint
, @CustId uniqueidentifier


Below is me passing the parts into the stored procedure to get it to run....

sps_Stored_Procedure (

'<Root><RowId Code="(All)" Type="9" /></Root>'
,<<ReportType [Numeric]>>
, <<RankBy [Numeric]>>
, <<RankValue [Numeric]>>
,'2010-01-01 12:00:00'
,'2013-01-01 12:00:00'
,'2010-01-01 12:00:00'
,'2013-01-01 12:00:00'
,'N'
, <<LOBCat [Numeric]>>
, <<TranReporting [Numeric]>>
,'6B6708EE-E2BC-5C6F-AB9C-97D4D2F9CB11'
,'A'
, <<SortSequence [Numeric]>>
,'0'
,''
, '!*R'
)


As you can see I have the uniquie ID hard coded. That will not work because I want the report to run for all customers. Some of the parameter are hard coded also so I do not have to type the dates over and over. The <<Prompt>> I can fill in different parameters.

But when I take the , @CustId uniqueidentifier and turn it into
 <<CustId uniqueidentifier [Text]>> I get the error of ...Error converting data type nvarchar to uniqueidentifier.:

The program will not close and allow me to run the report because of the error. It understands the following formats. (Numeric, Text, Date, Datetime, Time, Boolean ) Numeric gets me the same error.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39661165
In this case you should have a predefined uniqueidentifier value that will be understood by both the stored procedure and your program as a "value for all", i.e. it could be
'00000000-0000-0000-0000-000000000000'

Then in your stored procedure in place of your " AND custID = @custID" statement write this:
... AND (custID = @custID OR @custID = '00000000-0000-0000-0000-000000000000')

Open in new window

This way whenever the calling program passes '00000000-0000-0000-0000-000000000000' all customers will be returned
0
 

Author Comment

by:Newbi22
ID: 39661200
ok. I try the following and get Incorrect Syntax near 'AND'

,AND (custID = @custID OR @custID = '00000000-0000-0000-0000-000000000000')

I try this and get looking for a [Text] or [Nummeric]

,<<AND (CustId = @CustId OR @CustId = '00000000-0000-0000-0000-000000000000')>>

I try this and get : Error converting data type nvarchar to uniqueidentifier

<<AND (CustId = @CustId OR @CustId = '00000000-0000-0000-0000-000000000000'[Text])>>

Ugh!
0
 

Author Comment

by:Newbi22
ID: 39661205
here is a snip from the book

SQL Stored Procedure
Enter the name of the stored procedure directly here. If a parameter needs to be passed in, include it in your local databases syntax. Omit any “CALL” verbs, just enter in the procedure name. Example: MYPROCEDURE(‘Value1’,10)
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 24

Assisted Solution

by:chaau
chaau earned 500 total points
ID: 39661212
Hold on. You did not understand me. The "AND (custID = @custID OR @custID = '00000000-0000-0000-0000-000000000000')" needs to be applied to the code inside your stored procedure. Your program just needs to call it with a value of '00000000-0000-0000-0000-000000000000' as a parameter
0
 

Author Comment

by:Newbi22
ID: 39661295
You are correct, I do not understand. I am an accountant,  not IT. I get stuck doing this because no one else can help. I can not edit the Stored Procedure if this is what you are saying.

I have to add a line into the program in the order that the procedures are called. For example, this line , @ReportType smallint is translated to ,<<ReportType [Numeric]>>.... The <<>> renders a prompt for me. So when I run it, it ask me to enter a number.

This line , @CoType varchar(5) is translated to ,'N'....... N will always be the answer so I hard code it.

I'm not sure if I am explaining it correctly or not understanding what you are asking me to do.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39661332
I think in this case you will not be able to achieve what you want. Both of us don't know what the stored procedure is doing. However, if you can get someone to change the stored procedure as I described, you will be able to select all customers.
0
 

Author Comment

by:Newbi22
ID: 39661350
The stored run a report in out ERP system. There is an "window/panel" that pops up with all the selectors in drop down filters, radio buttons, etc like a normal program. The window prompts the same store procedure, so I can not change it.

I am using the program of Informer to run the same procedure so I can create calc columns, hide some of the results, etc... That way I do not have to re-do it every week in excel.

I can also use Informer to schedule the report. Informer also has built in BI that I can use.

I guess I am back to them for help...

What about wild cards or convert/cast to change the UID to Text.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39661361
Even if the wildcards are supported (e.g. in case of a character string) it depends on the query implementation inside the stored procedure. E.g. for strings you could write:
WHERE custName LIKE @custName

Open in new window

In this case you could specify @custName as 'SM%' and it will find all Smiths, Smirnoffs, ets.
However, if the query has a condition like this:
WHERE custName = @custName

Open in new window

then you will not be able to use wildcards.
0
 

Author Closing Comment

by:Newbi22
ID: 39672703
OK, not what I really wanted to hear, but I got a good education from the situation. Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now