Enclosing Multiple Statements In A Variable

Oct 22, 2004

Hi all;

How do I enclose multiple filters in a variable, for instance how would I put the following filter into a variable and also is it actually possible or do I have to do something else before performing this type of operation:

tel1 like '072%' or tel1 like '+27 72%' or tel1 like '072-%'
or tel1 like '+2772%' or tel1 like '72%'
and len(tel1) > 7
or tel2 like '072%' or tel2 like '+27 72%' or tel2 like '072-%'
or tel2 like '+2772%' or tel2 like '72%'
and len(tel2) > 7
or tel3 like '072%' or tel3 like '+27 72%' or tel3 like '072-%'
or tel3 like '+2772%' or tel3 like '72%'
and len(tel3) > 7
or tel4 like '072%' or tel4 like '+27 72%' or tel4 like '072-%'
or tel4 like '+2772%' or tel4 like '72%'
and len(tel4) > 7
or tel5 like '072%' or tel5 like '+27 72%' or tel5 like '072-%'
or tel5 like '+2772%' or tel5 like '72%'
and len(tel5) > 7
or tel_other like '072%' or tel_other like '+27 72%' or tel_other like '072-%'
or tel_other like '+2772%' or tel_other like '72%'
and len(tel_other) > 7

the problem is that it's got a couple of apostrophes which when declaring variables pulls it out of that mode, I have looked on the internet but can't seem to find anything

View 8 Replies


ADVERTISEMENT

Help Enclosing Create Procedure In Begin..end

May 28, 2008

I am trying to enclose a create procedure in a begin...end block and get the following:


Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'PROCEDURE'.

If i execute the create w/o the begin..end it executes correctly. I am at a complete loss. The greater plan is this: when we roll out an updated version of our software we update many things in the database, I need a way to halt and rollback any database changes if there is an error. If anyone has a better suggestion of help with resolving my error I would greatly appreciate it.

Here is my statement:


begin

CREATE PROCEDURE dbo.LMS_InvTurnsEOMUpdate

AS

-- EOM Script to update the InvTurnsEOMCostHistory and InvTurnsEOMInventory tables.

-- these are used for the Inventory Turns Report.

-- Add this script to SQL jobs to run on the last day of the month.

-- kaj 1/08

--exec LMS_InvTurnsEOMUpdate

--select * from InvTurnsEOMCostHistory

--select * from InvTurnsEOMInventory

declare @PeriodID varchar(10)

declare @PeriodDisplaySeq numeric(7,0)

declare @LMSDateCur numeric(7,0)

-- get current system date in NG format

--select cast(getdate() as smalldatetime)

set @LMSDateCur = ((year(getdate())*10000+month(getdate())*100+day(getdate()))-19000000)

-- get period id and period display seq

select @PeriodID = AOAHNB, @PeriodDisplaySeq = AOI4NB

from YAAOREP

where @LMSDATECUR>=AOAADT and @LMSDATECUR <=AOABDT

-- delete everything in InvTurnsEOMCostHistory Table that has a Period ID and Period Display Seq = current

delete from InvTurnsEOMCostHistory where @PeriodID = AOAHNB

-- insert into the InvTurnsEOMCostHistory Table

insert into InvTurnsEOMCostHistory

select ItemPrice.Item_ID, ' ', @PeriodID, @PeriodDisplaySeq,

@LMSDateCur, ItemPrice.ItemPrice_AvgCost, ItemPrice.ItemPrice_PurchaseCost,

ItemPrice.ItemPrice_LastCost, ItemPrice.ItemPrice_StandardCost,

Item.Item_PackMultiple

From ItemPriceMaxDat

Left outer join ItemPrice on ItemPrice.Item_ID = ItemPriceMaxDat.Item_id and

ItemPrice.ItemPrice_DateOfData = ItemPriceMaxDat.ItemPrice_DateOfData

Left outer join Item on ItemPriceMaxDat.Item_ID = Item.Item_Id

Where ItemPrice.CEST='1'



-- delete everything in InvTurnsEOMInventory Table that has a Period ID and Period Display Seq = current

delete from InvTurnsEOMInventory where @PeriodID = AOAHNB



insert into InvTurnsEOMInventory

select WH_ID, Item_ID, @PeriodID, @PeriodDisplaySeq,

@LMSDateCur, ItemWH_BackOrder, ItemWH_OnOrder, ItemWH_Allocated,

ItemWH_OnHand

