Issue with stored procedure...

Question

Issue with stored procedure...

pythag0re

SSC Enthusiast

Points: 108
More actions
September 23, 2008 at 6:22 pm

#194049

Hey guys,
So i have a stored procedure that has a few statements, etc.
When I try to save my stored procedure I get:
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
those are the lines that i'm having a problem with
CREATE PROCEDURE [dbo].[usp_procedure1]
-- Add the parameters for the stored procedure here
@IDbigint = 0,
@value1 varchar(16) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @eValue varbinary(80);
EXEC dbo.eProcedure @value1,@eValue OUTPUT;
UPDATE [table1] SET
column1=@eValue
WHERE condition=Blah;
Anybody has an idea on this?
Thanks!

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

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

Johannes Fourie Default port Points: 1470 More actions · Answer 1

Well either the second parameter of the dbo.eProcedure stored procedure is of type VARCHAR, or column1 of table1 is of type VARCHAR.

Perhaps make sure @eValue is the same type as the output parameter of dbo.eProcedure, and cast it to the same type as the column that you're trying to set.

Quatrei.X SSCrazy Points: 2697 More actions · Answer 2

Hi there,

I think your problem is here

EXEC dbo.eProcedure @value1,@eValue OUTPUT

Can you post the code that you used for this sproc. Its either the parameters for @Value in this sproc is not VARBINARY or the value given to it by the sproc is VARCHAR... It depends on the sproc you called

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

pythag0re SSC Enthusiast Points: 108 More actions · Answer 3

Column1 of table1 is indeed of varbinary type.

dbo.eProcedure as the following definition:

CREATE PROCEDURE [dbo].[eProcedure]

-- Add the parameters for the stored procedure here

@CT varchar(MAX) = NULL,

@et varbinary(MAX) OUTPUT

AS

BEGIN

....

as @value1 is of varchar type... and @eValue is of type varbinary, i would expect no problems?

Let me recall:

EXEC dbo.eProcedure @value1,@eValue OUTPUT

pythag0re SSC Enthusiast Points: 108 More actions · Answer 4

Bump... Anyone?

This is very weird haha i'm sure it's something really stupid, i just can't see it :S

pythag0re SSC Enthusiast Points: 108 More actions · Answer 5

Hey,

Thanks for everyone's input, problem fixed.. my declaration for a variable was bogus, i thought i had varbinary, but it was varchar 😛

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 6

Heh... thanks for posting your solution. Sometimes the problem just boils down to "must look eye." 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff Moden (9/27/2008)
Heh... thanks for posting your solution. Sometimes the problem just boils down to "must look eye." 🙂

Any more references like these and you're going to have to start kicking royalty fees over to Pat Morita:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 8

Matt Miller (9/27/2008)
Jeff Moden (9/27/2008)
Heh... thanks for posting your solution. Sometimes the problem just boils down to "must look eye." 🙂
Any more references like these and you're going to have to start kicking royalty fees over to Pat Morita:)

Heh... other way around... I taught Pat everything he knows. 😀 I used to be able to stand on a post, too... but then we got a divorce. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)