EXEC Inside CASE Inside SELECT

Select Case Inside Sql Statement ?
Code:


function findingcinemaid(nameofthecinema)

findcinemaid = "select cinemasid from cinemas" &_
" where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'"
set cinemaidfound = objconndb.execute (findcinemaid)

end function

select case foreachcinema

case 0

cinemaname = "ONE UTAMA"
findingcinemaid(cinemaname)



case 1


cinemaname = "MINES"
findingcinemaid(cinemaname)



case 2


cinemaname = "SEREMBAN 2"
findingcinemaid(cinemaname)



case 3


cinemaname = "KINTA CITY"
findingcinemaid(cinemaname)


case 4


cinemaname = "BUKIT RAJA"
findingcinemaid(cinemaname)


case 5

cinemaname = "TEBRAU CITY"
findingcinemaid(cinemaname)

case 6

cinemaname = "SUNWAY PYRAMID"
findingcinemaid(cinemaname)



case 7

cinemaname = "SURIA KLCC"
findingcinemaid(cinemaname)


end select




any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it

View Replies !

---

How To Write Select Statement Inside CASE Statement ?
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 Replies !

---

EXEC Inside A Cursor Iteration Problem
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 Replies !

---

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?
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 Replies !

---

Case Inside Begin.....end
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 Replies !

---

Subquery Inside Case?
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 Replies !

---

'Case' Statement Inside 'Where' Clause
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 Replies !

---

Simple Case Inside Of A Trigger
I will try to provide as much information as I can (last time I looked like a total NUB)

DATABASE: dbo.P21Play

TABLE:
[JemWorldshipExport]

FIELDS:
pick_ticket_no
void char
tracking_no
freight_out varchar (1.00)
Billing_Option varchar (1 or 2)

Ok so I am updating my db table with all of these fields accept for Billing_Option. What I am looking to do is

create a case statement that will look at my billing_option field (which will be either a 1 or a 2) and Change

my freight_Out field as following:

If Billing_Option is equal to 2 then make the output of freight_out equal to Null

If Billing_Option is equal to 1 then pass whatever was in freight_Out (ignore)

I am not sure yet how to write a CASE statement inside a trigger but I know its possible?

Here is my Trigger as it stands:


Code:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [JemWorldshipTrigger]
on [dbo].[JemWorldshipExport]
for insert as

DECLARE @pick_ticket_no decimal (19,0)
DECLARE @void varchar (1)
DECLARE @tracking_no varchar (40)
DECLARE @freight_out decimal (19,4)
DECLARE @Billing_Option varchar (50)

select @pick_ticket_no=pick_ticket_no,
@void=void,
@tracking_no=tracking_no,
@freight_out=freight_out from inserted

update P21PLAY.dbo.oe_pick_ticket
set P21PLAY.dbo.oe_pick_ticket.tracking_no=@tracking_no,
P21PLAY.dbo.oe_pick_ticket.freight_out= isnull(P21PLAY.dbo.oe_pick_ticket.freight_out,0) + @freight_out,

P21PLAY.dbo.oe_pick_ticket.date_last_modified=current_timestamp,
P21PLAY.dbo.oe_pick_ticket.last_maintained_by='Administrator'
where (P21PLAY.dbo.oe_pick_ticket.pick_ticket_no=@pick_ticket_no and @void='N')

update P21PLAY.dbo.oe_pick_ticket
set P21PLAY.dbo.oe_pick_ticket.tracking_no='UPS VOID',
P21PLAY.dbo.oe_pick_ticket.freight_out='0.0000',
P21PLAY.dbo.oe_pick_ticket.date_last_modified=current_timestamp,

P21PLAY.dbo.oe_pick_ticket.last_maintained_by='Administrator'
where P21PLAY.dbo.oe_pick_ticket.pick_ticket_no=@pick_ticket_no and @void='Y'

View Replies !

---

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
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 Replies !

---

Using IF Inside SELECT ?
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 Replies !

---

Using A While Inside A Select Statement
 

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 Replies !

---

SET Inside Of SELECT Statement
How can I do this

Select id
set @temp = id
from tblexample

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

View Replies !

---

Select Statement Inside UDf
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 Replies !

---

Counter Inside Select Statement?
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 Replies !

---

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

Manish Mehta

View Replies !

---

Declare Inside Select Statement?
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 Replies !

---

Select The CheckBox Inside The DataGridView
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 Replies !

---

Select Inside Update Query?
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 Replies !

---

Checking For @@ERROR After A SELECT Inside A Transaction?
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 Replies !

---

Subquery With Multiple Rows Inside SELECT
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 Replies !

---

Newbe Question: Calling Function Inside Select
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 Replies !

---

SELECT Query Stmt Inside Stored Procedure
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 Replies !

---

Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example

Select abc, cde, 'xyz' = Case
When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter}
When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter}
end
from MyTable

Thanks
Arcady

View Replies !

---

