EXEC Inside CASE Inside SELECT

Nov 16, 2007

I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.

@val1 and @val2 are passed in


CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)

INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2


SELECT COUNT(*) FROM #TEMP WHERE mybool = 1


If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.

Any suggestions?

View 8 Replies


ADVERTISEMENT

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

EXEC Inside A Cursor Iteration Problem

Jul 20, 2005

I 've have a stored procedure that compares fields across databases.In order to do so it requires 2 values it acquires from 2 tables. Thesearch is based on the ID of the data owner and a subject:proc_evaluate_results @StudentId = '222222', Course = 'PSY101'In order to obtain those values I run a cursor accross my records andSELECT THEM INTO 2 @variables, which then replace 222222 and PSY101with dynamic values eg.--define a cursor etc.etc.WHILE @@cursor_fetch = 0BEGIN--do the cursor call INTO @varsEXEC proc_evaluate_results @StudentId = @studentID, @Course =@CourseCodeENDNow,the vars are being passed to the stored procedure and executed OK,but the cursor gets stuck on the last record and continues to evaluateit until stopped manually.If I comment out the EXEC and replace it with eg. PRINT @Course + ' |' + @CourseCode it runs fine, exiting after the last record.ThanksR>

View 6 Replies View Related

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?

Sep 12, 2007

For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.

View 9 Replies View Related

Case Inside Begin.....end

May 16, 2002

begin
if @counter=2
update t_import_main set program2=@prog, g2=@gno,yr2=@yr, Program_code2=@pcode where uno=@oldu
case
when @pcode is null then
Program_code2=@gno
End

I get this error message
Incorrect syntax near the keyword 'case'.
What am i doing wrong??

View 2 Replies View Related

Subquery Inside Case?

Jun 8, 2008

Hello,

I am trying to do something like this:

select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA


But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.

How do I do this?

View 2 Replies View Related

Case Inside A Join?

Mar 4, 2014

Is it possible to do something like this:

Select a.address from person p
join Addresses a on p.AddressPointer =
Case when @UseNewAddress=1 then a.NewAddress else a.OldAddress end

It gives a syntax error but I'm wondering if there's another way to do it.

View 2 Replies View Related

'Case' Statement Inside 'Where' Clause

Mar 13, 2008

Hi

I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following?

DECLARE @searchCriteria Int
SET @searchCriteria = 2

SELECT column1, column2 FROM TABLE WHERE
CASE @searchCriteria
WHEN 1 THEN (column3 = 1000100)
WHEN 2 THEN (column3 = 1000101)
END CASE



...cheers

View 14 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

Using IF Inside SELECT ?

Apr 19, 2006

Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND

View 3 Replies View Related

Select Statement Inside UDf

Jul 26, 2007

iam trying to rerieve a certain value from one table
and i want to use that vaue inside a UDF
iam usinf a table valued function as i have to retireve no of values
Can i do something like this to retrieve the value

SET @Value=Select Value from Table WHERE xyz='some no.'
as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.

View 4 Replies View Related

SET Inside Of SELECT Statement

Sep 20, 2007

How can I do this

Select id
set @temp = id
from tblexample

I want to do something, on each row output, with @temp.

View 8 Replies View Related

Using A While Inside A Select Statement

Oct 19, 2007



Hi All,
Can we use the while loop inside a select statement? Meaning, something like this:




Code Block
SELECT DATE,
WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23
(SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) )
FROM SC_DATEDIMENSION_TABLE






What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right.

Hope the question is clear.

Thanks a lot.

Mannu.

View 4 Replies View Related

Counter Inside Select Statement?

Nov 14, 2007

Hi, can you add a counter inside a select statement to get a unique id line of the rows?
In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.
This is the normal way:
SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements
This is what i want:
DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1)
Anyone know if this can be done?

View 2 Replies View Related

Declare Inside Select Statement?

Nov 12, 2003

I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query.

So! Here's the statement that works:

