Return Value For NOT EXISTS

Oct 24, 2006

Hi

I frequently use the "If NOT EXISTS (Select ...) Insert ..." statement and would like to display a message to the user after the execution of the statement to say whether the Insert was or wasn't executed. Is there a way to get SQL to return a value that will tell me if the Insert was carried out or not? If not, perhaps you know of some other efficient way of handling this situation.

Regards

Neil

View 6 Replies


ADVERTISEMENT

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

RETURN Of IF NOT EXISTS INSERT Statement

Feb 20, 2006

Hi @ all

I'm using MSSQL and PHP.
I've got the following sql statement:

$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')

The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused:
mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not.
I tried:

$result = mssql_query($msquery);
$succeed = mssql_rows_affected ($result);

And:

$result = mssql_query($msquery);
$succeed = mssql_num_rows($result);

to get the rows, affected by the statement, but both return:

supplied argument is not a valid MS SQL-Link resource

Anyone an idea?

View 2 Replies View Related

If Username Exists, Return Try Again Otherwise Write To Db

Sep 9, 2007

I am a SQL newbie trying to create a registration page. The registration data is stored in "tblUsers," and the username is stored in the field named "UID" -- which is the primary key. If the user enters a username that already exists in the database, I want to return the "That username already exists" message and keep them on the registration page. If the username doesn't exist, I want to write all their registration info to the database.

I tried the code below using both an existing username and then using a username that didn't exist. Both times I got the "that username already exists" error message on the top of the confirmation page, and the record was not written to the database.

I also tried changing the "If objRec.RecordCount <> 0" to "If objRec.RecordCount >0" I got it to write to the database if the username didn't exist, but if the username did exist, I got the microsoft error '80040e14' (The changes you requested to the table were not successful because they would create duplicate values...)

Thanks for any help.


<%
SQLCmd = "SELECT UID From tblUsers WHERE UID = " & "'" & UID & "'"
Set objRec = objConn.Execute(SQLCmd)
If objRec.RecordCount <> 0 Then
Response.write "That username already exists"
Else
SQLCmd = "INSERT INTO tblUsers"
SQLCmd = SQLCmd & " (FIRSTNAME, LASTNAME, UID, PWD, STREET, CITY, STATE, ZIP, EMAIL, PHONE, FAX, PHYSICIAN, SPECIALTY, OTHER) "
SQLCmd = SQLCmd & " VALUES ('" & firstname & "',"
SQLCmd = SQLCmd & "'" & lastname & "',"
SQLCmd = SQLCmd & "'" & UID & "',"
SQLCmd = SQLCmd & "'" & PWD & "',"
SQLCmd = SQLCmd & "'" & street & "',"
SQLCmd = SQLCmd & "'" & city & "',"
SQLCmd = SQLCmd & "'" & state & "',"
SQLCmd = SQLCmd & "'" & zip & "',"
SQLCmd = SQLCmd & "'" & email & "',"
SQLCmd = SQLCmd & "'" & phone & "',"
SQLCmd = SQLCmd & "'" & fax & "',"
SQLCmd = SQLCmd & "'" & physician & "',"
SQLCmd = SQLCmd & "'" & specialty & "',"
SQLCmd = SQLCmd & "'" & other & "')"
Set objRec = objConn.Execute(SQLCmd)
End If
%>

View 14 Replies View Related

Return Rows Only If More Than 1 Row Of The Same Policy Number Exists?

Sep 25, 2007

I have a policy table which has policyNumbers and createDate. I need to retrieve all rows where createDate is between 2 dates and there is more than 1 row with the same policy number. I cannot figure out the SQL to return all policy number rows if there are more than 1 row with the policy. Most policies number will be in the table once. I need the others.

Thanks for suggestions.

View 1 Replies View Related

SQL Server 2012 :: How To Return User-defined Row When A Record Doesn't Exists

Dec 29, 2014

What I want to do is return a row of data when my query doesn't return a record. I have two tables:

CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY

[Code] ....

So when I run the following query:

SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00

As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as

abc-1-200.00
abc-1-500.00
def-0-0.00

View 4 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

View 3 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?

I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).

Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View 5 Replies View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, good day,