Problems Executing A SELECT Inside A TRAN Against Other Computer
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 Replies !

---

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

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 Replies !

---

How Can I Fill Data In Textboxes From Sql Databases But Two Different Tables When I Select A Name That Is Inside A Dropdownlist
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 Replies !

---

How Do I Imbed A Select Inside A Select
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 Replies !

---

While Inside Of A While?
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 Replies !

---

Can I Use IF Inside A Query?
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 Replies !

---

Stm Inside Sql Query
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 Replies !

---

Calling SP Inside The SP
hai guys

how should we have to cal the store procedure inside the same store procedure.

for Example

Create procedure A
as
Begin
Select * from mytable
execute A
end.

is this the correct one

View Replies !

---

New Session Inside SP?
Hi:

I want to open a new session/connection inside the execution of a stored procedure. Is this possible ?
I ask this because I need a new sesssion with its own transaction.

Thanks,
Rui Ferreira

View Replies !

---

Getting Servername Inside XP
Hi :

Can anyone tell me if it is possible to get information like : servername/databasename inside an extended stored procedure ?

I checked the "srv_pfield" function but it only returns user/password information.

Thanks,
Rui

View Replies !

---

Use ADO Inside ODBC?
Hi, everyone. I was using ODBC everywhere in my code and now I'm considering using ADO in a new project. However, I don't want to throw all the old ODBC code away. Is that possible that I can use some wrapper to use ADO underneath while having a ODBC interface?

Thanks!

View Replies !

---

Loop Inside SP
hello,

anyone for help?
what's the syntax of for.next, do while loop in Stored Proc?

ur help is much appreciated!


thanks,

View Replies !

---

IF Inside Of Where Clause
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 Replies !

---

Is There A Way Do Use If Inside Where Clause?
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 Replies !

---

CONTEXT_INFO Inside CLR
Hi,
I am wondering if it is possible ( I think I read it somewhere) to access the infomation inside CONTEXT_INFO inside CLR Code.

I am calling SET CONTEXT_INFO in my SQL Proc and I need to read the values back out inside a C# function.

Is this possible?

Thanks

Dave

View Replies !

---

Using OR Inside A WHERE Clause
Hi,
 
I have a quick question for you all...
 
If I use an OR statement inside the WHERE clause of a SELECT, should SQL Server evaluate both side of the OR or just the left hand side if it returns TRUE?
 
The reason I'm asking is that I have an SP the accepts a string parameter, this param is a search condition, say a name. The param is a nvarchar and can be null. In my SP I do this:
 
SELECT * FROM Customer
WHERE CustomerDeleted = 0
AND (
@searchText IS NULL OR CustomerID IN (SELECT ID FROM fn_GetSearchResults(@searchText))
)
 
The idea is that if the @searchText param is NULL then all Customers are return, otherwise the @searchText is used in a function to determine which customers match the criteria.
 
This only works if SQL stops evaluating the OR condition as soon as it comes accross a TRUE statement.
 
Thanks for any help
 
Graham

View Replies !

---

Cannot Use TOP Inside An Recursive SQL
Hi ,

I have created in my sqlserver 2005 database a stored procedure with the following code.
///////////////////////////////////////////////////////////////
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[d_sty_print_menu_per_role_per_app2]
    @menu_name VARCHAR(255) = NULL , 
    @is_user VARCHAR(255) = NULL ,
    @is_appl VARCHAR(255) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    ;WITH GetHierarchy (item_text ,orden , read_order,  item_parent , menu_item , enabled)
    AS
    (--Anchor.
        select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item ,
        (SELECT 'N' FROM PROFILE_PERMISSION PP
        INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
   WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
         not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
         PP.APPLICATION_CODE = @is_appl AND
         PP.MENU_NAME=@menu_name --and
         --PP.MENU_ITEM=tb1.menu_item
            ) as enabled
       
        From sys_menu_item as tb1
        where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help')
            And tb1.item_parent= @menu_name
    --Members
        UNION ALL
        select tb2.item_text, tb2.orden, tb2.read_order,  tb2.item_parent , tb2.menu_item ,
        (SELECT 'N' FROM PROFILE_PERMISSION PP
        INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
   WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
         not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
         PP.APPLICATION_CODE = @is_appl AND
         PP.MENU_NAME=@menu_name -- and
        -- PP.MENU_ITEM=tb1.menu_item
            ) as enabled
  
        from sys_menu_item as tb2 , GetHierarchy
        where  tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help')
        And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name    
    )

    select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled
    From GetHierarchy

END
///////////////////////////////////////////////////////////////
So far so good.
The problem is in a specific part of the sql statement (which is also part of my business logic).
the following statement has a little problem.

(SELECT  'N' FROM PROFILE_PERMISSION PP
        INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
   WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
         not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
         PP.APPLICATION_CODE = @is_appl AND
         PP.MENU_NAME=@menu_name --and
         --PP.MENU_ITEM=tb1.menu_item
            ) as enabled
