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
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
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]
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
Case 1
execute an SQL Update Command
Case 2
execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
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???
How do I properly write a CASE statement for the following business logic:
I want three of the following fields to be true in order to return 'Y'.
For example:
If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'
Otherwise, I want it to return 'N'.
I have tried the following code below and it is not working.
, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :
Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Below is the my sql statement:
SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,
ItemRef =
Case
when RH.Quantity < 0 then Sum(RH.Quantity)
when RH.Quantity >= 0 then Sum(0)
end
FROM dbo.RHISTORY AS RH INNER JOIN
dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID
WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007')
AND (RH.TransactionCode IN (13, 14, 15, 16))
Group by dbo.DateOnly(TH.TransactionDate), RH.Item
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
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
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!
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 RelatedHi,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 RelatedHi,
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...
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
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
Hi all,
I have 2 select statements in my Stored Proc.
I want to display the results of each query in my DataGridView.
However, only the data of the last select query is returned.
Why is this?
Thanks.
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
View 3 Replies View Related
Hi,
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too.
Basically those are SELECT statements.
I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report.
If possible, can anyone show me the format?
Thanks
Hey Guys. I’m having a little trouble and was wondering if you could help me out. I’m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What I’m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned?
Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql)
{
DataTable dt = new DataTable();
SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString());
SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon);
System.Data.DataSet ds = new System.Data.DataSet();
SqlCmd.Fill(ds);
dt = ds.Tables[0];
//Here's where I don't know how to access the second select statement
return dt;
} Here's my stored procedure:
ALTER PROCEDURE dbo.MyStoredProcedure
(
@Page int,
@AmountPerPage int,
@TotalRecords int output
)
AS
WITH MyTable AS
(
Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum
From Table
where Deleted <> 1
)
select * from MyTable
WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1);
Select @TotalRecords = COUNT(*)
from Table
where Deleted <> 1
RETURN
Thanks
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
View 5 Replies View RelatedI ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?
SELECT o.name, o.object_id,
ps.last_execution_time ,
ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds,
ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds,
ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
[code]...
Hey folks, having a problem here...
I have 2 piece of code designed to do the same thing. My problem is, i'm not getting the same results.
Code 1 where the results are correct
Code:
select Count(*) as TotalCount, Sum(DistAmt) as TotalSum
from table1
inner join table2
on table2.id = table1.id
where MailTypeID = 3
AND MailEventID = 2
and table1.IsActive = 1
code 2 - Y is correct, but Z is not, and not only is it not correct, but it is returning a number which equals more then the total rows from the table.
Code:
select
Y = sum(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then distamt else 0 end),
Z = count(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then 1 else 0 end)
from table1
inner join table2
on table2.id = table1.id
I have no idea what is going on.
Thanks!
Caden
Hi,
Is there a way to use more criteria in a CASE statement other than CASE WHEN expression THEN value ELSE value END
I need to test if the count is greater than 0. If so, then perform the case statement, else return zeros. Currently there are entries where the values are blank. These blank values are causing errors in the application and unfortunately, I am not able to update these values.
So far I have the following, but I am getting an error stating "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference."
Thanks in advance!
Code:
IF @Qid = 4
SELECT @Exp as Status, COUNT(*) AS Total, @CourseID as CourseID,
(SELECT
question
FROM
tableQuestions
WHERE
qid = @Qid) AS Question,
IF COUNT(*)>0 THEN
1.0 * SUM(CASE WHEN A.Q1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS Positive,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS Neutral,
1.0 * SUM(CASE WHEN A.Q1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS Negative,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS NA
ELSE
0 AS Positive,
0 AS Neutral,
0 AS Negative,
0 AS NA
END IF
FROM
table1 A INNER JOIN table2 B ON A.SessionID = B.SessionID
WHERE
(B.CourseID = @CourseID) AND (A.SubmitDate >= @BeginDate) AND (A.SubmitDate <=@EndDate)
This works EXCEPT when I add the ELSE line.
How do I accomplish the ELSE?
Does the ELSE have to state "WHEN [BG_STATUS] <> 'Blocked' and [BG_STATUS] <> 'Closed', etc? Do I have to delineate for the ELSE statement everything that BG_STATUS is *not* equal to? Seems there ought to be a way but I can't find it.
SELECT BG_SEVERITY AS 'Severity',
SUM(CASE WHEN [BG_STATUS] = 'Blocked' THEN 1 ELSE 0 END) as 'Blocked',
SUM(CASE WHEN [BG_STATUS] = 'Closed' THEN 1 ELSE 0 END) as 'Closed',
SUM(CASE WHEN [BG_STATUS] = 'Customer Test' THEN 1 ELSE 0 END) as 'Customer Test',
SUM(CASE WHEN [BG_STATUS] = 'Deferred' THEN 1 ELSE 0 END) as 'Deferred',
[Code] ....
I am creating a stored procedure which receives the following 4 variables. However, VacancySectorID is the only madatory variable.
@VacancySectorID int = NULL, (mandatory)
@VacancyRegionID int = NULL, optional)
@VacancyTypeID int = NULL, (optional)
@VacancyKeywords nvarchar(64) = NULL, (optional)
My objective is to dynamically build the WHERE statement which in turn will return either one or more of the variables and the required data. The following code works for a single if else but it appears that you cannot have more than one if else statement.
Anyones input would be greatly appreciated.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Public_GetVacancyListingBySearchCriteria]
(
@VacancySectorID int = NULL,
@VacancyRegionID int = NULL,
@VacancyTypeID int = NULL,
@VacancyKeywords nvarchar(64) = NULL
)
AS
--SET NOCOUNT ON;
IF @VacancySectorID IS NULL AND @VacancyRegionID Is NULL AND @VacancyTypeID IS NULL AND @VacancyKeywords IS NULL
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1 AND VacancySectorID = @VacancySectorID
ORDER BYVacancyPosted DESC, VacancyTitle
END
ELSE
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1
ORDER BYVacancyPosted DESC, VacancyTitle
END
whats the most efficient way to do this? I have a column that contains three values. A, B, C If colVal = A add one to batch a, if B add one to batch b...ect . Then display all three values
View 3 Replies View Relatedhi,
I want to know how can i equate the following case statements into
one Condition? Following is my Code
(case when ws.Action_Taken_ID not in(3,17,18) then '-1' else '0' end) as istechfcr,
(case when datediff(day,dbo.Usp_Get_Date(pr.Set_Serial_Number),oh.WO_Record_Date) <= q.Product_Gurantee_Months + 6 then '-1' else '0' end) as istechfcr,
(case when substring(ltrim(rtrim(ws.Symptom)),1,1) not in('X') then '-1' else '0' end) as istechfcr,
(case when ws.Action_Taken_ID not in(8,15,21,25) then '-1' else '0' end) as istechfcr,
Thanks..
Hi is it possible to have a case statement with 2 or more constraints ?
Like we have
CASE x
WHEN 1 THEN ..
WHEN 2 THEN ..
but what if i want a constraint like :
IF (x= 0 AND y=0)
{
then...
}
can this be done in a CASE statement ?
if not , can I use IF with SQL ?
Hi all,I have to translate an Access query into sql. The query has thefollowing statement. I know SQL doesn't support iif, so can someone tellme how to use the case statement to get the same result?select field1,IIf(Grand_total-50>0, grand_total-50, 0) AS field2,field3Thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedI'm trying to make a view that uses organization name from one tableand contact first and last name from another table. In the view Ihave a field that I want to show Organization followed by the maincontact. Problem is if the organization field or name field is NULLthen it doesn't show anything. If one field is empty I still want itto show the other field in the table.Example:Org1--ContactOrg2--ContactOrg3 (Still shows org even without a contact name)Contact (Still shows contact even without an org name)Tried using a CASE statement but didn't work.
View 1 Replies View RelatedDoes anyone know if there is a way to look at a value in a variable and based on that value run different Data Flow Tasks?
For example, let's say I have an SSIS package that contains a variable named Client and 3 separate Data Flow Tasks. I would like to do this: if Client = 1 then run Data Flow 1 else if Client = 2 then run Data Flow 2 else run Data Flow 3.
Is this possible?
Thanks.
Danielle
Can anyone show me how to write or post a sample of a nested case statement?
Thanks,
LOC
Hi All,
I'am trying to program a nested CASE statements (if this is not possible, does anyone have any alternate suggestions ?) and I'm getting syntax errors.
The statement is:
SELECT @cmdLine =
CASE @BackupType
WHEN 1 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
END
WHEN 2 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, INIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP, STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, NOINIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
WHEN 3 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Log ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP LOG ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
ELSE ''
END
TIA,
Romy Stevensen