T-SQL (SS2K8) :: If Not Exists Returning False When Should Be True

Jul 3, 2014

Actually title should be returns true when should false.

I want to check a table to see if a record already exists, if it doesn't then insert it, else do nothing:

IF NOT EXISTS
(SELECT 1 FROM Table1 WHERE col1 = 'Test')
BEGIN
INSERT INTO Table1 (col1) VALUES ('Test')
END

The value 'Test' is already in the database yet, the code is saying it's not and trying to insert it, resulting in duplicate key errors.

View 9 Replies


ADVERTISEMENT

Analysis :: Cube Dimension With Bool Filter Shows (blank) And True Instead Of False And True?

Jul 31, 2015

I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False). 

View 2 Replies View Related

In A Where How Can I Use True Or False

Oct 20, 2007

I have a stored procedure that has a boolean (bit) field passed to it (@emailcontract). If a user checks the check box on the webform I would like my where to return only the records where the email_contract column is true. If they don't check the check box I would like it to return records where email_contracts is true or false.
What would my where cluse look lile for this?

View 4 Replies View Related

True False

May 13, 2007

All



Can I ask what data type i use for a true false response (Boolean) in my table?



Thanks



Gibbo

View 1 Replies View Related

Return True False

Mar 20, 2008

Hi,
I need to check the existence of a row in a table.
So i am using an if condition
like
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[CheckNOAStages]
@NOAID int,
@StageCode nchar(20)
AS
BEGIN

SET NOCOUNT ON;
Declare @Count int
Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode
if (@Count>0)
Begin
return 1
end
else
begin
return 0
end


END

The stored proc is executing but on the Data Access Layer
I have this
Boolean exists = Convert.ToBoolean (Execute.ExecuteReader(spCollection, dbSQL));

Some how I am always getting false . How can I fix this?
Thanks

View 7 Replies View Related

How To Change From True Or False To 1 Or 0

Oct 29, 2007



Hello

I am exporting an SQL Server table to a comma delimited text file. The values of Columns defined as Bit are exported as "True" or "False", but I would like that in the file appear 1 or 0 instead (with no surrounding double quotes). How can I acomplish that?

I tried using a Transformation and convert to single byte unsigned integer, but True values are exported as "255" and False values as "0". Why?

Thanks a lot.

View 1 Replies View Related

Trying To Insert Checkbox True/false Into Db

Aug 15, 2005

Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db.  The db field i'm setting has type set to "bit", is this correct?  And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer             Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'"             Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)                 Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)"             Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)                 sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code             sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name             sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc             sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size             sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price             sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category                 If chkAspnet.Checked = True Then                sqlCommand.Parameters.Add("@aspnet","1")             Else                sqlCommand.Parameters.Add("@aspnet","0")             End If                     Dim rowsAffected As Integer = 0             sqlConnection.Open             Try                 rowsAffected = sqlCommand.ExecuteNonQuery             Finally                 sqlConnection.Close             End Try             Return rowsAffected         End Function             Sub SubmitBtn_Click(sender As Object, e As EventArgs)            If Page.IsValid then                InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value)                Response.Redirect("ListAllProducts.aspx")            End If    End SubAny help would be appreciated or links to tutorials.ThanksBen

View 2 Replies View Related

How Transaction Return True And False

Jun 10, 2008

Sir

I want to Return 1 and 0 after update , delete , Insert statement

IF Records Effected Return 1 else return 0

Pls help me out .........Sir

Yaman

View 4 Replies View Related

Setting A Bit Value To (@param='C') Ie True Or False

Nov 19, 2007

I want to pass a single char to a query and use that to set two flags.

(

@ID int,

@AssessedID int,

@CompetencyID int,

@Status char,

@Creator int

)



AS

UPDATE P4_Assessment

SET P4_Cancelled_f = (@Status = ('C')),

P4_Competent_f = (@Status = ('P')),

P4_Date = getdate(),

P4_Creator = @Creator

WHERE P4_ID = @ID


I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't.
This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))


Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?

TIA

View 3 Replies View Related

Convert Function True/false Test

Apr 5, 1999

I need a way to test if a convert function will work before I process it. if it fails, I want to intercept the error and return my own error to the front end

ex
if convert(int,@x) is true then do; else do

please email me if anyone has some advice
Mike

View 1 Replies View Related

Insert True/False Into Boolean Field

Jun 12, 2008

When I enter a a true or false value into a boolean field, what is the proper way to enter it? I'm using:

INSERT INTO tblTable (IsSomething)
VALUES (False)

I've heard that a better way is to use 1 and 0, or something.

View 1 Replies View Related

Results From Query Are TRUE And FALSE But Users Wanting YES And NO

Jan 7, 2004

Hi everyone.

I've got a Select query that pulls out some data from my database. Two of the columns are both booleans (bit's of size 1) so they come back as TRUE and FALSE - which I thought was fine.