======================
DECLARE @fixeddate datetime
DECLARE @CVG_ELECT char(1)
DECLARE @Effdt datetime
DECLARE EFFDTS CURSOR FOR
SELECT Z.EFFDT, COVERAGE_ELECT
FROM PS_LIFE_ADD_BEN Z
WHERE Z.EMPLID = '1000'
AND Z.EFFDT <=
GETDATE()
AND Z.PLAN_TYPE = '20'
ORDER BY Z.EFFDT DESC
OPEN EFFDTS
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
WHILE @@FETCH_STATUS = 0
BEGIN
if @CVG_ELECT <> 'E'
break
ELSE
SET @fixeddate = @Effdt
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
END

CLOSE EFFDTS
DEALLOCATE EFFDTS
PRINT @fixeddate

======================
If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea).

Is it possible to encapsulate this inside a select statement?

View 6 Replies View Related

Function Inside A Select Statement

Aug 23, 1999

Can I write a function inside a Select statement in sql server 7.0
If so HOW ?

Manish Mehta

View 2 Replies View Related

UPDATE Inside A SELECT Statement?

Mar 24, 2015

I would like to UPDATE a column form my SELECT statement below but not sure how to go about this?!

The column I need to update is: courses.active = N

SELECT schools.id, schools.name, schools.cactus_name, schools.cactus_name_english, schools.address1, schools.address2, schools.city, schools.county, schools.postcode, schools.country, schools.active, schools.latitude, schools.longitude, schools.contact, schools.website, schools.email, schools.telephone, schools.fax, schools.dos, schools.other_contacts, schools.school_commission, schools.bo_notes, courses.name, courses.domains,

[Code] ....

View 7 Replies View Related

Select Inside Update Query?

Feb 21, 2007

Is it possible for me to do something like

update table1 SET var1=something,var2=something2
from table1
(SELECT * from table2) as newtable
where newtable.field1=acondition

View 5 Replies View Related

Select The CheckBox Inside The DataGridView

Feb 29, 2008

Hi,

Can anybody help me fetch "True" & "False" value from a CheckBox inside a DataGridView row.

I tried the following code:-

But the cell.Selected doesnot take the correct value.


objGrid = (System.Windows.Forms.DataGridView)controlObj[0];

foreach (DataGridViewRow row in objGrid.Rows)

{

DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell;



if (cell.Value != cell.FalseValue)



{

if (cell.Selected == true)

{

///Some Code;
}

}



}


Kindly Help ASAP
Thanks

View 4 Replies View Related

Checking For @@ERROR After A SELECT Inside A Transaction?

Feb 26, 2006

Is it normal practice to check for @@ERROR  after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.

View 1 Replies View Related

Subquery With Multiple Rows Inside SELECT

Mar 6, 2008

Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:

Table: Profiles
Fields: Id, ProfileName

Table: Groups
Fields: Id, GroupName, ProfileId

I then need to return something like this:

ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"

Does anyone know how this can be done?
Thanks!

View 7 Replies View Related

SELECT Query Stmt Inside Stored Procedure

Nov 21, 2005

Friends,

What are the possible usuages of a SELECT query stmt inside a stored procedure ??

How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??

can any one throw some lite on this ..

Thanks,
SqlPgmr

View 1 Replies View Related

Newbe Question: Calling Function Inside Select

Jul 20, 2005

Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.

View 4 Replies View Related

Problems Executing A SELECT Inside A TRAN Against Other Computer

Nov 10, 2006

Hi
I have a problem executing a SELECT inside a TRAN against other computer

For example:
IN THE SQL Query Analizer of the COMPUTER2
1) this runs OK
BEGIN TRAN
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
COMMIT TRAN
2) this runs OK
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
3) this runs OK
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
4) this runs bad
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

The problem is that TABLE locks and it does not finish.

I've been looking for similar ERRORS in Microsoft Support but I found nothing
I've uninstall and install de SQL server 2000 SP4 and the problems continues the same


Please, someone could help me, thanks

View 11 Replies View Related

How To Use A Stored Procedure Inside Select Query (sql Server Version 8)

Sep 29, 2007



Hi,
Please help me in this problem...
i am new to sql server..
i am using sql server version 8...(doesnot support function with retun values..)
so i have created a procedure...
-----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
set @myDate = dateadd(dd, 1, @myDate)
end
select @myDate
end
go
[/code]
--------------------------------------------------------
i can able to execute this procedure separately.... working well...
but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
please help me... how to fix this problem...

View 16 Replies View Related

How Can I Fill Data In Textboxes From Sql Databases But Two Different Tables When I Select A Name That Is Inside A Dropdownlist

Apr 9, 2008

HI
I need help 
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee]&#13;&#10;where CurrentEmployee_YN=1&#13;&#10;order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No:</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
&nbsp;<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No:</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant:</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
</center>
<center>
&nbsp;</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
&nbsp; &nbsp;<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
 
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>&nbsp;
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
&nbsp;&nbsp;
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table

View 10 Replies View Related

How Do I Imbed A Select Inside A Select

Apr 14, 2008

I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended.

The statement has a select with an imbedded select and when I execute it I get the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thia is a crude sample of the statement

SELECT ID + ( select * from tableB where TableB = 0 ) as result1
FROM TableB

Why am I getting this error and how do I fix the statement?
thanks

View 8 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

While Inside Of A While?

Oct 16, 2000

I have a statement which might need a while inside of a while. The
start date - end date creates one record for a record insert. I have that working. But along with producing a record for every day there might be an
instance where something is dispersed 3 times a day for five days. I then need to create 3 records for every day for 15 records. This only happens on records if the daily dispersal is greater than 1. The code below works fine, but should I add a second while inside of the existing one for the @freq and increment it by one. Would an If or case inside of the while be better?
Thanks

set @freq = freq in table
set @nodays = datediff(day, @sdate - 1, @edate)
select @nodays
while @cnter < @nodays and
begin
--insert values

insert into PATIENT_MEDICATION_dispersal2_

values (@account_id,@caseid, @entcid, @ndcid, @sdate)
Set @cnter = @cnter + 1
set @sdate = @sdate + 1

end

View 2 Replies View Related

Stm Inside Sql Query

Jul 10, 2006