When I'm executing, it tells me that the Subquerry is returning more than one rows. I have tried to use TOP 1 but Sqlserver 2005 doesn't allow you to do that because you are inside a recursion.
I have tried to do this

(SELECT TOP 1 'N' FROM PROFILE_PERMISSION PP
        INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
   WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
         not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
         PP.APPLICATION_CODE = @is_appl AND
         PP.MENU_NAME=@menu_name --and
         --PP.MENU_ITEM=tb1.menu_item
            ) as enabled

But the system prevents me from doing that.

Any ideas ? How can I return only one row (I don't care which one) ?

Can I put this Sql statement in a function and then call it inside this recursion ? Is it permitted ?

I would mostly appreciated any help you can give me.

Thank you
zkar

View Replies !

---

How To Retrive A SP Table Inside A SP?
Hello, inside of my SP i want to execute another SP, something like:
EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID
this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not(it returns just one row with 3 columns).
Columns it returns:QResult , Threads , Answers
SELECT @isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID   ?
or how do you get it?
Patrick

View Replies !

---

DropDownList Inside CreateUserWizard
I am using the createuserwizard, and have set up my own profile table which hold first name, last etc. In my CreateUserWizard, the field for Vendor is a dropdownlist that is to be populated from a list in a database. I set up the form without the createuserwizard, and everything went skippy, but when I placed it into the wizard, I now cannot access the control of the dropdownlist.
I have tried doing..
Using SqlConnection As New SqlConnection(WebConfigurationManager.ConnectionStrings("Personal").ConnectionString)Dim VendorID As DropDownListVendorID = CType(CreateUserWizard1.FindControl("VendorName"), DropDownList)
Dim MyReader As SqlDataReaderDim sel As String = String.Format("SELECT VendorID, Name FROM Vendors")Dim MyCommand As SqlCommandMyCommand = New SqlCommandMyCommand.CommandText = selMyCommand.CommandType = CommandType.TextMyCommand.Connection = SqlConnectionMyCommand.Connection.Open()MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)But when I go to databind, it says:Object reference not set to an instance of an object. VendorID.DataSource = MyReaderLine 30:             VendorID.DataValueField = "VendorID"Line 31:             VendorID.DataTextField = "Name"

View Replies !

---

SQL Server Inside Virtual PC
Hi:
I have a Virtual PC on my machine that has Win 2003 Server and SQL Server 2000.
Every time I try to connect to a remote sql server using Enterprise
Manager, I get an error that the sql server doesn't exist, or you don't
have  access to it.
When I am at work, I give my VPC an IP and I am able to access the sql server.

Is it true that if I need to connect to an SQL Server I need an IP fixed to the VPC?
Can you help please.

View Replies !

---

Relationship Inside The Same Table
i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation

View Replies !

---

Transaction Inside Sp_executesql
Hi to all,Probably I'm just doing something stupid, but I would like you to tellme that (if it is so), and point the solution.There ist the thing:I' having a sp, where I call other sp inside.The only problem is, the name of this inside sp is builded variously,and executed over sp_executesql:create pprocedure major_sp@prm_outer_1 varchar(1),@prm_outer_2 varchar(2)assome codingset @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +@prm_outer_2set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4output'set @nvar_prmStr = N'@prm_1nvarchar(128), ' +N'@prm_2nvarchar(128), ' +N'@prm_3nvarchar(4000), ' +N'@int_exRetCodeint output, ' +N'@prm_4varchar(64) output'exec sp_executesql @nvar_stmtStr,@nvar_prmStr,@prm_1,@prm_2,@prm_3,@int_exRetCode = @int_exRetCode output,@prm_4 = @prm_4 outputNow the issue is, I've transactions inside test_sp_11 lets say wherethe 11 is @prm_outer_1 + @prm_outer_2.These procedures are existing inside database, but are called dynamiclydepending of the parameters.The problem is, when I call the specified sp directly, the rollbacktransaction is working without any problem.Inside this procedures test_sp_xx, is a call of another sp (lets sayinside_sp).There is a transaction included.When it is called over major_sp, then the rollback is not performedbecause of error:Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54Cannot roll back transactio_bubu. No transaction or savepoint of thatname was found.The funniest way is, if there is no error inside, the commit is workingwithout any problem!The question is majory (because I'm almost sure, that this is anissue): is it possible, to have atransaction inside dynamicly called sp over sp_executesql?If ok to do that?Thank's in advanceMatik

View Replies !

---

How To Execute A DTS Package From Inside A Job
Good day!

Just wanted to ask how can I execute a DTS package from inside a scheduled job.

I have 3 DTS Package, so instead of scheduling the 3 package separately...I created a job to execute the 3 packages in a single scheduled time.

My syntax inside the command window is:
EXEC DTSPackageName

But its not working.
Any help is greatly appreciated.
Thanks.

View Replies !

---