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


ADVERTISEMENT

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

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager

Jun 15, 2007

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.



Thanks in advance



Daniel Buchanan.



If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

View 1 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 Statement Inside Sql Query??

Jun 7, 2008

Hello,

I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.

I could use the "decode function" but

I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..

I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?

View 1 Replies View Related

If Statement Inside Sql Query??

Jun 7, 2008

Hello,

I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.

I could use the "decode function" but

I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..

I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?

View 1 Replies View Related

Using IIF Statements Inside A Query?

Jan 30, 2008



Hi,

I'm wondering if it is possible to use IF statements in a query, for example if this was my query:

SELECT Asset, Source, Val1, Val2, Val3
FROM tableA

Say the sign of the Vals is always positive, but based on if the Source field is null i want to make the Vals negative.
Could I do something like this:

SELECT Asset, Source, (IIF Source = null, Val1*-1, Val1), (IIF Source = null, Val2*-1, Val2), (IIF Source = null, Val3*-1, Val3)
FROM tableA


When I try something like this it doesn't work, is there a way to do this in a query?

Thanks.

View 3 Replies View Related

Getting Result Of Query Inside Another Query

May 22, 2007

Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.

View 4 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

Timeout For Query Inside BEGIN_INIT

Jun 8, 2007

Hi all,
I have a website under development. when trying to load page for the first time, I am getting sometimes a timeout error messege.
My guess is that one of the queries is problematic and takes too long. I need to track the problematic query. one way to do that is using the ASP trace option.
So this is what I did and I got the below error. the error as I can see is inside a BEGIN_INIT function, but I dont have a fnction like this in my code.
Do you have any suggestion how to track? here is the trace code:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210


span.tracecontent b { color:white }
span.tracecontent { background-color:white; color:black;font: 10pt verdana, arial; }
span.tracecontent table { clear:left; font: 10pt verdana, arial; cellspacing:0; cellpadding:0; margin-bottom:25}
span.tracecontent tr.subhead { background-color:#cccccc;}
span.tracecontent th { padding:0,3,0,3 }
span.tracecontent th.alt { background-color:black; color:white; padding:3,3,2,3; }
span.tracecontent td { color: black; padding:0,3,0,3; text-align: left }
span.tracecontent td.err { color: red; }
span.tracecontent tr.alt { background-color:#eeeeee }
span.tracecontent h1 { font: 24pt verdana, arial; margin:0,0,0,0}
span.tracecontent h2 { font: 18pt verdana, arial; margin:0,0,0,0}
span.tracecontent h3 { font: 12pt verdana, arial; margin:0,0,0,0}
span.tracecontent th a { color:darkblue; font: 8pt verdana, arial; }
span.tracecontent a { color:darkblue;text-decoration:none }
span.tracecontent a:hover { color:darkblue;text-decoration:underline; }
span.tracecontent div.outer { width:90%; margin:15,15,15,15}
span.tracecontent table.viewmenu td { background-color:#006699; color:white; padding:0,5,0,5; }
span.tracecontent table.viewmenu td.end { padding:0,0,0,0; }
span.tracecontent table.viewmenu a {color:white; font: 8pt verdana, arial; }
span.tracecontent table.viewmenu a:hover {color:white; font: 8pt verdana, arial; }
span.tracecontent a.tinylink {color:darkblue; background-color:black; font: 8pt verdana, arial;text-decoration:underline;}
span.tracecontent a.link {color:darkblue; text-decoration:underline;}
span.tracecontent div.buffer {padding-top:7; padding-bottom:17;}
span.tracecontent .small { font: 8pt verdana, arial }
span.tracecontent table td { padding-right:20 }
span.tracecontent table td.nopad { padding-right:5 }






Request Details

Session Id:
sesqltrnnbq35g45fxkyqk45
Request Type:
GET

Time of Request:
6/9/2007 12:36:41 AM
Status Code:
500

Request Encoding:
Unicode (UTF-8)
Response Encoding:
Unicode (UTF-8)




Trace Information

Category
Message
From First(s)
From Last(s)

aspx.page
Begin PreInit



aspx.page
End PreInit
4.97502699318581
4.975027

aspx.page
Begin Init
5.03576665047642
0.060740

Unhandled Execution Error
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)  at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)  at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)  at System.Data.SqlClient.SqlConnection.Open()  at System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate)  at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation)  at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.GetConnectionHolder()  at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.LoadPersonalizationBlobs(WebPartManager webPartManager, String path, String userName, Byte[]& sharedDataBlob, Byte[]& userDataBlob)  at System.Web.UI.WebControls.WebParts.PersonalizationProvider.LoadPersonalizationState(WebPartManager webPartManager, Boolean ignoreCurrentUser)  at System.Web.UI.WebControls.WebParts.WebPartPersonalization.Load()  at System.Web.UI.WebControls.WebParts.WebPartManager.OnInit(EventArgs e)  at System.Web.UI.Control.InitRecursive(Control namingContainer)  at System.Web.UI.Control.InitRecursive(Control namingContainer)  at System.Web.UI.Control.InitRecursive(Control namingContainer)  at System.Web.UI.Control.InitRecursive(Control namingContainer)  at System.Web.UI.Control.InitRecursive(Control namingContainer)  at System.Web.UI.Control.InitRecursive(Control namingContainer)  at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
8.31843797517178
3.282671