i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(

anyone know how to handle the problem when SP return no data ?

thanks in advance

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

IF NOT EXISTS

Jul 7, 2006

Hello,
I am trying to create a table if one with the same name does not exists.  My code is:
Dim connectionString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|PensionDistrict4.mdf;Integrated Security=True;User Instance=True"
Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
Dim newTable As String = "CREATE TABLE [" + titleString + "Comments" + "] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"
sqlConnection.Open()
Dim sqlExists As String = "IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString + "Comments" + "')"
Dim sqlCommand As New SqlCommand(newTable, sqlConnection)
If sqlExists = True Then
sqlCommand.Cancel()
Else
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
End If
I keep getting a "Input String was incorrect format" for sqlExists?  I am new to Transact-SQL statements, any help would be appreciated.
Thanks Matt

View 1 Replies View Related

Where Not Exists---help

May 27, 2007

ok i have 2 tables---one table is name CourseInformation with a field named Instructor and the data in there looks like this 'John Doe'.
My other table Instructors has 3 fields InstructorName, LastName, FirstName.
I am grabbing the Instructor field from CourseInformation and breaking up the names and inserting them into my instructors table as follows..
Insert into Instructors(InstructorName, LastName, FirstName)
(Select Distinct
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor)))+', '+
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1),
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor))) as LName,
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1) as FNamefrom CourseInformation
where NOT EXISTS(Select * from instructors where LastName = LName and FirstName = FName)
AND Instructor is not null)
 
Only problem is, I cant get the where not exists clause to work right(of course that wont work what i have cuz the LName and FName columns dont exist, i just did that for demo purposes).  I dont want duplicate instructors in there..how can i accomplish this..is there a better way to rewrite my query? Any help is appreciated.
 

View 3 Replies View Related

NOT EXISTS Vs NOT IN

Feb 7, 2008

HI All,Which is best among the two 1) NOT IN or 2) NOT EXISTS .If the query is Select col1 from tab1 where col2 NOT IN (Select col 3 from tab2 where cl3=0) OR Select col1 from tab1 where col2 NOT EXISTS (Select col 3 from tab2 where cl3=0)  

View 2 Replies View Related

If Not Exists

Oct 12, 2001

Hi,

I have a question on the if not exists. Can you do something like this? If request not exists in table one then insert values into table two?

Thank you in advance,
Anne

View 3 Replies View Related

If Exists..

Jul 15, 2004

Hi,
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.

i'm looking for the script, something like this..

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO

In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.

View 2 Replies View Related

Help With EXISTS

Jun 15, 2007

hi guys! is there anyway to retrieve the row from the result of EXISTS function aside from using the code below?


if EXISTS (select * from rcps_useraccount where User_login = 'daimous')
BEGIN
select * from rcps_useraccount where User_login = 'daimous'
END

View 3 Replies View Related

Exists

May 16, 2008

Im having a problem with the following can anyone spot how i can fix it? I dont think it likes the begin statement but without it, it has a declare issue.




IF EXISTS (SELECT 1
FROM snapevent.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='FastEp_Snap_OD_Acc' + preports.dbo.f_filedate(getdate())
begin
declare @CMD nvarchar(300)
Set @CMD = 'drop table Snap_OD_Acc_' + preports.dbo.f_filedate(getdate())
--print @CMD
exec (@CMD)
end

View 1 Replies View Related

If Exists - SP

Jun 20, 2008

Hi

Please help me

I have to create a SP.

The secenario is that,
A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)

UserID AcctID Level1 level2
test testee N Y

the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.


After checking all the strings ,
it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows

View 1 Replies View Related

Where Exists

Feb 18, 2006

I'm trying to perform an insert query on a table, but I also want to check to see if the record exists already in the table. It should be fairly simple, but I'm having a time of it. Should be something like:

select * from users u
inner join miamiherald m
on u.emailaddress = m.advertiseremail
where not u.emailaddress not exists <<< (???)

If it does exist, I then want to retrieve two columns from it. HELP!!

View 1 Replies View Related

IF EXISTS

May 28, 2007

Hi,

The following works in SQL 2005 but NOT SQL 2005 Compact Edition:

IF EXISTS (SELECT ID FROM Court2 WHERE BookingDate = '2007-05-28')
UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')
ELSE
INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52)

In CE I get the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

I can't find where the problem is - can someone help.

Thanks,

View 5 Replies View Related

Which One Is Better In Or Exists

Dec 6, 2007

Dear all,
i am trying to improve the performance of stored procedures and functions in that in key word is there i have to replace with exists.which one will give better performance.


Thanks&Regards,

Msrs

View 2 Replies View Related

From In To Exists

Dec 6, 2007

Dear all,
i'm trying to replace in with exists...
i've changed the query but still i'm getting error.
please let me know where i'm missing