From ItemWH

Where ItemWh.CEST='1'

end



Thanks,

Sean

View 5 Replies View Related

Select Declared Variable With Case Statements

Apr 19, 2008

I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email.
I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email.

I have declared all variables appropriately but I will leave that section of the code out.


Select @ifiledate = iFileDate from tblTelemark where
iFileDate = CASE
WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN

Select @countfri1 = Count(*) from tbl1
Select @countfri2 = Count(*) from tbl2
Select @countfri3 = Count(*) from tbl3
Select @countfri4 = Count(*) from tbl4


WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN
Select @countsat1 = Count(*) from tbl1
Select @countsat2 = Count(*) from tbl2
Select @countsat3 = Count(*) from tbl3
Select @countsat4 = Count(*) from tbl4

WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN
Select @countsun1 = Count(*) from tbl1
Select @countsun2 = Count(*) from tbl2
Select @countsun3 = Count(*) from tbl3
Select @countsun4 = Count(*) from tbl4


END

Is there a way to do what this that works???

View 3 Replies View Related

Matrix Reports Spawn Extra Page - Enclosing In List Fixes This.

Apr 20, 2007

We really like using the Matrix reports, but we were finding that every Matrix report that we created would spawn an extraneous blank page. We tried putting the matrix in a rectangle, which works well for positioning other items on reports, but this had no effect on the problem.



Then we tried placing the matrix in a list with the list group details set to "=Nothing". It worked great - no more extra pages. Looked and didn't see this tip mentioned elsewhere so thought it might be worth sharing.

View 1 Replies View Related

How To Do Multiple If Statements

Oct 11, 2015

I want the below sql code to force a error if it falls on a saturday or sunday, and there are no rows in the OrderTrans table for the previous day (TransDate) but not sure how to do multiple if statements.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrderTrans](
[OrderId] [int] NOT NULL,

[Code] ....

View 0 Replies View Related

Using Transactions With Multiple SQL Statements

Oct 31, 2006

I am writing a fairly complex data automation procedure that basically uses about 5 stored procedures and some additional sql from C# to populate a database. i would like to utilize transactions in C# and sql to help me if something goes wrong.  My question is, do i have to maintain the same connection in order to have one transaction for all my Commands.  right now i do things like connection.Open();Command.ExecuteNonQuery(); connection.Close(); connection.Open();Command2.ExecuteNonQuery(); connection.Close(); i think i have to keep the same connection open for the duration of all my commands in order to utilize transactions, is this correct? thanks, mcm

View 5 Replies View Related

How To Execute Multiple Statements?

Sep 17, 2007

Is there a way to execute multiple SQL statements within SQL Server? I'm triyng to write a sql statement that will delete from three tables. I need at least two sql statements to delete from these three tables. Can this be done?

View 12 Replies View Related

USING MULTIPLE SELECT STATEMENTS

Apr 23, 2008

how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
 Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
 
Code2.....
 
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
 
 thanks in advance

View 10 Replies View Related

Multiple SQL Statements In One SQLCommand

Oct 3, 2005

I am just wondering if it is possible using SQL Server 2000 to have multiple SQL Statements executed with one sqlComm.ExecuteNonQuery(); call?

View 1 Replies View Related

Multiple Select Statements

Dec 22, 2005

Hi guys and gals,
I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:
'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
My Code is below:
//Start of sql
CREATE PROCEDURE ADMIN_GetSingleUsers( @userID  int) AS
DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,    Cast(Users.Active as  varchar(50)) as Active,   Cast(Users.Approved as  varchar(50)) as Approved,   Users.Unit_ID As usersUnitID,   *    From TITLE, Users   WHERE    User_ID = @userID AND   TITLE.TITLE_ID = Users.Title_ID )
Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL
//End of sql
Can you point to what I am doing wrong? Thanks in advance!

View 4 Replies View Related

Multiple Case Statements

Mar 14, 2008

This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?

select
contact1.accountno,
contact1.key5 as "Ch#",
contact2.uexpsort as "Sort",
contact1.company as "Church",
contact1.contact as "Editor",
contact1.phone1 as "Phone",
contact2.uemerg as "Emergency",
contact1.Address1 as "Address",
contact1.city as "City",
contact1.state as "State",
contact1.zip as "Zip",
contact2.ubulletnqt as "Qty",
contact2.ubullcolor as "BullColor",
contact2.ubarcode as "Barcode",
contact2.udelivery as "Delivery",
contact2.uoutputdev as "OutputDev",
contact2.utimedeliv as "Time",
contact2.ucolor as "DelivColor",
contact2.ucollated as "Collated",
contact2.ustapled as "Stapled",
case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes",
case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"

from
Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno
inner join contsupp on Contact1.Accountno=Contsupp.Accountno
where
contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5

Thanks for your help

View 2 Replies View Related

CTE With Multiple Update Statements?

Sep 19, 2006

HI Gurus

I have written one CTE (common table expression) and trying to use same CTE with three seperate UPDATE statements which gives me error saying "Invalid Object name" (it works fine when I try to use with 1 update statement (any one from three update statements)



Isnt it possible that I can use 1 CTE with mutiple update statements?



waiting for your reply....

View 4 Replies View Related

Multiple MDX Statements In A Report

Aug 28, 2007

Does anyone know how to execute more than one MDX statement in a single report? For example, If you wanted to create a session set, run some mdx with the set included, then drop the session set. In Management studio the mdx works perfectly if you separate the individual mdx statements with GO. I tried using semicolons as mdx statement terminators and this didn't work either.

Reporting Services does something between when the report begins it's execution and the time the MDX is submitted to AS. And running a trace to see exactly what is passed in doesn't work either - the only message displayed is the same error message as what is passed back to RS. (I ran a trace with all events selected).

Anyone have any ideas?

Thank you in advance,
John

View 3 Replies View Related

Parameter For Multiple Where Statements

Sep 5, 2007

I'm hoping someone out there has a creative solution for my scenario. I have the report code below. I want to create a parameter that allows the user to select which where statement to use in the report. Then I want to use the corresponding where description below ( ex: @WHEREDESC1 ) in my header which will describe (in laymen's terms) which parameter was chosen and what is being reported on.

Has anyone achieved this or have an idea how it may be done?



DECLARE @WHEREDESC1 VARCHAR(50), @WHEREDESC2 VARCHAR(50), @WHEREDESC3 VARCHAR(50),

@WHEREDESC4 VARCHAR(50), @WHEREDESC5 VARCHAR(50)

SET @WHEREDESC1 = 'SRM Admitting and Principal Diagnosis do not match'

SET @WHEREDESC2 = 'SRM and AMPFM Admit dates do not match'

SET @WHEREDESC3 = 'SRM and AMPFM Discharge dates do not match'

SET @WHEREDESC4 = 'SRM and AMPFM Visit Type Codes do not match'

SET @WHEREDESC5 = 'SRM and AMPFM DRG codes do not match'

SELECT LEFT(srm.EPISODES.ADMISSION_DATE, 11) AS SRM_Admit_DT, ampfm.rpt_AdtVisit.AdmitDate AS AMPFM_Admit_DT,

LEFT(srm.EPISODES.EPISODE_DATE, 11) AS SRM_Disch_DT, ampfm.rpt_AdtVisit.DischDate AS AMPFM_Disch_DT,

srm.EPISODES.EPISODE_TYPE AS SRM_Visit_Type, ampfm.rpt_AdtVisit.VisitTypeCode AS AMPFM_Visit_Type,

srm.EPISODES.CHARGES AS SRM_Charges, ampfm.rpt_VisitFinancial.TotCharges AS AMPFM_Charges,

ampfm.rpt_VisitFinancial.VisitChargesActive AS AMPFM_Active_Charges, ampfm.rpt_AdtVisit.PatientFullName,

srm.CDMAB_DRG_INFO.DRG_NUMBER AS SRM_Drg, ampfm.rpt_InsuranceDrg.InsDrg AS AMPFM_Drg,

ampfm.rpt_AdtVisit.Complaint AS AMPFM_Admit_Dx, srm.cdmab_base_info.ADM_DX_CODE, srm.cdmab_base_info.ADM_DX_DESC,

srm.cdmab_base_info.PRIN_DX_CODE, srm.cdmab_base_info.PRIN_DX_DESC, srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.MEDREC_NO

FROM srm.EPISODES INNER JOIN

ampfm.rpt_AdtVisit ON srm.EPISODES.ACCOUNT_NUMBER = ampfm.rpt_AdtVisit.AccountNumber INNER JOIN

ampfm.rpt_VisitFinancial ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_VisitFinancial.IntOOS INNER JOIN

ampfm.rpt_InsuranceDrg ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_InsuranceDrg.IntOOS AND

ampfm.rpt_AdtVisit.VisitTypeCode = ampfm.rpt_InsuranceDrg.VisitTypeCode INNER JOIN

srm.CDMAB_DRG_INFO ON srm.EPISODES.EPISODE_KEY = srm.CDMAB_DRG_INFO.EPISODE_KEY INNER JOIN

srm.cdmab_base_info ON srm.EPISODES.EPISODE_KEY = srm.cdmab_base_info.EPISODE_KEY

--WHERE srm.cdmab_base_info.ADM_DX_CODE <> srm.cdmab_base_info.PRIN_DX_CODE

--WHERE srm.EPISODES.ADMISSION_DATE <> ampfm.rpt_AdtVisit.AdmitDate

--WHERE srm.EPISODES.EPISODE_DATE <> ampfm.rpt_AdtVisit.DischDate

--WHERE srm.EPISODES.EPISODE_TYPE <> ampfm.rpt_AdtVisit.VisitTypeCode

--WHERE srm.CDMAB_DRG_INFO.DRG_NUMBER <> ampfm.rpt_InsuranceDrg.InsDrg

Order By srm.EPISODES.ACCOUNT_NUMBER

View 7 Replies View Related

Execute Multiple Statements At Once...

Oct 17, 2006

I am not even sure if this is possible but I want to be able to excute several statements (or SPs) at once from inside a SPs (or any other method).

What I am doing is a I am taking data from a single column, multiple rows and making it into one row (i.e. data1 + data2 + data3....) But I am doing this to a total of 2.1 million individual rows and the result will be about 204k rows.... what I have written is basically a nested loop and it works fine but very slow... slow as in it has been running for 24 hours now and it is about 60% done... I need this to finish in under 36 hours preferably...

If I could handle more than one set of data at once (there will be no duplicates) I could speed up the process by how ever many I feel like working with...

So is there a way to execute a statement (sp or function) and go to the next statement without waiting for the first to finish??



Thanks Big Time... I hope this is possible.

OH.... SQL 2000 SP4 on Windows 2003.

View 16 Replies View Related

Combining Multiple Select Statements In A SP

Jul 27, 2007

I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set.  For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set? 

View 2 Replies View Related

Update Multiple Columns In One Sql Statements

Nov 2, 2004

I am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.

I have a table with transactions - with tran types as the key.

in this example, types = A,B,C ,D.

in this first example I am updating the sum of QTY to value t_A based on
tran types =A.

can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.



Create table #t_reconcile(
t_lot_number int not null,
t_A float,
t_B float,
t_C float,
t_D float)


insert #t_reconcile

select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number

View 3 Replies View Related

How To Run Multiple Sql Statements From A Single File?

Nov 15, 2005

Hi,I am wondering if anyone has any examples of how to run multiple sql statements from a file using .net?  I want to automatically install any stored procedures or scripts from a single file on the server when my web application runs for the first time.  Thanks for your help in advance!

View 1 Replies View Related

Condensing Multiple Update Statements

Mar 18, 2008

I'm working on a query which involves changing the case of a field from mixed case to all lower case. The field exists in multiple tables, so to do this I have multiple update statements. Is there a way to make this more efficient?

See below for example:

update InvestBroker
set BrokerID = lower(BrokerID)

update InvestFill
set BrokerID = lower(BrokerID)

update InvestBrokerAccount
set BrokerID = lower(BrokerID)

update InvestFIXBroker
set BrokerID = lower(BrokerID)

update InvestUploadBrokerFilter
set BrokerID = lower(BrokerID)

update InvestSettleInstructions
set BrokerID = lower(BrokerID)

View 2 Replies View Related

Multiple Case Statements In One Select

Dec 16, 2014

I know I should know the answer to this, but I just can't quite get the syntax down

Code:
Select case when zipCode = '10185' Then 'Deliver'
Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '88221' And paid = 'No' Then 'Hold'
Else when zipCode = '34123' Then 'Deliver'
End
From postalDeliveryDatabase

View 7 Replies View Related

Multiple Statements In Stored Procedure

Apr 29, 2008

I believe we can you multiple statements in stored procedures?

Suppose I have a stored procedure and I pass parameters to this SP.
What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below:

CREATE sp_temp_proc
@order_id int,
@order_position int,
@temp_order_id OUTPUT
@temp_order_position OUTPUT,

SELECT order_id AS temp_order_id
FROM <TABLE> WHERE order_position < @order_position

@temp_order_id = temp_order_id


UPDATE <TABLE> SET order_position = @order_position WHERE order_id =
@temp_order_id

UPDATE <TABLE> SET order_position = @temp_order_position WHERE order_id = @order_id

View 2 Replies View Related

Are Multiple Statements Allowed For CommandText?

Dec 4, 2007

Hi,

I'm wondering if SqlCommand.CommandText could be set with multiple statements when CommandType=Text. anyone knows it?

I'm sure storedprocedure is the right way to go. I'm curious if this is one thing that only sp can do.

Thx
Tao

View 1 Replies View Related

Multiple SQL Statements In A Stored Procedure

Jul 23, 2005

Hi!I got 2 stored procedure, proc1 executes proc2,proc2 does some updates and inserts on different tables ...proc1:ALTER PROCEDUREASexecute proc2SELECT * FROM tblFoo______________________my problem is, that when executing proc1, I receive the message:"THE SP executed successfully, but did not return records!"But I need the resultset from "SELECT * FROM tblFoo" that is executedat the end of proc1.I'm not sure, but I think that I solved a similira problem with "setnocount on", I put it into both SP, but it's still the same ... noresultset ...How can I display "SELECT * FROM tblFoo" within a SP, where SQLstatements are executed before?!Thank you!

View 1 Replies View Related

Analysis :: Multiple IIF Statements In SSAS?

Jun 2, 2015

I am using multiple IIF statements in ssas, it does not produce any output, does not shows any error, simply it processing..

View 3 Replies View Related

Use Of Multiple Unconnected Or Unassociated IIF Statements

Jun 19, 2007

Hello,

I am curious as to whether I can insert more than one IIF statement into a table's visibility expression without nesting one inside the other.

I am rather loath to do so because of the risks that subsequent developers unfamiliar with both the situation and the logic could alter one IIF and not realize that if chokes off the other.

It had been my hope to apply the IIFs such that they would act independent of one another in a sort-of 'OR' statement whereby if one resulted in the table's hidden property being 'True' then it would not matter if the other's was true or false, etc.



Thanks in advance.

View 2 Replies View Related

Transact SQL :: Can Have Multiple Statements Under CASE-THEN

Jun 3, 2015

So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....

CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]

View 3 Replies View Related

Price Of Using Multiple Databases In SELECT Statements

Sep 11, 2007

Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?  

View 2 Replies View Related

Stored Procedure With Multiple Update Statements

Jan 31, 2008

I have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. "
 The procedure takes in three parameters and retrieves 23 values from the DB to display on my form.
Any ideas?
 

View 4 Replies View Related

DataSet With Multiple SELECT Statements To The Same Table

Apr 29, 2008

Hi,
 OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC)        objConn.Open()        sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'"        Dim objCommand As New SqlDataAdapter(sSQL, objConn)        DS = New DataSet()        objCommand.Fill(DS)        Repeater1.DataSource = DS        Repeater1.DataBind()        DS.Dispose()        objCommand.Dispose()        objConn.Close()    End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...

View 5 Replies View Related

Multiple Order By Statements In Union Select

Jan 20, 2005

I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.

declare @OrderBy
@OrderBy = 'first_name'

Select first_name,last_name from table1
Union
Select first_name,last_name from table2

if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end

You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help

View 6 Replies View Related

Execute Multiple SQL Statements In Stored Proc

Nov 1, 2005

Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)

Table Test

Id Description

1Insert into test(Id,Name) Values (1,'Ron')
2Update Test Set Name = 'Robert' where Id = 1
3Delete from Test where Id = 1


In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks

Reo

View 2 Replies View Related

Insert Statement With Multiple Select Statements

Aug 29, 2006

hi

first of all is it possible? if so, what am i doing wrong with this



INSERT into TB2

(

ClientCode,
EngagementCode,
EngagementDescription

)




SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),

NULL,

SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)


the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.

this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks

tibor

View 5 Replies View Related

EXIT Installation Scripts That Have Multiple GO Statements

Oct 26, 2007

Hi All I'm Scripting various Upgrade scripts that set up roles / users / create databases etc and want to Exit the Script unconditionally if for example the user already exists etc. I can't Use RETURN because it just jumps to the Code following the next GO statement There is also some USE [Database] Statements Is there a way to do this or am I missing something fundamental here ? GW

View 5 Replies View Related







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