Solved

Conversion failed when converting the varchar value 'bit' to data type bit.

Posted on 2009-05-07
7
609 Views
Last Modified: 2012-05-06
I have written the following Scaler-Valued function in SQL Sever 2005 that returns the data type of the specified column and the table. The function script is pasted in the Code sction. When I try to call the function using the follwing script:

DECLARE @DataType VARCHAR(25)
SET @DataType=cast(dbo.fnGetDataTypes('8520', 'ReleasedParts') as bit)
SELECT @DataType

I get the following error:

Conversion failed when converting the varchar value 'bit' to data type bit.

Can anyone please help as this is time critical,

Thank you


CREATE FUNCTION [dbo].[fnGetDataTypes] (@ColumnName VARCHAR(50),@TableName VARCHAR(50))
RETURNS BIT
WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @DataType VARCHAR(25)
	
SET @DataType=(SELECT
    st.name as UserType
FROM dbo.syscolumns c
INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
WHERE c.id = OBJECT_ID(@TableName) and c.name=@ColumnName
)
 
			
		 RETURN @DataType
END

Open in new window

0
Comment
Question by:skaleem1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24331357
CREATE FUNCTION [dbo].[fnGetDataTypes] (@ColumnName VARCHAR(50),@TableName VARCHAR(50))
RETURNS VARCHAR(25) -------------- it should not be bit
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24331369
your function returns a varchar, containing the name of the data type.
you try to "cast" that to bit cannot work...
DECLARE @DataType VARCHAR(25)
SET @DataType= dbo.fnGetDataTypes('8520', 'ReleasedParts')  
SELECT @DataType

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24331376
that also ...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:skaleem1
ID: 24331812
I am sorry, the script that I am trying to use to call the function is:

DECLARE @DataType VARCHAR(25)
SET @DataType=dbo.fnGetDataTypes('8520', 'ReleasedParts')
SELECT @DataType

But I still get the same error:

Conversion failed when converting the varchar value 'bit' to data type bit.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24331843
dID YOU SEE MY POST, YOUR FUNCTION IS TRYING TO RETURN A BIT VALUE, but the actual return type should be 'varchar'
0
 
LVL 1

Author Comment

by:skaleem1
ID: 24331953
Oh yes, that is a silly oversight on my part, thanks a lot Aneeshattingal.
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 31579254
thanks again, I have given you the points
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

717 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