=iif(len(Fields!itemID.Value)>0,"red",iif( Fields!eventID.Value.equals("none"),"Yellow","PaleTurquoise"))
the error:
The BackgroundColor expression for the textbox €˜textbox19€™ contains an error: Object reference not set to an instance of an object.
how can i fix it
thanks!
Hi Experts,
I am new to this forum and SQL usage.
I have a database with following table fields as
Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Amount - Integer
Now i want to create a View & split the Amount column into 2 basd on
split this Amount into 2 columns as below:
Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Debit Amount - Integer (where D_C is D)
Credit Amount - Integer (where D_C is C)
Regards
SEkar
I have a derived column transformation that inspects two columns for spaces and creates a third column, initialised with either '1' or 'X'.
However the transformation is failing with the error"
Cannot insert the value NULL into column 'TNV-INSERT1'
"
Here's the expression I'm using:
SUBSTRING(rt_tran_inv_text,1,255) != " " ? " X" : SUBSTRING(rt_tnv_narr_cr,1,30) != " " ? " X" : "1"
Neither of the source columns contain nulls. Any idea why it's trying to insert nulls into the new column?
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;
Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
[code]...
I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.
Hi guys,
I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.
The details are as following:
There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.
Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.
After series of consequent executions I have got following log file:
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦
As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.
Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.
Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.
Hi all,
I ran the following CTE sql code:
Use ChemDatabase
GO
WITH PivotedTestResults AS
(
SELECT TR.AnalyteName, TR.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN TR.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN TR.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN TR.Result END)
FROM TestResults TR
JOIN Samples S ON TR.SampleID = S.SampleID
GROUP BY TR.AnalyteName, TR.Unit
)
SELECT AnalyteName, UnitForConc,
avg1 = abs(Prim + Dupl) / 2,
avg2 = abs(Prim + QA) / 2,
avg3 = abs(Dupl + QA) / 2,
RPD1 = abs(Prim - Dupl) / abs(Prim + Dupl) * 2,
RPD2 = abs(Prim - QA) / abs(Prim + QA) * 2,
RPD2 = abs(Dupl - QA) / abs(Dupl + QA) * 2
FROM PivotedTestResults
GO
//////////////////////////////////////////////////////////////////////////////////////
I got the following errors:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Unit'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Unit'.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!???
How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?
Please help and advise.
Thanks in advance,
Scott Chang
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.
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies View RelatedI'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?
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.
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
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
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 + "'";
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 RelatedI 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!!!
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.
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
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
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
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!
hello,
anyone for help?
what's the syntax of for.next, do while loop in Stored Proc?
ur help is much appreciated!
thanks,
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND
View 3 Replies View RelatedHi ,
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
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
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
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image,
2 (SELECT Location
3 FROM Location_Table
4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID,
5 (SELECT TypeOfProperty
6 FROM Type_Table
7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8 FROM Property_Table
9 WHERE (TypeID = @TypeID OR
10 TypeID IS NULL) AND (LocationID = @LocationID OR
11 LocationID IS NULL) AND (Price >= @MinPrice OR
12 Price IS NULL) AND (PriceMax <= @MaxPrice OR
13 PriceMax IS NULL)
This is working:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
"MONTH(Some_Date) >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
This is NOT:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
Month >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
it says - Invalid column name 'Month'
Why ? Why ? Why ?
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting
Alter Table Employees
Add Constraint CK_SNN
Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Any help would be nice. Thanks in advance.
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _
"S (@UserID, @UserName, @UserPassword, @Name, @Email)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userID.ParameterName = "@UserID"
dbParam_userID.Value = userID
dbParam_userID.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_userID)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)
Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_name.ParameterName = "@Name"
dbParam_name.Value = name
dbParam_name.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_name)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Message.Text = "Register Successed, click on the link WebCam for login"
Else
Message.Text = "Failure"
End If
End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs)
Line 53:
Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Line 55: Message.Text = "Register Successed, click on the link WebCam for login"
Line 56:
Source File: c:inetpubwwwrootweb_sitemusic
egister.aspx Line: 54
In the DB the fields are added as :
UserID as int
UserName as varchar
UserPassword as varchar
Name as varchar
Email as varchar
and I have TRYED to change from "varchar" on to "text" but I receive same error message.
PLEASE HELP !!!!!!!! this is not first time I get the same errors on the all reg pages :( WHY ? WHAT LINE I HAVE TO EDIT ?
Thank You !!!
Regards
Hi please lok at this SP I have written and point where am I commiting mistake.
The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails]
(
@ProductCode Int = null
)
AS
DECLARE @sqlString AS nvarchar(2000)
SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode,
Master.PROD_DESC_TEXT AS ProductName,
Detail.PiecesPerBox, Detail.Price
FROM cp_nafta_dws.PRODUCT AS Master
INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail
ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM'
BEGIN
IF NOT (@ProductCode = NULL)
BEGIN
SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode
END
END
EXEC @sqlString
GO
Thanks
Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does it not bring anything when Where is there?
Thanks,
This code is not updating the database, please help me
SqlCommand UpdCmd; SqlCommand SelCmd; SqlDataAdapter da; DataSet ds = new DataSet();
da = new SqlDataAdapter(); if (!(Conn.State == ConnectionState.Open)) { Conn.Open(); }
SelCmd = null; SelCmd = new SqlCommand("sp_SelectUserInfo",Conn); SelCmd.CommandType = CommandType.StoredProcedure;
oSelCmd.Parameters.Add("@UserID",userid);
da.SelectCommand = oSelCmd;
da.Fill(ds,"UserTab"); oUpdCmd = null; UpdCmd = new SqlCommand("sp_UpdateUserInfo",Conn); UpdCmd.CommandType = CommandType.StoredProcedure;
UpdCmd.Parameters.Add("@UserID",userid); UpdCmd.Parameters.Add("@FirstName",firstName); UpdCmd.Parameters.Add("@LastName",lastName); UpdCmd.Parameters.Add("@Region",region); da.UpdateCommand = UpdCmd; da.Update(ds,"UserTab");
Conn.Close();