Calling SP Inside The SP
Jul 13, 2004hai 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
ADVERTISEMENT
Calling Variable Inside T-SQL Statement
Oct 4, 2007Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =)
I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: XXXXXXXXXXXXX
-- Create date: 10/4/2007
-- Description: This proc is designed to pull all CSA
-- part sales from XXXXXX business system and upload them
-- into the local XXXXXXXX Database for commission reporting
-- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int
As
INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )
SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo
WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')
In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September.
This is the error message I get when I try to create the proc:
Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17
Incorrect syntax near '+'.
~~~ Thanks All~~~
Calling Stored Procedures Inside Cursors
Aug 13, 2001I have created a cursor using the following type of syntax:
DECLARE MyCursor CURSOR FOR
SELECT ID FROM tblEmployees
OPEN MyCursor
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor
Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.
Newbe Question: Calling Function Inside Select
Jul 20, 2005Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View 4 Replies View RelatedCalling Subscription Screen Inside Report Viewer
Sep 3, 2007Hello
I use SQL Server 2005 Reporting Service to develop all my Reports. i design the report and deploy to the report server. i have created a Web application where i use the above report to be shown inside a ReportViewer page.
when any user view's the report i also want to give them the subscription screen inside the report viewer. BIG QUESTION is how do i call the subscription screen of a specific report inside the report viewer?
any help is appreciated
/Chandresh Soni
Calling Managed CLR Procedure From Inside User Defined Function -- How To ?
May 15, 2008I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.
I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.
I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.
I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.
However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.
Any advice on how to do this?
Thanks,
Bruce
Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?
Sep 12, 2007For 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.
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
EXEC Inside CASE Inside SELECT
Nov 16, 2007I'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?
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
May 26, 2008Just 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.
While Inside Of A While?
Oct 16, 2000I 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
Stm Inside Sql Query
Jul 10, 2006Hi 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 + "'";
Can I Use IF Inside A Query?
Jan 7, 2008Is 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 RelatedIF Inside Of Where Clause
Aug 7, 2000I 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!!!
Is There A Way Do Use If Inside Where Clause?
Jul 25, 2006I 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.
New Session Inside SP?
Sep 16, 2004Hi:
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
Getting Servername Inside XP
Jan 28, 2005Hi :
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
Use ADO Inside ODBC?
Dec 30, 2003Hi, 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!
Loop Inside SP
Feb 1, 2004hello,
anyone for help?
what's the syntax of for.next, do while loop in Stored Proc?
ur help is much appreciated!
thanks,
Using IF Inside SELECT ?
Apr 19, 2006Is 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 RelatedCannot Use TOP Inside An Recursive SQL
Dec 11, 2007Hi ,
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
Using OR Inside A WHERE Clause
Jan 15, 2008Hi,
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
CONTEXT_INFO Inside CLR
Jan 21, 2008Hi,
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
How To Retrive A SP Table Inside A SP?
Feb 28, 2007Hello, 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
Relationship Inside The Same Table
Sep 19, 2005i 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 4 Replies View RelatedDropDownList Inside CreateUserWizard
Mar 25, 2006I 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"
Loop Inside Of A Cursor
Oct 23, 2000I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated
declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null
open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS <> -1)
begin
select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code
set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate)
while @cnter < @nodays
begin
while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = @fcnter + 1
set @erdate = @sdate
END
if @hfreq = 9
begin set @fcnter = 1
set @sdate = @sdate + 2
Set @cnter = @cnter + 2
end
else
begin
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
end
end
close dbcursor
deallocate dbcursor
Case Inside Begin.....end
May 16, 2002begin
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??
How To Execute A DTS Package From Inside A Job
Oct 12, 2005Good 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.
Executing SP Inside SP Dynamically
Feb 13, 2006I 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!
Loop Inside View
Apr 4, 2007Hello,
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!
Don't Want SPROC To Execute Whatever Is Inside
Mar 14, 2012I have this SPROC that is executed by a SSIS every hour. Basically, between 1AM-6AM, I don't want the SPROC to execute what ever is inside. it still keeps running whatever is inside the IF statement.
TRUNCATE TABLE DataSubscription
DECLARE @StartTime AS VARCHAR(100)
DECLARE @EndTime AS VARCHAR(100)
SET @StartTime = CONVERT(CHAR(10),GETDATE(),101) + ' 01:00AM'
SET @EndTime = CONVERT(CHAR(10),GETDATE(),101) + ' 06:00AM'
IF GETDATE() NOT BETWEEN CAST(@StartTime AS DateTime) AND CAST(@EndTime AS DateTime)
BEGIN
-- Do stuff here.
END;
Executing BCP Utility Inside Sp....
Feb 26, 2004Hi ,
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.