View 3 Replies View Related

Query To Each Item Inside A Datalist

Dec 25, 2007

Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server">    Sub Page_Load(Sender As Object, E As EventArgs)        Dim DS As DataSet        Dim MyConnection As SqlConnection        Dim MyCommand As SqlDataAdapter        MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")        MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection)        'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True        DS = New DataSet()        MyCommand.Fill(DS, "aspnet_Users")        MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView        MyDataList.DataBind()                          End Sub</script><body>    <br />  <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server">      <ItemTemplate>        <table cellpadding="10" style="font: 10pt verdana" cellspacing="0">          <tr>            <td width="1" bgcolor="BD8672"/>            <td valign="top">                          </td>            <td valign="top">            <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div>               <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status:          </div>                            <p></div>              <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' >                                <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%>                                <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" >              </a>            &nbsp;&nbsp;</td>          </tr>        </table>      </ItemTemplate>  </ASP:DataList>  </body></asp:Content> Thanks a lot. 

View 5 Replies View Related

Connecting To 2 Databases Inside One Query

Sep 16, 2004

hi there,
I saw a similar thread before but i've tried it and no luck. I want run a query off a database (stawebdata.branchinfo) then a join into bluepages.phonelist

i've tired

JOIN bluepages.phonelist p

Incorrect syntax near the keyword 'JOIN'.

any ideas... is this possible?

complete code below:

select DISTINCT b.name, b.tel1, b.id, b.telprivate,b.sc, b.manager,b.branchEmail
from branchInfo b
inner
JOIN bluepages.phonelist p on b.name = p.subdepartment
where b.name like 'Sarah' or b.id LIKE '%@keyword%' OR b.county LIKE '%@keyword%' OR b.Area LIKE '%@keyword%' OR b.Manager LIKE '%@keyword%' OR b.AssistantManager LIKE '%@keyword%' OR b.Postcode LIKE '%@keyword%' OR b.PseudoCity LIKE '%@keyword%' OR b.TicketingTA LIKE '%@keyword%' OR b.SLC LIKE '%@keyword%' OR b.ItineraryTA LIKE '%@keyword%' OR b.TIDS LIKE '%@keyword%' OR b.ABTA LIKE '%@keyword%' and
p.department = 'UK Branch' and
b.isbranch <> 'False' or
(p.lastName = '@keyword' or p.firstname = '@keyword')
order by b.name asc
GO

View 2 Replies View Related

Is It Possible To Use Dynamic Query Inside A Func

Jun 17, 2006

Hi to all,

Is It possible to use dynamic qyery inside a function in sql server.

For Example:

Create function fn_Test
Returns Table
As
Return sp_ExecuteSql 'SELECT * FROM EMP'

Like this.

With regards
Amjath

View 10 Replies View Related

Deciding Approle Inside A Query?

Jan 9, 2008

Inside a query how can I decide which application role is presentlty active?

View 7 Replies View Related

Hi, Pls See The Query Inside, Reg. Removing Duplicates..

Mar 17, 2008

-- declared variables
declare @database_name varchar(100), @table_name varchar(100), @primary_key_field varchar(100)
declare @list varchar(8000)
-- set values to variables
set @list = ''
set @database_name = 'data200802_dan'
set @table_name = 'other02'
set @primary_key_field = 'callid'

use database