However, the users are wanting to see YES and NO since they find TRUE and FALSE confusing (yes I know how silly that sounds).

Is there any way I can do this?

My query is like this:
SELECT [stuff], [things] FROM [table1], [table2] WHERE [table1].[condition] = [table2].[condition]

Thanks
Andrew

View 4 Replies View Related

Dtabase Design For Storing True/false Questions

Feb 12, 2007

Hai,

I am trying to design a database for storing trule/false questions.
I have no need to store any thing like chapters are topic. I want to just design a bare minimum tables for just storing questions and its responses.
So does the following tables and fields suffice to achieve that?.

Questions_table(id(autogenerated),course_id,Questi on(text type))
id field uniquely identify each question, course_id(ex:CRS235) is used to identify which course a particular question belongs to, and Question filed store actual question.

Response_table(id,response)
Response field is of int type, 1 for correct answer, 0 for wrong answer. I am not storing options for each question because every question has same options that is True/False.

So, will this work. Please advice and I would appreciate if any one can point me to a good resource on the web.

Thanks,

View 2 Replies View Related

DropObjectsFirst=False; CopyData=True; ExistingData=Replace

May 12, 2008

Hi,
I'm using SSIS to update the destination table.

Both servers are running identical SQL Server 2005 versions.

I want to copy data and replacing existing data WITHOUT dropping the destination table first.

The SSIS package works when:
1) DropObjectsFirst = TRUE
2) CopyData = TRUE
3) ExistingData = REPLACE

The SSIS package does not work when:
1) DropObjectsFirst = FALSE
2) CopyData = TRUE
3) ExistingData = REPLACE


The error I get is the "destination table already exists". Of course, it already exists. I just want to copy/update the destination table. So, why does SSIS insist on dropping the table first?

View 3 Replies View Related

SQL 2012 :: Enable Promotion Of Distributed Transactions For RPC - Set To TRUE Or FALSE

May 14, 2015

It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.

Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:

Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:

OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.

... when set to off, the error goes away.

View 8 Replies View Related

How To Return True If A Sql Row Exists

Mar 10, 2008

 Hi all, I am trying to access a sql database and if the userid exists in the database to the let me query another statement within an IF block to check another statement within the data, however I cannot seem to get it to work. I need something like sql.row.count != 0 within  the 2nd IF statement below. What can I do?Thanks in advance.Jason   using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;public partial class _Default : System.Web.UI.Page {    protected void Page_Load(object sender, EventArgs e)    {            }    protected void btnStart_Click(object sender, ImageClickEventArgs e)    {        if (Session["userid"] == null)        {            Response.Redirect("accessdenied.aspx");        }        else        {            //Response.Redirect("page1.aspx");            SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);            objConnect.Open();            SqlCommand cmd = new SqlCommand("SELECT user_id FROM users WHERE user_id = '" + Session["userid"] + "'");            if (cmd == true)            {                Response.Redirect("page5.aspx");            }            objConnect.Close();        }    }} 

View 7 Replies View Related

T-SQL (SS2K8) :: Table Compare - Getting False Matches

Mar 26, 2014

I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.

The problem is that when I run my compare I get false matches. Example:

CREATE TABLE #ABC (Acct VARCHAR(10), Que INT);
INSERT INTO #ABC VALUES
('2310947',110),
('2310947',245);

[Code] ....

Which gives me two records when I really do not want any as the tables are identical.

View 2 Replies View Related

Minimizing Penalty (weighted Sum Of False Positives Plus False Negatives)

May 25, 2006

I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.

How can I enforce a different penalty for a false positive versus a false negative ?  (I am assuming that in some sense the mining algorithms can then minimize the total penalty).

View 5 Replies View Related

If Exists Command Returning An Error

Feb 24, 2007

Hello, can anyone see a problem with this T-SQL? 1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 GO
4 ALTER PROCEDURE [dbo].[Logon_P]
5 @User_ID VARCHAR(50),
6 @User_Password VARCHAR(50)
7 AS
8
9 IF EXISTS(SELECT 1
10 FROM [User]
11 WHERE [User_Name] = @User_ID)
12 BEGIN
13 RETURN 1
14 IF ((SELECT User_Password FROM dbo.[User] WHERE [User_Name]) = @User_ID) = @User_Password
15 BEGIN
16 RETURN 2
17 END
18 END
19 ELSE
20 RETURN 0
21 Its returning the following error:Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11An expression of non-boolean type specified in a context where a condition is expected, near ')'. 

View 3 Replies View Related

NOT IN Vs. NOT EXISTS Returning Diff Results

Mar 12, 2004

select count(orgcode)
from orglevel
where orglvl = 1 and exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

26 rows

select count(orgcode)
from orglevel
where orglvl = 1 and orgcode in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

26 rows

select count(orgcode)
from orglevel
where orglvl = 1 and not exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

6 rows

select count(orgcode)
from orglevel
where orglvl = 1 and orgcode not in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

zero rows


Confusing the hell out of me .....

View 2 Replies View Related

T-SQL (SS2K8) :: Returning MAX (Value) In String

Jul 29, 2014

I need to create a TSQL to return MAX(Value) removing the first part and last part.

Example

I have these Varchar Reference code:

1.00001-Q1
2.00100-Q2
3.00005-Q4

I need to cut the string to find the max number excluding (Part1):

1.
2.
3.

And also excluding (Part3):

-Q1
-Q2
-Q4

In this case converting varchar to INT, the correct value that i want is: 00101

The middle part excluding (Part1 and Part3)

Then my final reference could be:

1.00101-Q1
or
2.00101-Q2
or
3.00101-Q4

View 3 Replies View Related

T-SQL (SS2K8) :: After Update Trigger - Only Insert Records Not Exists

Jul 30, 2014

I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:

ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress]
FOR UPDATE
AS
DECLARE @rc AS INT ;

[code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Returning Only Portion Of String?

Jun 19, 2014

i need to cut my string on 3 portion, for exemple my string is :

a) 1.9999-Q1

b) 01.9999-Q11

I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:

a) 1
9999
Q1

