Explain The Below Stored Procedure

May 7, 2008

CREATE procedure r_routeGetCache1
@pLongest varchar(32),
@pLongestCls int,
@pDate datetime,
@pSrcInt varchar(7),
@pSrcIntGroup varchar(64),
@pSrcIntDom varchar(64),
@routeclass int=0 ,
@pCLI varchar(32)='',
@pOperatorGroup int = 0 ,
@isgrade int=0

as
begin
declare @pc varchar(2)
declare @dd int
declare @hh int
declare @mm int

set @pc=left(@pLongest,2)
set @dd=1+(@@datefirst+datepart(dw,@pDate)-2)%7
set @hh=(datepart(hh, @pDate)-0)
set @mm=datepart(mi, @pDate)
set nocount on

--- Azam
select top 1 @routeclass = routecls from r_interface
where [group] = @pSrcIntGroup
and state='I'

if @routeclass is null set @routeclass=0
--- End

--insert
CREATE TABLE #operator_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[cls] [varchar] (1) NOT NULL ,
[pc] [varchar] (2) NOT NULL
)

insert into #operator_selected(routecls,oprid,cls,pc)
select x.routecls,x.oprid,x.cls,x.pc
from RoutingV3..r20_route_timecls x (nolock),
RoutingV3..r_TimeCls y with (nolock),
RoutingV3..r_interface tit with (nolock),
RoutingV3..r_timecode tco with (nolock),
RoutingV3..r_daycode dco with (nolock)
where x.routecls=0
and tit.id = x.oprid and tit.state = 'I'
and y.tintid =x.oprid and y.cls=x.cls
and y.dc=dco.id and y.tc=tco.id and y.sc=0
and @dd between dco.d1 and dco.d2
and ((24+@hh+isnull(tit.prefixcls,0))%24) * 100 + @mm between tco.h1
and tco.h2-1
and x.pc=@pc

CREATE TABLE #timecls_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[pc] [varchar] (2) NOT NULL ,
[prefixcode] [varchar] (50) NOT NULL ,
cnt int,
clsA int,
clsP int,
clsO int,
clsW int,
[cls] [varchar] (1)
)

insert into
#timecls_selected(routecls,oprid,pc,prefixcode,cnt,clsA,clsP,clsO,clsW,cls)
select a.routecls, a.oprid, a.pc, max(a.prefixCode) prefixCode,
count(*) cnt,
sum(case when a.cls='A' then 1 else 0 end) clsA,
sum(case when a.cls='P' then 1 else 0 end) clsP,
sum(case when a.cls='O' then 1 else 0 end) clsO,
sum(case when a.cls='W' then 1 else 0 end) clsW, 'Z' cls
from RoutingV3..r20_route12 a(nolock), #operator_selected b
where a.routeCls=@routeclass --b.routeCls
and a.oprId=b.oprId and a.cls=b.cls
and a.pc=b.pc
and left(@pLongest,prefixLen)=a.prefixcode
--- Exclude Artificial
--and not (id<0 and parentId is not null and parentId>0)
group by a.routecls, a.oprid, a.pc

update #timecls_selected
set cls = (case when (clsW>0) and (cnt = clsW) then 'W'
when (clsO>0) and (cnt = clsO) then 'O'
when (clsP>0) and (cnt = clsP) then 'P'
when (clsA>0) and (cnt = clsA) then 'A'
else 'Z' end)