DELETE FROM table25 WHERE Col6='SET' AND Col1 IN (SELECT s.Col1 FROM Table24 s
WHERE Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

DELETE FROM table25 r WHERE r.Col6='SET' AND exists (SELECT s.Col1 FROM Table24 s
WHERE r.col1=s.col2 and Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

thank you very much


Vinod
Even you learn 1%, Learn it with 100% confidence.

View 9 Replies View Related

Need Help With IF And EXISTS

Jan 15, 2008

I'm trying to search my table for a url, and if it in the table, increment the number of hits of the url. Here's my code:

tblLinks
-----------------------
URL | NumOfHits
-----------------------



ALTER PROCEDURE sproc_UpdateLink

(
@url varchar
)

AS

IF EXISTS(SELECT LinkURL
FROM tblLinks
WHERE @url = LinkURL)
BEGIN
UPDATE tblLinks
SET NumOfHits = NumOfHits + 1
WHERE @url = LinkURL
END

RETURN


I've also tried:


ALTER PROCEDURE sproc_UpdateLink

(
@url varchar
)

AS

IF (SELECT LinkURL
FROM tblLinks
WHERE @url = LinkURL) IS NOT NULL
BEGIN
UPDATE tblLinks
SET NumOfHits = NumOfHits + 1
WHERE @url = LinkURL
END

RETURN


but that didn't work either.

View 7 Replies View Related

IF EXISTS

Mar 20, 2008

There's an error in this SQL statement somewhere, but I can't seem to find it. Here's what I'm trying to do:

I have a list of states, and each state is assigned an ID
For each state, I have some data
Given a state name, find the state's ID
If the state ID exists, output the data for that state
If the state ID is not in the database, output the data for all states

Here's my tables:

--------------------------
States
--------------------------
ID | Name
--------------------------


--------------------------
Data
--------------------------
StateID | StateData
--------------------------



Here's my SQL:

ALTER PROCEDURE sproc_GetStateData
(
@State varchar
)
AS
DECLARE @StateID int

IF EXISTS(SELECT @StateID = ID FROM States WHERE Name = @State)
BEGIN
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
WHERE (D.StateID = @StateID)
END
ELSE
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
RETURN

View 6 Replies View Related

Help With Exists

Jul 23, 2005

Hi,I need some help doing a simple query. Here is what I haveSELECT Category_Id, Category_nameFROM AG_Category WHERE Auth_Logic_Id = 244And Category_ID(I want category_id to not have a '.' in it. Basically returningeverything above but checking also the category_id so it doesn't returnone where category_id have a . in it)Thanks:D

View 1 Replies View Related

Using EXISTS

Jul 23, 2005

I need to insert records into the table parSalesDetailModifier fromOLDparSalesDetailModifier where (1) those records DO NOT exit inparSalesDetailModifier and (2) those records have a parent record inparSalesDetail.When I run the below query I get the error message that I am violatingthe Primary Key Constraint for parSalesDetailModifier. In other words,it's trying to insert a record that does exist.Also posted below are create and insert startements for thte tables.If someone would be kind enough to show me what I am doing wrong, I'dreally appreciate it.Thanks,Jennifer-------------------------------- STORED PROCEDURE-------------------------------CREATE Proc LoadModifier2@S datetime,@E datetimeASINSERT INTO ParSalesDetailModifier(parSalesDetailModifierID,parSalesHdrID,parSalesDetailID,ModifierType,POSModifier,Condiment,CondimentPrice,UnitNumber,BusinessDay)SELECTOLD.parSalesDetailModifierID,OLD.parSalesHdrID,OLD.parSalesDetailID,OLD.ModifierType,OLD.POSModifier,OLD.Condiment,OLD.CondimentPrice,OLD.UnitNumber,OLD.BusinessDayFROM OldParSalesDetailModifier OLDWHEREEXISTS( SELECT DET.parSalesHdrID,DET.parSalesDetailID,DET.UnitNumber,DET.BusinessDayFROM ParSalesDetail DETWHERE OLD.parSalesHdrID = DET.parSalesHdrIDANDOLD.parSalesDetailID = DET.parSalesDetailIDANDOLD.UnitNumber = DET.UnitNumberANDOLD.BusinessDay = DET.BusinessDay)ANDNOT EXISTS( SELECT NEW.parSalesHdrID,NEW.parSalesDetailID,NEW.parSalesDetailModifierID,NEW.UnitNumber,NEW.BusinessDayFROM ParSalesDetailModifier NEWWHERE OLD.parSalesHdrID = NEW.parSalesHdrIDANDOLD.parSalesDetailID = NEW.parSalesDetailIDANDOLD.parSalesDetailModifierID = NEW.parSalesDetailModifierIDANDOLD.UnitNumber = NEW.UnitNumberANDOLD.BusinessDay = NEW.BusinessDay)AND OLD.BusinessDay between @S and @E-------------------------------- END STORED PROCEDURE--------------------------------------------------------------- CREATE TABLES-------------------------------CREATE TABLE [parSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetailModifier] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID],[ParSalesDetailModifierID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetailModifier_parSalesDetail] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) REFERENCES [parSalesDetail] ([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID])) ON [PRIMARY]GOCREATE TABLE [OLDparSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NULL) ON [PRIMARY]GOCREATE TABLE [parSalesDetail] ([parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[Before] [int] NOT NULL ,[Quantity] [int] NOT NULL ,[After] [int] NOT NULL ,[Promo] [money] NOT NULL ,[PromoBefore] [money] NOT NULL ,[ItemPrice] [money] NOT NULL ,[PromoAfter] [money] NOT NULL ,[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[UnitNumber] [int] NOT NULL ,[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[AmountTenderTime] [datetime] NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetail] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetail_parSalesHdr] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID]) REFERENCES [parSalesHdr] ([BusinessDay],[UnitNumber],[parSalesHdrID])) ON [PRIMARY]GO-------------------------------- END CREATE TABLES--------------------------------------------------------------- INSERT INTO TABLES-------------------------------insert into parSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917332,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917332,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917340,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917340,2,'NOONIN','NO ONIN',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917340,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(4,2298561,10917340,2,'WELL','WELL',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917341,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917341,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917341,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into parSalesDetailvalues(2298561,10917332,0,1,0,.0000,.0000,3.4900,. 0000,'DM',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917340,0,1,0,.0000,.0000,.2500,.0 000,'JALA',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917341,0,1,0,.0000,.0000,1.3400,.0000,'MD-DP',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928910,0,1,0,.0000,.0000,.9900,.0000,'2PIE99',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928911,0,1,0,.0000,.0000,.5900,.0000,'DECAF',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928912,0,1,0,.0000,.0000,1.6900,.0000,'BOB-BAC',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10929376,0,1,0,.0000,.0000,.5900,.0000,'COFFEE',2,'N','2003-12-0110:00:44.000','2003-12-01')-------------------------------- END INSERT INTO TABLES-------------------------------

View 7 Replies View Related

IF EXISTS Help

Oct 31, 2007

Hi,
I'm having problems with this T-SQL:
Sorry if this is obvious, I'm very new to this...

Just trying to return the ID if the row exists and if not just return 0.
What would I need to turn this into a stored procedure too?





Code Block

declare @Return int

if exists(select id from Computer where Computer_name = 'MYCOMPUTER')
begin
select @Return = id
PRINT @Return
end
else
begin
select @Return = 0
PRINT @Return
end


I'm getting this error....

Msg 207, Level 16, State 1, Line 5Invalid column name 'id'.Msg 207, Level 16, State 1, Line 10Invalid column name 'id'.

View 5 Replies View Related

NOT IN, NOT EXISTS

Mar 10, 2008



I'm running into a problem on my SQL2000 server. I thought all 3 of the following queries SHOULD return the same results, but the query using the NOT IN does not return any records where the other 2 queries DO.

Is my understanding of NOT IN wrong, or is there something corrupt in my DB?

SELECT *
FROM [person]
WHERE personid NOT IN (SELECT personid FROM [personOrganization])

SELECT *
FROM [person]
LEFT OUTER JOIN [personOrganization]
ON [Person].[personId] = [personOrganization].[personId]
WHERE [personOrganization].personid IS NULL

SELECT *
FROM person
WHERE NOT EXISTS(SELECT 1 FROM [personOrganization] WHERE [personOrganization].personid=person.personid)


View 11 Replies View Related

IF NOT EXISTS

Mar 25, 2008

Hi all,

I'm trying to run "alter table" to add a new column to the tables but the problem is some of the tables have the column and some don't.

Is there a way to use if not exists to alter the tables with no column only??

thanks,

View 7 Replies View Related

EXISTS

Aug 30, 2006

Well, finally I lost one day to find a solution for a simple problem. I
wanted to derive two tables... that seemed to be simple... but it isn't.



My last question for today:



Why does my sql query return the error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS



The Query:



SELECT
Configurations.AlarmId, Configurations.Mode, Configurations.Activated,
Configurations.Empty, Configurations.UserName, Configurations.datetime,

(SELECT ConfigurationId, Setpoint1, Alarm

FROM
dbo.ExtendedValues(Configurations.ConfigurationId) AS ExtendedValues)
AS Expr1
FROM Configurations INNER JOIN

Controllers ON Configurations.ConfigurationId =
Controllers.ActiveConfig INNER JOIN

Tanques ON Controllers.ControllerId = Tanques.ControllerId
WHERE (Tanques.TanqueId = 1)



Where do I have to enter EXISTS?



Thanks for every hint!!



P.D.: The ExtendedValues Function:



CREATE FUNCTION dbo.ExtendedValues
    (
        @ConfigurationId int = -1
    )
RETURNS TABLE
AS
    RETURN SELECT *
FROM ConfigurationsTanques
WHERE ConfigurationsTanques.ConfigurationId = @ConfigurationId

View 1 Replies View Related







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