b) 01
9999
Q11

View 9 Replies View Related

T-SQL (SS2K8) :: Returning Multiple TOP Records?

Jul 10, 2014

Here is my setup: I have the following tables -

tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account

I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.

People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.

So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.

But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?

Straight TOP by itself won't do it.
GROUP BY by itself won't do it.

View 9 Replies View Related

T-SQL (SS2K8) :: Returning Only Documents By Condition

Aug 6, 2015

I need to build a query that can return only documents where the field "u_DIM4" for the same document have more than one different value..my script are this one just to having an example:

Select docnome [documentname], adoc [docnr], count(*) [countAlldifferentbyDoc], u_dim4
from fn
where u_dim4 <> '' and data between '2015-01-01' and '2015-07-31'
AND adoc = '02634'
Group by docnome,adoc,u_dim4
ORDER BY 2 asc

[code]...

View 4 Replies View Related

T-SQL (SS2K8) :: Select Asterisk Not Returning All Columns In UDF

May 13, 2015

I have a UDF with a select * that works fine in one region (DEV) but not another (QC). It's not returning the last 2 columns from the table in QC.I looked at the UDF and it does a fairly simple select:

select a.*
from myTable A

The table is the same in both regions and I did a sp_help on the table to ensure these 2 columns are listed. They are. Also, executing a select * in a query windows does return the final 2 columns from the table in QC. The issue resides in the QC version of the UDF only.

The UDF has already been updated to retrieve all columns by name but I'm curious why this would happen. For some reason I'd just like to know and in case it happens again.

View 5 Replies View Related

T-SQL (SS2K8) :: Historical Data Where Number Of Records Exists Between Two Dates With Different Years

Jul 10, 2015

Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.

A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.

SELECT
MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10],
MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10],
MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11],
MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11],
MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],

[Code] ....

I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).

View 4 Replies View Related

T-SQL (SS2K8) :: Stored Procedure Returning Inconsistent Results?

Mar 11, 2015

Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.

Background

We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.

The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.

Observations

The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.

So the Sproc...

USE [xxxxxxxxxx]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

[Code]....

So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).

The only suggestions I can make have been...

change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS

The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.

I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?

View 6 Replies View Related

T-SQL (SS2K8) :: Returning Stored Procedure Results Into CTE Or Temp Table?

Aug 20, 2013

Is it possible to return the results of a stored procedure into either a CTE or temp table?

In other words, is it possible to do this:

with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???

View 9 Replies View Related

T-SQL (SS2K8) :: Returning Results That Fall Within Current Financial Year?

Jun 3, 2014

I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.

Perhaps using the datediff() function, or other functions as required to achieve what I need?

View 3 Replies View Related

T-SQL (SS2K8) :: Prevent SELECT Query From Returning Results Using LOCKS

Dec 2, 2014

I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.

I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??

Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.

View 9 Replies View Related

T-SQL (SS2K8) :: Returning Rows Within Certain Distance In Miles From City Using Longitude And Latitude

Nov 19, 2014

I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.

Two things I don't like about query:

1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)

Sample DDL: 2 tables
create table dim_lead
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,

[Code] .....

View 9 Replies View Related

T-SQL (SS2K8) :: Get Last Record In A Month When No Guarantee Month Exists Of Unique Dates?

Apr 22, 2015

following table global_usage

ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01

[Code] .....

Now the problem,per month I need the most recent value so I'm expecting

ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01

I've tried a few thing like group,having even row_number() but I keep getting wrong results

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved