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)
)
AS
BEGIN
SELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)
THEN (OR description LIKE @KEYWORD)
END
or am i limited to this:
....
BEGIN
IF @OPTION = 1
SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE
@KEYWORD
ELSE
SELECT id FROM projects WHERE title LIKE @KEYWORD
END
View Complete Forum Thread with Replies
Related Forum Messages:
EXEC Inside CASE Inside SELECT
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 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 !
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 !
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 !
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 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 !
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 !
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] where CurrentEmployee_YN=1 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"> <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> </center> <center> </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> <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> <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" /> </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 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 !
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 !
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 !
How To Access Database Inside Lan
Hi, I have an application inside the company domain. I am moving a part of this application to internet so external users can view/register in this application. Can some provide advise on what should be the webserver/datbase server architecture and what firewall settings required. Thanks
View Replies !
Executing SP Inside SP Dynamically
I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure. Exec sp_executesql N’exec procedurename {parameterlist}’, N’{parameter declaration}’, Parametervalues Now I want to read a particular value that is being return be the procedure. NOTE: procedure is returning a resultset. Please help me. Thanks!
View Replies !
Loop Inside View
Hello, is it possible to build a loop for the following statement? CREATE VIEW vwObjects as ( Select 2001 as year, 1 as quarter, id as id from dbo.objects o where o.edate >= '20010101' and o.sdate < '20010401' union Select 2001 as year, 2 as quarter, id as id from dbo.objects o where o.edate >= '20010301' and o.sdate < '20010701' ... union Select 2002 as year, 1 as quarter, id as id from dbo.objects o where o.edate > '20020101' and o.sdate < '20020401' ... ) I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year. Thank you!
View Replies !
Executing BCP Utility Inside Sp....
Hi , My reuierement is to execute a bcp to write the query output to a flat file. I am using the following syntax... exec xp_cmdshell 'bcp "SELECT top 50 * tbl_xyz" queryout tbl_xyz_output.txt -c -S abc -U x -P x' But I am getting the following error.... output --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CTLIB Message: - L6/O8/S5/N3/5/0: ct_connect(): directory service layer: internal directory control layer error: Requested server name not found. Establishing connection failed. NULL This bcp executes perfect if I am going to call from command prmomt. Can anyone please help me in this.... Thanks in Advance. -Mohit.
View Replies !
How To ...Transactions Inside Cursors
How can I do to use transations inside Cursors for Example: Declare C cursor for Select.... Open C Fetch C into ... While @@Fetch_status = 0 Begin Begin Trans Insert .... Insert .... If @@Error=0 Commit trans Else Rollback Fetch C into ... End It doesn't Work!!! How can i do to rollback only current row in the Cursor if an error occurs(for example insert a null value in a column that it doesn't support nulls)? Thank you very Much!!!
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 !
|
TRACKER