update #timecls_selected
set cls = (select max(a.cls) from RoutingV3..r20_route12 a (nolock)
where a.routecls=#timecls_selected.routecls
and a.oprid = #timecls_selected.oprId
and a.pc=#timecls_selected.pc
and a.prefixCode=#timecls_selected.prefixCode
and ((a.cls='A' and #timecls_selected.clsA<>0) or
(a.cls='P' and #timecls_selected.clsP<>0) or
(a.cls='O' and #timecls_selected.clsO<>0) or
(a.cls='W' and #timecls_selected.clsW<>0))
)
where cls = 'Z'

CREATE TABLE #route (
seqno int identity(10,10),
priority int,
[id] int,
isactive int,
reason int,
exception int,
exceptionCls int,
calcexception int,
ext int,
oprid int,
parentid int,
routecls int,
prefixcode varchar(50),
universe varchar(5),
domain varchar(5),
pdomain varchar(5),
[group] varchar(40),
interface varchar(40),
userinfo varchar(40),
hint varchar(100),
clsorg char(1),
cls char(1),
cost float,
flag int,
rating int,
access varchar(10),
redlist int ,quality float)


if @isgrade =0
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else isnull(x.exceptionCls,0) end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc and
x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.cost
end
else
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else 10 end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc
and x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.quality,x.cost
end

set nocount on
select top 5
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #topN
from #route x, RoutingV3..r_interface y with (nolock)
where x.isActive=1
and x.oprId=y.id
and @pLongest like x.prefixcode+'%'
and exceptionCls<>0
order by seqno+[calcException], x.cost

select
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #all
from #route x, RoutingV3..r_interface y with (nolock)
where x.oprId=y.id
and @pLongest like x.prefixcode+'%'
order by seqno+[calcException], x.cost

delete #all
from #topN a, #all b
where a.id = b.id


set nocount off

select * from #topN where isActive=1
union all
select * from #all where isActive=1
end

View 3 Replies


ADVERTISEMENT

Can Anybody Explain Me What Is An Extended Stored Procedure And When Can It Be Useful ?

Feb 28, 2008



Hello Friends.....
I want to ask that What is mean by Extended Sored Porcedure and when can it be useful ?

Where can be it useful in our day to day SQL Seveloper's activites ?

waiting for reply....

View 4 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi 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?
 

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
 Any help on this would be appreciated.

View 1 Replies View Related

Calling Stored Procedure Fromanother Stored Procedure

Oct 10, 2006

Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames

View 16 Replies View Related

Use Resultset Returned From A Stored Procedure In Another Stored Procedure

Nov 15, 2006

I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
 Thanks,
Kevin

View 3 Replies View Related

SQL Stored Procedure Issue - Search Stored Procedure

May 18, 2007

This is the Stored Procedure below -> 
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/2/2007 4:52:19 PM ******/
 
CREATE  PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
 
IF (@i_WildCardFlag=0)BEGIN
 SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch  INNER JOIN   BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND  ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
 if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
 if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch  INNER JOIN  BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND  ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
 if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
 if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
 
 
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
 
Please help me regarding the same.
 
Thanks in advance.
 
Sandeep Kumar
 

View 2 Replies View Related

Sql Count Using Stored Procedure Withing Stored Procedure

Apr 29, 2008

I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks

View 10 Replies View Related

Exec Twp Stored Procedure In A Main Stored Procedure

Apr 29, 2004

Hi there

i have a stored procedure like this:

CREATE PROCEDURE SP_Main

AS
SET NOCOUNT ON

BEGIN TRANSACTION

exec SP_Sub_One output

exec SP_Sub_Two output


IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran

return
GO


now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.

now i can put the "begin transation" in the two sub stored procedure's. The problem is

what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"

now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data

how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.

Thanks in advance

Mahmoud Manasrah

View 1 Replies View Related

Calling A Stored Procedure Or Function From Another Stored Procedure

Mar 2, 2007

Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN



SET NOCOUNT ON



DECLARE @TTL int

SET @TTL = dbo.TTLValue()



DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

END

CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.

View 6 Replies View Related

Execute Stored Procedure From Stored Procedure With Parameters

May 16, 2008

Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY

CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC

Any assistance would be greatly appreciated.
Thank you,
Dave

View 6 Replies View Related

Can Someone Please Explain This To Me...

Jun 26, 2007

why do the following return the same datasets?
 select * from myTable where myData = ''
 select * from myTable where myData = '     '
in the first I'm specifically searching empty strings, in the second a sequence of five spaces. Yet both return any and all white character matches? Is this a "feature" of SQL...
P.S. I'm using T-SQL

View 2 Replies View Related

How Do I Run &#39;explain&#39; On A Sql??

Jun 27, 2001

Hi, does anyone know how to run an 'explain' on a sql query on SQL Server 2000?

Thanks.

View 1 Replies View Related

Could Someone Please Explain The Following...

Dec 1, 2005

As previous threads have indicated I've been trying to figure out what's slowing down our 3rd party ETL tool.

My research has figured out the culprit, but now I need some advice on the best way to handle it.

Here's what's going on...
SPID 1 = dynamic sql passed from Crystal
SPID 2 = ETL tool
SPID 3 = report in stored proc

Until a few days ago we didn't even know SPID 1 existed. These are different guys, using old version of Crystal passing crappy queries.

Here's the scenario
SPID 1 is running (inefficient select)
SPID 2 is waiting
30 minutes pass and SPID 2 is still waiting PAGEIOLATCH_SH
SPID 3 fires off

Now SPID 2 is blocking SPID 3, but SPID 2 is still waiting for SPID 1

Finally when SPID 1 finishes, everything catches up. But it appears that there is a period of at least 45 minutes when all SPID's are just sleeping.

My questions:
1. If SPID 1 is just running an inefficient select query, why is it holding up SPID 2 (Which reads and inserts). SPID 1 should have a shared lock on table, this would prevent SPID 2 from insert/update?
2. Is there any steps I can take to give SPID 2 precendence? or is it first come first serve?

Thanks in advance

View 10 Replies View Related

Explain

May 2, 2008

Any one explain me about this query exactly wat it will do

backup log iiswebappauditdb with truncate_only

View 3 Replies View Related

Explain Me

May 8, 2008

set @tb1='rptmain.Month01.DBO.LCDB' in

View 1 Replies View Related

Can U Just Explain....

Nov 3, 2007

Query:
insert into aaaa values(',Â?'&',',Â?'&');

BUJJI.

View 6 Replies View Related

Can Some Explain This To Me?

Oct 25, 2007

Hello,I run the following query on a database (SQL 2005):delete from person where pers_companyid in (select pers_companyid fromcompany where comp_expiry is not null)I wanted to delete all people associated with a company which had anexpiry date. The mistake I made was that the column pers_companyiddoes not exist. It should have been comp_companyid.However, the query ran anyway and deleted all records from my persontable? If I run the subquery on its own then it doesn't run as thecolumn is missing.Shouldn't I have got an error running this query?Thanks,David

View 2 Replies View Related

Please Explain Transaction For Me

Sep 29, 2007

 I found this on http://msdn2.microsoft.com/en-us/library/86773566.aspx which i am hoping may be able to resolve my problem but I am not sure I actually understand it. Where it shows // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction");
is the "SampleTransaction" nothing more then just a name of the transaction that is being created through this code or is it actually pointing to something?I believe that I understand the rest. Oh and in case you have a better way of doing what I am needing here is the SQL Query that is pulling info for me to display on a pageSELECT     p.product_ID, p.class_ID, p.category_ID, p.product_name, p.product_desc, p.product_image, p.product_dimension, p.product_o1, p.product_o2,                       p.product_o3, p.product_ac, p.product_ph, p.product_photo, pcl.class_name, pca.category_nameFROM         products AS p INNER JOIN                      productClass AS pcl ON p.class_ID = pcl.class_ID INNER JOIN                      productCategories AS pca ON p.category_ID = pca.category_IDBasically I am using the transaction to perform multiple updates seeing as how I will need to update multiple tables.  private static void ExecuteSqlTransaction(string connectionString){ using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } }} 

View 1 Replies View Related

Please Explain This SQL Trigger

Sep 25, 2005

I am new to triggers.I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @counter = 1 When does this counter change value? USE Northwind GO CREATE TRIGGER PriceCheck     ON [Order Details]     FOR INSERT     AS DECLARE @counter int     SET @counter= @@ROWCOUNT     DECLARE @unitprice money     DECLARE order_details_insert_cursor CURSOR FOR         SELECT Unitprice             FROM inserted     IF @counter = 1         BEGIN             IF(SELECT UnitPrice FROM inserted) > 300             -- If the price entered is greater than 300             BEGIN                 --print a warning                 PRINT 'Cannot enter price greater than 300'                 --Take back the command                 ROLLBACK TRANSACTION             END         END     ELSE         BEGIN             OPEN order_details_insert_cursor             FETCH NEXT FROM order_details_insert_cursor INTO @unitprice             WHILE @@FETCH_STATUS = 0                 BEGIN                     if @unitprice > 300                     -- If the price entered is greater than 300                     BEGIN                     --print a warning                         PRINT 'Cannot enter price greater than 300'                         --Take back the command                         ROLLBACK TRANSACTION                         RETURN                         --Exit trigger immediately                     END                     FETCH NEXT FROM order_details_insert_cursor INTO @unitprice                 END                                  CLOSE order_details_insert_cursor             END                          DEALLOCATE order_details_insert_cursor

View 3 Replies View Related

Explain Xp_cmdshell

Feb 2, 2006

I've got some questions about xp_cmdshell and was hoping someone could explain some things to me.  I've written a trigger for a table that uses 'xp_cmdshell' to launch a VB.Net application.  The syntax is correct and it "seems" to work but in the way I intended.  When the app launches there is no GUI present but when I check the task list I see that the exe has been launched.  This intrigued me so I started playing with it in Query Analyzer.  When I run the commandexec master.dbo.xp_cmdshell 'C:WINDOWSsystem32otepad.exe'I seen a process for notepad show up on my processes list started by 'system' as opposed to myself and notepad still doesn't open up for me to see.  I also noticed that the query executes until I kill that particular process.  I'm guessing cause it's waiting on the tranaction to complete.  What I want to know though is why can't I see the program when I open like this?  Is it because 'system' is the user behind it or is there some extra parameter to throw in the xp_cmdshell?  

View 3 Replies View Related

EXPLAIN Function

May 19, 2008

Is there some equivalent to MySQL's EXPLAIN function? (or some utility that does the same thing?)

Basically the function takes a query and will return a list of the tables in the query and the number of rows that would need to be checked. It's used to check how well the indexes are set. (If a table returns a very high number, that could be a bottleneck for the query).

Any help is appreciated. Thanks.

-D. Israel

View 1 Replies View Related

Explain Plan

Mar 17, 2008

Dear All,
what exactly is ExplainPlan and maintenance plan? and where would we use these?


i've found some artilce in the NET, but i'm not satisfied


please provide me good articles and your words for me

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

Can Someone Explain This Behaviour?

Jul 23, 2005

Hello All,The following script is reproducing the problem assuming you haveNorthwind database on the server.Please note it gives you the error message on line 12.USE tempdbGOsp_addlinkedserver 'Test17'GOsp_setnetname 'Test17', @@SERVERNAMEGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_works]GOCREATE PROCEDURE This_works@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.OrdersSELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITgoIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_does_not]GOCREATE PROCEDURE This_does_not@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITGOPRINT 'This_works'EXECUTE This_works 0PRINT ' 'PRINT 'This_does_not'EXECUTE This_does_not 0Thanks for any help or hint,Igor Raytsin

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved