?
Solved

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

Posted on 2009-05-07
7
Medium Priority
?
618 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

777 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