Solved

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

Posted on 2009-05-07
7
606 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Agent Timeout 5 74
How to place a condition in a filter criteria in t-sql (#2)? 10 67
Strange msg in the SSMS pane 13 64
calculate running total 8 51
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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