sp Error converting varchar to bigint

Question

sp Error converting varchar to bigint

jrob

Grasshopper

Points: 13
More actions
November 7, 2005 at 11:14 am

#89826

I am trying to write a sp that retrieves the identity from one table and retrieves data based on this ID from other tables. I am getting "Error converting data type varchar to bigint".
Casting nor convert have solved this issue. Any Suggestions? Thanks
CREATE PROCEDURE dbo.viewReport
@myStaff nvarchar (50),
@myCoalition nvarchar (50)
AS
DECLARE @mainID bigint
DECLARE @secondaryID bigint
SET @mainID =CONVERT(bigint,'SELECT CPIndex FROM CoalPol WHERE Staff=@myStaff AND [Coalition/Policy]=@myCoalition' )
EXEC(@mainID)
SELECT * FROM CoalPol WHERE CPIndex = @mainID

Viewing 6 posts - 1 through 5 (of 5 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

I'm not really clear on what you're trying to do, but your convert isn't converting cpindex, it's trying to convert the string you've given it as the second argument.

Try:

CREATE PROCEDURE dbo.viewReport

@myStaff nvarchar (50),

@myCoalition nvarchar (50)

AS

DECLARE @mainID bigint

DECLARE @secondaryID bigint

SELECT @mainid = convert(bigint,CPIndex) FROM CoalPol WHERE Staff=@myStaff AND [Coalition/Policy]=@myCoalition

SELECT * FROM CoalPol WHERE CPIndex = @mainID

And then again, I might be wrong ...
David Webb

jrob Grasshopper Points: 13 More actions · Answer 2

Thanks for the input. Unfortunately no success.

CPIndex is within the CoalPol table as an identity column of type 'bigint'.

Trying to understand the error, I was assuming that it must be returning the select as a varchar, while mainid is looking for a bigint.

John Hopkins SSC Enthusiast Points: 162 More actions · Answer 3

Given that CPIndex is already bigint, it doesn't need to be converted. So borrowing from David's sample:

CREATE PROCEDURE dbo.viewReport

@myStaff nvarchar (50),

@myCoalition nvarchar (50)

AS

DECLARE @mainID bigint

DECLARE @secondaryID bigint

SELECT @mainid = CPIndex FROM CoalPol WHERE Staff=@myStaff AND [Coalition/Policy]=@myCoalition

SELECT * FROM CoalPol WHERE CPIndex = @mainID

I think that should get you closer.

HTH,

John

Regards,

John Hopkins

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

If cpindex is a biging, you shouldn't have to convert it.

CREATE PROCEDURE dbo.viewReport

@myStaff nvarchar (50),

@myCoalition nvarchar (50)

AS

DECLARE @mainID bigint

DECLARE @secondaryID bigint

SELECT @mainid = CPIndex FROM CoalPol WHERE Staff=@myStaff AND [Coalition/Policy]=@myCoalition

SELECT * FROM CoalPol WHERE CPIndex = @mainID

The above code should set @mainid to the cpindex value from a row which matches on @mystaff and @mycoalition and then turn around and select all the rows which match that @mainid

Are you getting an error message or an empty return set?

What error are you getting

And then again, I might be wrong ...
David Webb

jrob Grasshopper Points: 13 More actions · Answer 5

Thank you for your tech(ex)pertise. Your snippet solved the issue. Again, thank you.