select @list = @list + column_name + ', '
from information_schema.columns
where table_name = @table_name --table name
and column_name != @primary_key_field --unique identifier
select @list = substring(@list, 1, len(rtrim(@list)) - 1)

--above 5 lines btw came from a helper in the msdn forum. thanks

SELECT DISTINCT @list
INTO '#' + @table_name
FROM @table_name
@table_name + ':'
IF (SELECT COUNT(*) FROM @database_name + '.dbo.' + @table_name) = 0
BEGIN
INSERT INTO
@database_name + '.dbo.' + @table_name + '(' + @list + ')'
SELECT
@list
FROM
'#' + @table_name
END
ELSE
BEGIN
DELETE @database_name + '.dbo.' + @table_name +' ( ' + @list + ')'
GOTO @table_name
END
DROP TABLE '#' + @table_name



the query above is basically.. selecting all the fields from a table in database W/OUT their primary key. then putting them in a temp table.. delete all the records in the original table. then paste the records from the temp table into the original table.

is there a way for this to work? i don't know how to use the variables w/ this script. please help me correcting this query..

this is for removing duplicates btw. thanks!

View 3 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

Multiple FROM Statements Inside A Single Query?

Mar 21, 2012

what is the logic in multiple "FROM" statements inside a single query?

View 1 Replies View Related

Converting Values To YesNo Inside Query

Mar 19, 2015

I have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?

Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,

[Code] ....

or

Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
THE_Big_One1.STC_SCAN,

[Code] ...

View 5 Replies View Related

How To Query Two MS SQL DB's On The Same Server Inside A Stored Procedure

Jul 20, 2005

Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!

View 5 Replies View Related

Change Local Variable Inside Query

Jul 20, 2005

/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter

View 1 Replies View Related

Query From Parameters Inside A Stored Procedure

Apr 30, 2008

Hi,

I have to write a stored procedure what repair a table.
It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID').
I do not found the way to write this procedure to can create statement from parameter of the procedure.
I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'.
If the parameter is in the 'WHERE', nothing problem.

My procedure is:
CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @OrphanRowCount int
select @OrphanRowCount = 1

while(@OrphanRowCount > 0)
begin
DECLARE KWCursor CURSOR FOR
SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);

OPEN KWCursor;
FETCH NEXT FROM KWCursor INTO @OrphanRowCount
CLOSE KWCursor
DEALLOCATE KWCursor

IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0)
BEGIN
exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)')
END
end
END
GO

How can I run fully parameterized queries from an SP.
I can make it only like exec('DELETE FROM' + @TableName + ...)


Thank you for any idea,
Imre

View 2 Replies View Related

Problems With ABS() Function Inside Sub Query (within WHERE-Clause)

May 28, 2008

Hello,

when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:

SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ?
GROUP BY Panel_Position)

i get this error returned:
0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.

I don't know where inside the sql string a conversion is necessary/fails.
Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:

SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ?
GROUP BY Panel_Position)

The only difference between the 2 statements is the ABS() function inside the sub query.

More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.

I would appreciate it very much if someone out there knows an answer or a hint and could tell me.

With kind regards,
Andre

View 5 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

Permission-problem On Simple Query Inside CLR Function

Aug 17, 2007

I have created an assembly with permission set safe, and a function inside the assembly.
The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.

However when I execute my CLR function I get an error saying something like:
"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"

I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.
And my .dll is also signed.

Has this something to do with writing something a config file?
I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?

View 6 Replies View Related

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 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 To Determine, Inside A Function, If A Linked-server-query Returned Results

Feb 13, 2004

Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL

(CAT_ASA is the linked-server's name)

Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!

View 3 Replies View Related

SQL Server 2012 :: Write A Loop On Result Of First Query Inside A Stored Procedure

Jan 23, 2015

I have to write a Stired Procedure with the following functionality.

Write a simple select query say (Select * from tableA) result is

ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4

Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?

View 2 Replies View Related

Execute A Query Inside Dataflow And Use The Fields Returned To Continue Dataflow... How?

Apr 17, 2007

Dear Friends,

I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...

I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(

How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?

Thanks!!

View 38 Replies View Related

SQL Query: Finding Records Between Datetime Inside Datetime

Mar 17, 2007

Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int)     |    stamp_start (Type: DateTime)        |      stamp_end (Type: DateTime)           |      Username (varchar)0             |      17-03-07 12:00:00                      |            17-03-07 12:30:00                     |     Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
 Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark

View 2 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

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