[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-05-07
7
Medium Priority
?
627 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 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
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 …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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