Solved

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

Posted on 2009-05-07
7
588 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
  • 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 142

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24331376
that also ...
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 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 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

24 Experts available now in Live!

Get 1:1 Help Now