Hi everybody,
VARCHAR has a higher precedence than CHAR. If you have a query
SELECT COALESCE(c1,c2) FROM T1
where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the return
type to be varchar(10) similiar to UNION. However it turns out to
be CHAR(10). Does anyone know why that is so?
Thanks,
Steffen
i have 2 columns which are decimal
one is alway empty and the other always has a value -- i just need which ever one has a value -- i keep getting Error converting data type varchar to numeric
(COALESCE(TicketLoadQty, '') + ' ' + COALESCE(TicketTimeQty, '')) as theQuantity
how do i get around this
Hello:
I'm creating a 'dynamic where clause' with 15 parameters. I was using coalesce with the example below and it's working fine until it encounters a null value. I was trying to use nullif with coalesce to no avail. Can someone show me based on my example below how I can incorporate nullif with coalesce. Thanks in advance for you help.
Code Block
WHERE cs.DirID = Coalesce(@DirNum, cs.DirID)
Hi,i need an opinion on this,...i am using UNION ALL quite a bit in my queries..most of the time these are like 1 line select query,returning "different" type of stuff ..like say query 1 is returning one EmpName and query 2 is DOB and so on(keeping it simple for example)...i am combining it b/c i find it cumbersome to call 10 dfferent query to populate 1 structure(table etc) ,rather i wud call one and manipulate the result to fill up that one structure..Is that okay to combine different kind of return types in one (here first row of multi-select query result is empName,second row is DOB and so on...)?????thanks
View 5 Replies View RelatedI am calling a stored procedure and have an output parameter that I pass back to my asp page. The datatype of the value is nvarchar. When I run the page I get an error "Syntax error converting the nvarchar value 'OK' to a column of data type int". Can a return parameter be anything else other than an integer? Below is my code. Thanks for the help.
Parem = cmd.parameters.add("@Retval",SqlDbType.nvarchar,50)
Parem.Direction = ParameterDirection.Output
Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.
I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)
The problem is that the function should return Integer and if the value is zero I need to return empty string.
It does't say any thing about returning the string but when it trying to use the format on a string it give me a worning and print #ERORR in the cell.
is there a solution for this?
Do you know the syntax to format the number in the function before the return?
(Sorry for the dumb question but I know C# not VB.net and there is no intelisance in that editor).
Thanks a lot!
How can I return a datetime type variable from a stored procedure in SQL Server to C# code?
View 4 Replies View RelatedHi,
I am trying to retrive some XML from my SQL server 2005 database. The XML is in a table called "myxml" and it is being stored a native XML. The Field type is "XML". The VB code returns nothing when I select SELECT xml. How ever if i SELECT NAME it displays the correct name from the name field. how do I return the XML to be viewed as XML throught the response.write?
Thanks!
Here is my Table definition:
Column Name
Data Type
Allow Nulls
Id
Int
xml
xml
Yes
NAME
Nvarchar(50)
yes
My XML that is stored in the XML field:
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE>
</CHAPTER>
</ROOT>
Here is my VB code:
Dim myconnection As SqlConnection
Dim mycommand As SqlCommand
myconnection = New SqlConnection()
myconnection.ConnectionString = _
ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString
Dim strSQL As String = "SELECT [xml] FROM myxml WHERE id = 76"
' create SQL command instance
mycommand = New SqlCommand(strSQL, myconnection)
Try
' open database connection
myconnection.Open()
'execute T-SQL command
Dim dr = mycommand.ExecuteReader()
While dr.read()
Response.Write("<p>" & dr(0).ToString() & "</p>")
End While
Response.Write("GOT IT")
Catch ex As Exception
Response.Write(ex.Message)
Response.Write(strSQL)
Response.End()
End Try
' Close connection
myconnection.Close()
Hello All,I have a scenario in which my stored procedure has to return fewvariables with their value and also the collection. Now in SQL their isno such as array, so the best is to return the table in place of.I am execting the stored procedure by having sql command in place.and created the various parameters(variables) those i need the valuesof and secondly wondering how should i be creating the parameter as atable returntype.Any help on this would be a million worth useful.I can excerpt the code if required.RegardsSandesh Kadam
View 1 Replies View RelatedHi all,
I am trying to figure out a way to analyze a stored procedure to deduce its output type.
A stored procedure can return values throught one of the following:
1) Using the Return keyword
2) Returning a recordset
3) Throught its Output parameters
4) A combination of 1, 2 or 3
Using the Information_Schema, you can access the SQL code of the stored procedure from, say, VB.Net. From there, I want to know what output type the stored procedure has.
A) for 1), I guess I can parse the code for the 'Return' keyword, but that wouldn't be efficient, for the 'Return' word could be inserted into string values...
B) for 2), it would be difficult to analyze the stored procedure's code to know FOR SURE whether a recordset is returned. You just can't parse for SELECT keywords in complex code...
C) 3) should be easy enough, getting the return parameters types from the Information_Schema.
So, is there an easy way that I missed for achieving this, some object in the .Net Framework that I could use to analyze SQL queries perhaps, or am I doomed to do it the hard way?
Thanks,
-Etienne
hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error.
there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
can anybody help me?
thanks in advance
Chetan S. Raut
Hi,
I'm using quite odd combination of technology for my project, I'm using PHP and MSSQL 2000, at one certain page, I want to insert to a table where one of the column is TEXT data type, and I want to get the value from the TEXTAREA at the page, of course, with carriage return captured, I manage to get it done in MySQL, where it automatically store the carriage return keyed in by user at the TEXTAREA, while for MSSQL I no luck in finding solution for this, is there any settings I can set or I need to convert the carriage return keystroke to HTML tag at my PHP?
Thanks
Hi all,
I have a stored like this
CREATE PROCEDURE fts_insert_service_tasks( @status_no int output, @status_text nvarchar(255) output, @fts_employee char(100) , @fts_SCCode bigint, @fts_TaskDescription ntext) AS
declare @str_err nvarchar(255)
declare @err_no int
set @err_no=0
if ( isnumeric(@fts_SCCode) = 0 )
begin
set @str_err ='The fts Sccode is not a number'
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if ( @fts_SCCode = '' )
begin
set @str_err ='The fts Sccode can not be null '
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if ( len(@fts_employee) > 100)
begin
set @str_err ='Maximum Employee length allowed is 100 characters'
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if ( @fts_employee = '' )
begin
set @str_err ='The employee fiedl can not be null'
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if (@err_no=0)
begin
INSERT INTO fts_ServiceTasks (fts_employee , fts_Sccode, fts_taskdescription)
VALUES(@fts_employee, @fts_SCCode, @fts_taskdescription)
set @status_no=0
set @status_text = 'Add Service Task Ok'
end
else
begin
set @status_no=@err_no
set @status_text = @str_err
end
GO
and I called it from the ASP
<%function Add_Service_Task(fts_employee,fts_sccode, fts_TaskDescription)
cm.ActiveConnection = m_conn
cm.CommandType = 4
cm.CommandText = "fts_insert_service_tasks"
cm.Parameters.refresh
cm.Parameters(3).Value = fts_employee
cm.Parameters(4).Value = fts_sccode
cm.Parameters(5).Value = fts_TaskDescription
on error resume next
cm.Execute
if cm.Parameters(1)=0 then
exec_command=cm.Parameters(2).Value
else
call obj_utils.ErrMsg(cm.Parameters(2).Value,3000)
Response.End
end if
if err.number <> 0 then
call obj_utils.ErrMsg("System error at " & err.number & err.Description & ", please contact the administrator", 5000)
Response.End
end if
Add_Service_Task=exec_command
end function%>
I test with SQL 2k, Win2k3 OK
But with Win2k i got:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E30)
Type name is invalid.
/fmits/classes/cls_servicecall.asp, line 256
Please help me!
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is:
select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
Thanks for the help
how can i insert a carriage return when i update the field?
say i want to put the following inside a field:
firstline
secondline
how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'
the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines
i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'
but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline
thanks
I have this data:
clientcreditCardamount
1A10
1A20
1B40
1C5
1C10
1C20
I need to write a query (Oracle) that will return the clientID and the creditCard type with the highest spending :
In this case:
clientcreditCard
1B
I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).
For instance this works, and will return all applicable results.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](1000) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
And this does not. It returns zero results what so ever.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](MAX) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?
ALTER procedure [dbo].[findConsultantMail]
(
@PerID numeric(18,0),
@perMail nvarchar(100) OUTPUT
)
as
SELECT @perMail=PerMail FROM Personel
WHERE (PerID =@PerID)
return @perMail
I want to get Email address from sql database.
But whenever I executed stored procedure I get an error message
"Conversion failed when converting the nvarchar value 'xxxxxx@xxxxxx' to data type int"
If I want some numeric ID it is worked.
I also change my SP like this but results same.
ALTER procedure [dbo].[findConsultantMail]
(
@PerID nvarchar(18),
@perMail nvarchar(100) OUTPUT
)
as
SELECT @perMail=PerMail FROM Personel
WHERE (PerID =cast(@PerID as numeric(18,0)))
return cast(@perMail as nvarchar(100))
How can I get a string value form stored procedure.
I have a pulldown menu which has like 4 options
producta productb productc and all
I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc
Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC
and I am trying to use coalesce but unable to get my result.
I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.
If I do the following with no max funciton, i see all the values but i just want max from each branch.
DECLARE @buildList varchar(100)select @buildlist=COALESCE(@buildList + ', ', '') + convert(varchar(10),build) from results
where branch in ('ProductA','Product B','ProductC')
select @buildList
Please let me know how to do this.
I have a stored procedure which receives a dynamically built WHERE clause. This is then appended to the sql query within like....'select * from table' +@where_clause.
I know that I am possibly leaving myself open to sql injection so I wanted to find an alternate way of handling this. I stumbled across an article which speaks of using COALESCE as a way to sidestep using dynamic WHERE clauses....http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
In my application the user can enter 1-to-many textboxes as search criteria. What I have been doing is a series of IF statements to determine if each textbox is populated or not and build the WHERE clause accordingly.If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & tx_lastname.Text & "'"
Else
sqlwhere = " where lname like '" & tx_lastname.Text & "'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & tx_firstname.Text & "'"
Else
sqlwhere = " where fname like '" & tx_firstname.Text & "'"
End If
End IfAnd so forth. But the above article seems to insinuate that I provide a static WHERE clause like this...'select * from table WHERE LNAME = COALESCE(@lname, lname) and COALESCE(@fname, fname). And this would handle it.Have any of you ever used this before? This is my first question. My other question is could this particular method be made compatible to fit with the LIKE operator?My user needs to be able to search based on close matches. For instance, if they enter 'JOHN' in the last name field, the results should contain 'JOHN', 'JOHNSON', 'JOHNS', etc.Any help would be appreciated.
A few people have mentioned that i should use coalesce in the following statement. the problem is i don't know where i should be using itCan someone show me where i should place it? 1 SELECT (SELECT Location
2 FROM Location_Table
3 WHERE (Property_Table.LocationID = LocationID)) AS Location,
4 (SELECT TypeOfProperty
5 FROM Type_Table
6 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty, PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
7 FROM Property_Table
8 WHERE (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) OR
9 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) OR
10 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) OR
11 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) OR
12 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price <= @MaxPrice) OR
13 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
14 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price <= @MaxPrice) OR
15 (NULLIF (@MinPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
16 (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
17 (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
18 (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
19 (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice)
I have inherited a db with slowness claims. Last week at a MS seminar where independent SQL Consultant gave presentation on performance gotchas. One of his top 5, do not use coalesce on joins and where clause. Of course it is all over this db. Looking at below was this a bad approach?
WHEREcoalesce(PM.ALTKEYDOC,'x') = coalesce(@AK,PM.ALTKEYDOC,'x')
AND coalesce(PM.FIRSTNAME,'x') LIKE coalesce('%' + @FN + '%',PM.FIRSTNAME,'x')
AND coalesce(PM.LASTNAME,'x') LIKE coalesce('%' + @LN + '%',PM.LASTNAME,'x')
AND coalesce(PM.SEX,'x') = coalesce(@SX,PM.SEX,'x')
AND coalesce(PM.BIRTHDATE,'1/1/1900') = coalesce(@BD,PM.BIRTHDATE,'1/1/1900')
AND coalesce(PM.DIVISION,'x') = coalesce(@DI,PM.DIVISION,'x')
AND PM.STATE = @STATE
ORDER BY LASTNAME
Hi.
I have a piece of a store procedure I don't quite understand, as follows:
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@DealReference,'%'))
What exactly is the COALESCE function doing here with the parameter?
How would i use a coalesece on this function to get null. if i use coalesce(xxxxx,0). If there is nothing in there it returns a blank space but i need to put null in there
cast([DPVisionPlan] as nvarchar(255)) [DPVisionPlan],
I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.I came across a piece of code posted in the forum about the different uses of coalesce.
use adventureworks
DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')
[code]...
what the hell does that mean!? how do i use it and where?
View 20 Replies View RelatedHere is my statement
COALESCE (Reg_HomeAdd1, N'') + N', ' + COALESCE (Reg_HomeAdd2, N'') + N', ' + COALESCE (Reg_HomeAdd3, N'') + N', ' + COALESCE (Reg_HomeAdd4, N'')
if one of the fields is null how do I stop two commas showing
ie
The Nook,West Street,,Townsville
I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.
Below is my sample:
SELECT *
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY q.StudentID
Having
(SUM(Coalesce(q.AbsenceValue),0) = 0.00)
COALESCE(SUM(q.AbsenceValue) = 0.00,0)
I have tried using the coalesce statement a couple of ways with no resolution, pls help!!
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
View 1 Replies View RelatedHi everybody,
I have a stored procedure that creates some temporary tables and in the end selects various values from those tables and returns them as a datatable. when returning the values, some fields are derived from other fields like percentage sold. I have it inside a Coalesce function like Coalesce((ItemsSold/TotalItems)*100, 0) this function returns 0 for every row, except for one row for which it returns 100. Does that mean for every other row, the value of (ItemSold/TotalItems)*100 is NULL ? if so, how can I fix it ? any help is greatly appriciated.
devmetz
Hi All
I have a problem in making out why Coalese is considered to be better than ISNULL .According to my investigation Coalesce Returns the data type of expression with the highest data type precedence.If for eg I have declared that I want to return the value only upto 3 char then why will I use Coalesce and override my requirement.Anyone with clear concept about this plz explain.
DECLARE @v1 VARCHAR(3)DECLARE @i1 INT
SELECT ISNULL(@i1, 15.00) /2,
COALESCE(@i1 , 15.00) /2,
ISNULL(@v1, 'Teaser #2'),
COALESCE(@v1, 'Teaser #2')
The result will be
7
7.500000
Tea
Teaser #2
thanks in advance
Hi guys,
Do you have any idea why COALESCE function gives timeout in SQL2000 and works in SQL7.0
for ex.
in SQL7.0 this statement works fine in one of my SP
ROUND(COALESCE(ABS((SELECT SUM(Amount)))))
but in SQL2000 my SP is not working and my program gives timeout. But, if I change to ISNULL it works fine. Any clues on this issue?
This problem was there in SQL65 with ISNULL and then we have changed to COALESCE. Now, again repeated in 2000.