Help - Conditional Checks Within A SQL Query - Other Ways Of Doing It?
Jun 13, 2007
I'm trying to simplify a SQL Stored Procedure.
The query accepts an int, @ItemTypeID int
I have the Query:
SELECT ... FROM ItemList WHERE ItemTypeID = @ItemTypeID
or, if @ItemTypeID is 0,
SELECT ... FROM ItemList
Is there a way to do this query without doing:
IF @ItemTypeID = 0
BEGIN
...SELECT QUERY...
END
ELSE
BEGIN
...SELECT QUERY...
END
?
I have two connections in a package pointing to two different databases on the same server. I have to insert records from 'DB1' table 'Gender1' to 'DB2' table 'Gender2'. Before I do that though, I have to make sure the minimum value (of all the Gender Keys that are going to be inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the maximum value of DB2-GenderKey (which is a primary key but not an identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.
I have I rather complex query and need to display a Sum in different ways. Now I'm wondering if the performance improves if you nest the queries in the described way. To me it looks, as if the sum just has to be calculated once?
select SUM(tiempo) as time_minutes, CONVERT(varchar(5), dateadd (minute,sum(tiempo),'1900-1-1 0:00'), 114) as time_hours, SUM(time)* 0.95 as time_discount from table
Select tiempo as time_minutes, CONVERT(varchar(5), dateadd (minute,tiempo,'1900-1-1 0:00'), 114) as time_hours, tiempo * 0.95 as time_discount From ( Select Sum(tiempo) as tiempo from table)a
I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:
SELECT A.col5, SUM(C.col6) AS col6sum FROM TableA AS A INNER JOIN TableB AS B ON A.col1 = B.col1 INNER JOIN TableC AS C ON B.col2 = c.col2 WHERE A.col3 = constant 1 AND B.col4 = constant2 GROUP BY A.col5;
The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."
Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:
1. When A Join B, Left: A, Right: B. Â Â When Join C, Left: result of A join B, Right: C
2. When A Join B, nbsp;  When Join C, nbsp;When A Join B, nbsp;  When Join C, nbsp;When A Join B, nbsp;   When Join C, "line-height:13.5px;">
Q2:Â The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."
How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.
Hi again. Another problem though... Can I make a query in if else statement? For example int querynum; if(querynum==1)<execute query1> else if(querynum==2)<execute query2> else <execute query3>
There is no problem with passing parameters(getquerynum) from my front end application to sql function. I can manage to do it. hehe.
the query looks like this:
DECLARE @querynum number SET @querynum = @getquerynum
what i want to do is,
if(querynum==1) Select * from hremployees else if(querynum==2) Select * from hrapplicants else Select * from pspersonaldata
is it possible by having multiple queries in conditional statement? if so, how to do it in sql language? Thanks -Ron-
I have a table having CusId, CusName, LocId and TvId among other fields. I have 3 dropdown list which are are populated with cusid, locid and tovid.
I have added "Please Select" in all the 3 ddl with value -1. Now i have written a sql statement as :
declare @CusId int declare @LocId int declare @TvId int
select CusId, CusName, LocId and TvId From CustDetails where CusId = @CusId and LocId = @LocId and TvId = @TvId
All i want is if any of the three variables has a value -1 then query should be modified. say @CusId has a value of -1 selected in dropdown list then query should become
select CusId, CusName, LocId and TvId From CustDetails where CusId IS NOT NULL and LocId = @LocId and TvId = @TvId
Please help me on this as i am totally new to sql.
Hi,I'm trying to construct a query (in a stored procedure) which will have a number ofselection criteria based on input parameters. There are a number of these parameterswhose selection conditions they represent which all have to be true for a row to bereturned in the resultset.The basic query is:SELECT Store, StoreNumberFROM StoresWHERE ...I'm trying to come up with the WHERE clause.For example, I want to define a parameter named @ExcludeSpecialties which if it hasthe value 1, means to return all stores but exclude stores whose StoreNumber is inthe list (800, 802, 804). If the parameter has the value 0, then it means "don'tcare" and all StoreNumbers should be returned.One could certainly argue that there probably should have been an column in theStores row to indicate the store is a specialty store, rather than using a hard-wiredlist of numbers. But the current data schema cannot be easily changed. Besides, thelist never changes.Indeed, there is a Franchise bit column in the row which is selected by anotherparameter called @ExcludeFranchise whose WHERE predicate could be written as:WHERE Franchise = CASE WHEN @ExcludeFranchise = 1 THEN 0 ELSE Franchise ENDand if all the parameters were like this, I wouldn't be posting. Sadly, for theSpecialties test I'm stuck with a NOT IN list.This is easy enough to do in an IF/ELSE block, but there are several such similarparameters whose values may be specified in any combination. This, I think, makesIF/ELSE impractical as the number of IF/ELSE statements to handle all possiblecombinations would grow very quickly.I'm hoping there is a simple solution to this NOT IN list, and it's just that I can'tsee it.Can anyone help?Thanks,-- Jeff
Hello everybody,After several attempts of writing the query, I had to post myrequirement in the forum.Here is what I have, what I need and what I did.Table ACol1 Col21 Nm12 Nm23 Nm3Table BCol1 Col210 10020 200Table CCol1 (A.Col1) Col2 (B.Col1)1 102 10Table DCol1 (A.Col1) Col21 Value12 Value2I need results based on below criteria,1.Criteria - B.Col2 = 100ResultsetA.Col1 D.Col11 Value12 Value22.Criteria - B.Col2 =""A.Col1 D.Col11 Value12 Value23 NULL3.Criteria - B.Col2 =200Empty resultsetHere is the query I tried, but looks its not working. Probably there isa better way to do this.DDL and DML statements:create table #tab1 (a1 int, a2 nvarchar(20))create table #tab2 (b1 int, b2 int)create table #tab3 (c1 int, c2 int)create table #tab4 (d1 int, d2 nvarchar(20))insert into #tab1 values (1, 'nm1')insert into #tab1 values (2, 'nm2')insert into #tab1 values (3, 'nm3')insert into #tab2 values (10, 100)insert into #tab2 values (20, 200)insert into #tab3 values (1, 10)insert into #tab3 values (2, 10)insert into #tab4 values (1, 'value1')insert into #tab4 values (2, 'value2')selecta.a1, d.d2from #tab1 aleft join #tab3 bon a.a1 = b.c1left join #tab2 con b.c2 = c.b1left join #tab4 don a.a1 = d.d1wherec.b2 = [100 or 200 or ''] or exists (select 1 from #tab4 dwhere a.a1 = d.d1and c.b2 = [100 or 200 or ''] )The above query works well to give results for Criteria 1 and Criteria3, but doesn't return for '' (criteria 2). I couldn't manage crackingthe solution. I shall try once again, but meanwhile if anyone couldhelp me in this, that would be great.Thanks.
EmployeesA empId integer, empName varchar(60), empAge integer, empStatus char(1) - can be N,D or S - New, Deleted or Shifted
and the following in Sql2005
EmployeesB Id smallint, Name varchar(60), Age int, Status char(1) - Bydefault 'N'
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
If empStatus = N in EmployeesA, then insert a new record in EmployeesB If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D' If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
How do I do it for each table each row in EmployeesA using a foreach file loop?
SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.
CREATE PROCEDURE dbo.GetByVersion ( @targetVersion varchar(30), @product varchar(50) ) AS SET NOCOUNT ON; SELECT * FROM MyTable WHERE (product = @product) AND CASE WHEN @targetVersion='' THEN (targetVersion='') ELSE (targetVersion LIKE @targetVersion + '%') END GO
I get a syntax error in the Stored Procedure editor on an equal sign in this line: WHEN @targetVersion='' THEN (targetVersion='')
What I want is this (in psuedocode): if @targerVersion is blank search for records where the targetVersion column = blank else search for records where the targetVersion column starts with @targetVersion
Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)
I'm converting some direct SQL queries into stored procedures. The original queries have conditional parts appended depending on certain parameters like this:
Sql = "First part" If certain condition then Sql &= "Second part" Endif
How could I do this in a stored procedure? So far I have made them like this:
IF @condition > 0 Fist part Second part ELSE First part
You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?
SELECT table1.col1 ,€™n/a€™ _response FROM table1 INNER JOIN table2
This is the query that get the report data for my report. Now I need to replace the second column with an actual response like €˜accepted€™ and €˜rejected€™. And these values should be a result of evaluation of this form
If(Statusarray.count < 1) Set _response = €˜accepted€™ Else Get Statusarray[1] Compare this to Statusarray[0] Set _response = some result based on comparision.
The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.
How can this be done in sql(using SQL Server 2005)
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. this is the source error for this error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) please any one can help me?
I want move data from informix to SQL server 7.0 every 10 seconds. I do not know what is the best way. It seems that I need create a DTS package and schdule the task from SQL server Agent, but can the task be schduled to reoccurred every 10 seconds?
In a very busy SQL2000 enterprise edition server with 8GB memory and 6 cpus sp3, I could not install a update trigger, unless all the appl connections are dropped. For this 24 HR running svr, could do it.
then I try to run a query as follows:
if exists (select rfABC.* A from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro begin print ' ---- Yes metroID <> LAMetro, start job exec.... -----'
insert into tempCatchMetroIDGPRS select rfABC.*, metro, getdate() from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on rfABC.Address = L.address and rfABC.metro <> L.metro
update rfABC A set A.metro = L.metro from rfABC A inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro end else begin print ' ---- no metroID <> LAMetro, skip job exec.... -----' end
------------------------------ this query hang there could not execute. When I took off the if ... else condition, it run with like 0 second. Wondered if a 'busy' (which updates the IP address continueously) could cause above issues...
Suppose you have two (or more) tables with foreign key constraints. Myquestion is thus:Is it better to check if the fk exists before you try to perform theinsert or let SQL do it for you?On one hand, if you check yourself and the key does not exist you cangracefully handle it (maybe exit out of method with error). If you letSQL do it, the server will throw an error which cannot be suppressed.On the performance side, you doing the check will incur a slight (VERYslight) hit since SQL will ALSO check anyways.
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
I have to made a change and want to see it this is possible. The column IDPRT# is used here as MAX. THe user wants to exclude a few of the items which skews the average. Can i also select the IDPRT# a second time? and then in the CRystal reports I can select not =? but will this mess up the MAX line in any way?
SELECT "OEIND94"."IDDOCD" AS INV_DATE, "OEIND94"."IDORD#" AS ORD_NUM, "OEIND94"."IDORDT" AS ORD_TYPE, "OEIND94"."IDPRLC" AS PROD_FAMILY, "OEIND94"."IDPR$C" AS PRICE_CODE, "OEIND94"."IDCOM#", MAX("OEIND94"."IDPRT#") AS ITEM_REF, "ICPRT1"."IARCC4" AS PROD_TYPE,
My week starts on Monday rather than on Sunday which is default(US, English) in SQL Server and would like to change the same so I would get proper weeknumber and dayindex using DATEPART.
I am looking at different ways of setting DATEFIRST(SET DATEFIRST 1) in SQL Server 2005.
I could set in a stored procedure, but this isn't a feasible way for me because I am using .nettiers to generate by business objects and stored procedures. I need to alter the sp's everytime I newly generate the code and sp's.
I couldn't set it in a function which I was hoping initially. I understand this datefirst is stored in one of the sys table in MASTER DB and I couldn't find a straight forward way to change this.
Can anyone suggest me a way to set the DATEFIRST either at a database level or at a server level(probably by changing the sys table in MASTER DB).
I'm starting to collect and develop some scripts that will tell me the health and welfare of my MSSQL 2k server. I have a few for blocks, db size, who is on and what they are currently running.
I was wondering if you guys could share some of the scripts you guys use to watch the health of your servers.
On weekends I have Integrity Checks scheduled to run. Many of these fail for individual databases because users do not log off and the databases cannot be switched to single user mode.
I have checked Books-on-line and have not yet stumbled onto a TSQL command that breaks the connections.
Is there a TSQL command to do this? If not, how can these connections be broken?
I'm starting to collect and develop some scripts that will tell me the health and welfare of my MSSQL 2k server. I have a few for blocks, db size, who is on and what they are currently running. I was wondering if you guys could share some of the scripts you guys use to watch the health of your servers.
How many ways are there to connect to a database in ASP.NET? Could someone list them in a 1,2,3... manner. For those who are going to ask why I want to know this and why I don't do one way, I'll explain after I get the answer.
I have a few databases on this Windows 2000 Server running SQL 2000 which were detached from SQL 7.0 and attached to SQL 2000. The problem is the Maintenance Plans (Integrity Checks keep failing on SQL 2000. I 'DTS'ed a SQL 7.0 database to this SQL 2000 server and ran the Maintenance Plans on that database. Works fine only for the DTS'ed database. What am I missing ??? :confused:
Hello, I had a DB Maintenance plan, the schedule is every day, but today I found teh 'Integrity checks job is failed". What is that mean? How to check this. Thanks.
I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT ) between 400 & 500 # 20 > 501 # 10
NOV between 300 & 400 #30 between 400 & 500 # 20 > 501 # 10
I have Daily User DB Integrity Checks job running daily From past 2 days i am getting below error.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515). [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'. [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast). [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.