Hi all
As following I show my sql server query.Please just look at the blue code.How can I add a statement to do not read the code if value received is null, i.e., do not add INNER JOIN stm.Thanks a lot
   string strCmd = "SELECT ";    strCmd += " Codigo_cotacao          as 'Cód. Proposta', ";    strCmd += " Cod_empresa          as 'Cód. Cliente', ";    strCmd += " Nome_empresa          as 'Nome Cliente', ";    strCmd += " Negocios_atividades_propostas.Id_atividade_proposta  as 'Cód. Atividade', ";    strCmd += " Nome_atividade_proposta       as 'Atividade', ";   // add something here if Ramo_cotacao is null and not read the next line    strCmd += " Negocios_ramos.Cod_ramo       as 'Cód. Ramo', ";    strCmd += " convert(varchar,Data_cotacao,103)     as 'Data Proposta', ";    strCmd += " convert(varchar,Vigencia_cotacao_inic,103)   as 'Iníc. Vigência', ";    strCmd += " convert(varchar,Vigencia_cotacao_fim,103)   as 'Térm. Vigência', ";    strCmd += " Nome_status          as 'Status', ";    strCmd += " NVIdas_cotacao          as 'Núm. de Vidas', ";    strCmd += " Premio_cotacao          as 'Prêmio Estimado', ";    strCmd += " Nome_canal           as 'Canal', ";    strCmd += " Nome_corretor          as 'Corretor', ";    strCmd += " Nome_pac           as 'PAC', ";    strCmd += " Negocios_gerentes_canais.Nome_gerente    as 'Gerente Canal', ";    strCmd += " Negocios_gerente_beneficios.Nome_gerente   as 'Gerente Benefícios', ";    strCmd += " Nome_filial          as 'Filial', ";    strCmd += " Nome_regiao          as 'Região', ";    strCmd += " Nome_consultor          as 'Consultor' ";    strCmd += " FROM Negocios_cotacoes  ";    strCmd += " INNER JOIN Negocios_empresas      ON Cod_empresa = Empresa_cotacao ";    strCmd += " INNER JOIN Negocios_atividades_propostas  ON AtivProp_cotacao = Negocios_atividades_propostas.Id_atividade_proposta ";   // add something here if Ramo_cotacao is null and not read the next line    strCmd += " INNER JOIN Negocios_ramos       ON Negocios_ramos.Cod_ramo = Ramo_cotacao ";    strCmd += " INNER JOIN Negocios_status       ON Id_status = Status_cotacao ";    strCmd += " INNER JOIN Negocios_canais       ON Cod_canal = Canal_cotacao ";    strCmd += " INNER JOIN Negocios_corretores      ON Cod_corretor = Corretor_cotacao ";    strCmd += " INNER JOIN Negocios_pacs       ON Cod_pac = Pac_cotacao ";    strCmd += " INNER JOIN Negocios_gerentes_canais    ON Negocios_gerentes_canais.Cod_gerente = GerenteCanal_cotacao ";    strCmd += " INNER JOIN Negocios_gerente_beneficios    ON Negocios_gerente_beneficios.Cod_gerente = GerenteBeneficios_cotacao ";    strCmd += " INNER JOIN Negocios_filiais      ON Negocios_filiais.Cod_filial = Filial_cotacao ";    strCmd += " INNER JOIN Negocios_regioes      ON Cod_regiao = Regiao_cotacao ";    strCmd += " INNER JOIN Negocios_consultores     ON Cod_consultor = Consultor_cotacao ";    strCmd += " INNER JOIN Negocios_produtos     ON Produto_cotacao = Id_produto ";    strCmd += " WHERE Codigo_cotacao <> -1 ";   if (hiddenddlEmpresa.Text != "Todas")    strCmd += "  AND Negocios_empresas.Cod_empresa = "   + hiddenddlEmpresa.Text;   if (hiddenddlCategoria.Text != "Todas")    strCmd += " AND Negocios_categorias.Id_categoria = "  + hiddenddlCategoria.Text;   if (hiddenddlProduto.Text != "Todos")    strCmd += " AND Negocios_produtos.Id_produto = "   + hiddenddlProduto.Text;   if (hiddenddlRamo.Text != "Todos")    strCmd += " AND Negocios_ramos.Cod_ramo = "     + hiddenddlRamo.Text;   if (hiddenddlCorretor.Text != "Todos")    strCmd += " AND Negocios_corretores.Cod_corretor = "  + hiddenddlCorretor.Text;   if (hiddenddlConsultor.Text != "Todos")    strCmd += " AND Negocios_consultores.Cod_consultor = "  + hiddenddlConsultor.Text;   if (hiddenddlCanal.Text != "Todos")    strCmd += " AND Negocios_canais.Cod_canal = "    + hiddenddlCanal.Text;   if (hiddenddlStatus.Text != "Todos")    strCmd += " AND Negocios_status.Id_status = "    + hiddenddlStatus.Text;   if (hiddenddlRegiao.Text != "Todas")    strCmd += " AND Negocios_regioes.Cod_regiao = "    + hiddenddlRegiao.Text;   if (hiddenddlGerenteCanal.Text != "Todos")    strCmd += " AND Negocios_gerentes_canais.Cod_gerente = " + hiddenddlGerenteCanal.Text;   if (hiddenddlFilial.Text != "Todas")    strCmd += " AND Negocios_filiais.Nome_filial = '"    + hiddenddlFilial.Text + "'";   if (hiddenddlAtividadeProposta.Text != "Todas")    strCmd += " AND Negocios_atividades_propostas.Id_atividade_proposta = " + hiddenddlAtividadeProposta.Text;   if (hiddenddlPAC.Text != "Todos")    strCmd += " AND Negocios_pacs.Cod_pac = "     + hiddenddlPAC.Text;   if (hiddenddlGerenteBenef.Text != "Todos")    strCmd += " AND Negocios_gerente_beneficios.Cod_gerente = " + hiddenddlGerenteBenef.Text;   if (hiddentxtDataPropostaInic.Text != "" && hiddentxtDataPropostaFim.Text != "")    strCmd += " AND Data_cotacao BETWEEN '" + hiddentxtDataPropostaInic.Text + "' AND '" +  hiddentxtDataPropostaFim.Text + "'";   if (hiddentxtInicioVigenciaInic.Text != "")    strCmd += " AND Vigencia_cotacao_inic BETWEEN '" + hiddentxtInicioVigenciaInic.Text + "' AND '" +  hiddentxtInicioVigenciaFim.Text + "'";   if (hiddentxtDataPropostaFim.Text != "")    strCmd += " AND Vigencia_cotacao_fim BETWEEN '" + hiddentxtFinalVigenciaInic.Text + "' AND '" +  hiddentxtFinalVigenciaFim.Text + "'";   

View 3 Replies View Related

Can I Use IF Inside A Query?

Jan 7, 2008

Is it possible to use IF inside a query, in the WHERE statement? I started with the query right below, but I onlye got error. After testing and rewriting a lot I ended up with the last query. But there hast to be a better, smarter, more elegant way to write this query? Any hint? ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description:    Return all links from the requested category.-- =============================================    (@CategoryId int,    @AdminFilter bit)AS    SELECT        Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden    FROM        Link    WHERE        Link.Parent = @CategoryId        IF (@AdminFilter = 1)            print 'AND Link.Hidden = @AdminFilter'    ORDER BY Link.Title    ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description:    Return all NOT hidden links from the requested category.--                If in Administrators role the return ALL links (the hidden ones also).-- =============================================    (@CategoryId int,    @AdminFilter bit)AS    IF (@AdminFilter = 1)    BEGIN        SELECT            Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden        FROM            Link        WHERE            Link.Parent = @CategoryId        ORDER BY Link.Title    END    ELSE    BEGIN        SELECT            Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden        FROM            Link        WHERE            Link.Parent = @CategoryId AND            Link.Hidden = @AdminFilter        ORDER BY Link.Title    END Regards, Sigurd 

View 4 Replies View Related

IF Inside Of Where Clause

Aug 7, 2000

I have a sql statement that has several OR statements in it which work fine. It looks like bottom below.

What I need to know is can you put a IF statement in a where clause like this. Such as
WHERE convert(datetime, patient_.df_admit_date, 101) > = @tdate or
if patient_.dru = "yes" convert(datetime, patinet_.df_admit_date, 101) > = @tdate - 8 or

WORKIN STATEMENT
select
PATIENT.ACCOUNT_ID,patient_.DF_PPD_POS_NEG, PATIENT.LAST_NAME, PATIENT.FIRST_NAME, PATIENT.MIDDLE_INIT, PATIENT.OTHER_ID_NUMBER,
PATIENT_.DF_ADMIT_DATE, PATIENT_.DF_PPD, PATIENT_.DF_PPD_POS_NEG, PATIENT_.DF_PPDB_DATE,
PATIENT_.DF_XRAY_DATE, PATIENT_.df_ppd_read, FROM
{ oj development.dbo.PATIENT PATIENT INNER JOIN development.dbo.PATIENT_ PATIENT_ ON
PATIENT.COMPANY_ID = PATIENT_.COMPANY_ID AND
PATIENT.DEPARTMENT_ID = PATIENT_.DEPARTMENT_ID AND
PATIENT.ACCOUNT_ID = PATIENT_.ACCOUNT_ID}
where
convert(datetime, patient_.df_admit_date, 101) > = @tdate or
convert(datetime,patient_.df_ppd, 101) >= @tdate - 2 or continued!!!

View 1 Replies View Related

Is There A Way Do Use If Inside Where Clause?

Jul 25, 2006

I am trying to do the following:


select * from table1
where createddate = '7/25/06'
and id = @temp

where @temp is char(1). The problem is @temp may be null or blank.

I didn't want to check @temp and then run the select statement.

How to check if @temp is not null or not empty inside WHERE clause and then run the select statement if @temp not empty?

Thanks for any help.

View 4 Replies View Related







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