Need Advise On Stored Procedure

Jul 27, 2005

Little rusty on my SQL but here is what I need.   Currently have a stored procedure that returns numerical data for 8 different element readings.    ( Carbon, Silicon, Sulfur, etc ).   This data is then put into a datagrid on an asp.net page and shows the readings for each test.On my asp .net page... they only want data that is out of the specified ranges to be shown ( EX:  > 1.6 AND < 2.0 ). Currently it is setup on the page to change the font color to red of data of its range but now they don't want data to be shown at all if for that one test everything is in its range.So how could I write my Where clause to check the 8 different element ranges and only need one of them to be true in order to show.Could I use an If / Then statement?Any help is appreciated.... Sorry this is quickly done but got to have it done soon.  

View 1 Replies


ADVERTISEMENT

Stored Procedure Loop Not Working, Please Advise, Code Attached

Apr 13, 2008

This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T                    (@PartNo                 varchar(20),                 @Wkorder        varchar(10),                 @Setup        datetime,                 @Line        smallint,                 @TT        integer,                 @Tester        smallint,                 @LT1        integer,                 @LT2        integer,                 @LT3        integer,                 @LT4        integer,                 @LT5        integer,                 @LT6        integer,                 @LT7        integer,                 @LT8        integer,                 @LT9        integer,                 @LT10        integer,                 @LT11        integer,                 @LT12        integer,                 @LT13        integer,                 @LT14        integer,                 @LT15        integer,                 @LT16        integer,                 @LT17        integer,                 @LT18        integer,                 @LT19        integer,                 @LT20        integer,                 @LT21        integer,                 @LT22        integer,                 @LT23        integer,                 @LT24        integer,                 @LT25        integer,                 @LT26        integer,                 @LT27        integer,                 @LT28        integer,                 @LT29        integer,                 @LT30        integer,                 @LT31        integer,                 @LT32        integer,                 @LT33        integer,                 @LT34        integer,                 @LT35        integer,                 @LT36        integer,                 @UnitFound        integer        OUT,                         @parameters_LamType         varchar(50)       OUT,                 @parameters_Shunt        real               OUT,                 @parameters_ShuType     varchar(50)       OUT,                 @parameters_Stack        real              OUT,                 @parameters_Steel          varchar(50)       OUT,                 @Partno11            varchar(20)    OUT,                 @Wkorder11            varchar(10)    OUT,                 @Partno12            varchar(20)    OUT,                 @Wkorder12            varchar(10)    OUT,                 @Partno24            varchar(20)    OUT,                 @Wkorder24            varchar(10)    OUT,                 @Partno29            varchar(20)    OUT,                 @Wkorder29            varchar(10)    OUT,                 @Partno34            varchar(20)    OUT,                 @Wkorder34            varchar(10)    OUT,                 --@DL1        integer        OUT,                 --@DL2        integer        OUT,                 --@DL3        integer        OUT,                 --@DL4        integer        OUT,                 --@DL5        integer        OUT,                 --@DL6        integer        OUT,                 --@DL7        integer        OUT,                 --@DL8        integer        OUT,                 --@DL9        integer        OUT,                 --@DL10        integer        OUT,                 @DL11        integer        OUT,                 @DL12        integer        OUT,                 --@DL13        integer        OUT,                 --@DL14        integer        OUT,                 --@DL15        integer        OUT,                 --@DL16        integer        OUT,                 --@DL17        integer        OUT,                 --@DL18        integer        OUT,                 --@DL19        integer        OUT,                 --@DL20        integer        OUT,                 --@DL21        integer        OUT,                 --@DL22        integer        OUT,                 --@DL23        integer        OUT,                 @DL24        integer        OUT,                 --@DL25        integer        OUT,                 --@DL26        integer        OUT,                 --@DL27        integer        OUT,                 --@DL28        integer        OUT,                 @DL29        integer        OUT,                 --@DL30        integer        OUT,                 --@DL31        integer        OUT,                 --@DL32        integer        OUT,                 --@DL33        integer        OUT,                 @DL34        integer        OUT)                 --@DL35        integer        OUT,                 --@DL36        integer        OUT)ASSET @Tester = 1WHILE @Tester < 36      BEGIN    Set @Line = (Select Line from dbo.location where Tester = @Tester)        IF @Line = 453        BEGIN        If @Tester = 1 BEGIN SET @LT1 = 453 END        If @Tester = 2 BEGIN SET @LT2 = 453 END        If @Tester = 3 BEGIN SET @LT3 = 453 END        If @Tester = 4 BEGIN SET @LT4 = 453 END        If @Tester = 5 BEGIN SET @LT5 = 453 END        If @Tester = 6 BEGIN SET @LT6 = 453 END        If @Tester = 7 BEGIN SET @LT7 = 453 END        If @Tester = 8 BEGIN SET @LT8 = 453 END        If @Tester = 9 BEGIN SET @LT9 = 453 END        If @Tester = 10 BEGIN SET @LT10 = 453 END        If @Tester = 11 BEGIN SET @LT11 = 453 END        If @Tester = 12 BEGIN SET @LT12 = 453 END        If @Tester = 13 BEGIN SET @LT13 = 453 END        If @Tester = 14 BEGIN SET @LT14 = 453 END        If @Tester = 15 BEGIN SET @LT15 = 453 END        If @Tester = 16 BEGIN SET @LT16 = 453 END        If @Tester = 17 BEGIN SET @LT17 = 453 END        If @Tester = 18 BEGIN SET @LT18 = 453 END        If @Tester = 19 BEGIN SET @LT19 = 453 END        If @Tester = 20 BEGIN SET @LT20 = 453 END        If @Tester = 21 BEGIN SET @LT21 = 453 END        If @Tester = 22 BEGIN SET @LT22 = 453 END        If @Tester = 23 BEGIN SET @LT23 = 453 END        If @Tester = 24 BEGIN SET @LT24 = 453 END        If @Tester = 25 BEGIN SET @LT25 = 453 END        If @Tester = 26 BEGIN SET @LT26 = 453 END        If @Tester = 27 BEGIN SET @LT27 = 453 END        If @Tester = 28 BEGIN SET @LT28 = 453 END        If @Tester = 29 BEGIN SET @LT29 = 453 END        If @Tester = 30 BEGIN SET @LT30 = 453 END        If @Tester = 31 BEGIN SET @LT31 = 453 END        If @Tester = 32 BEGIN SET @LT32 = 453 END        If @Tester = 33 BEGIN SET @LT33 = 453 END        If @Tester = 34 BEGIN SET @LT34 = 453 END        If @Tester = 35 BEGIN SET @LT35 = 453 END        END        SET @Tester = @Tester + 1      ENDSELECT       @parameters_LAMTYPE = LAMTYPE,       @parameters_SHUNT = SHUNT,       @parameters_SHUTYPE = SHUTYPE,       @parameters_STACK = STACK,       @parameters_STEEL = STEEL    FROM DBO.PARAMETERS A    INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF    WHERE B.PARTNO = @PARTNO    SET @UnitFound = @@rowcountIF @UnitFound = 0    BEGIN            SELECT               @parameters_LAMTYPE = LAMTYPE,               @parameters_SHUNT = SHUNT,               @parameters_SHUTYPE = SHUTYPE,               @parameters_STACK = STACK,               @parameters_STEEL = STEEL            FROM DBO.PARAMETERS            WHERE PARTNO = @PARTNO            SET @UnitFound = @@rowcount            END        --IF @LT1 = @Line  BEGIN SET @DL1 = 1 END        --IF @LT2 = @Line  BEGIN SET @DL2 = 1 END        --IF @LT3 = @Line  BEGIN SET @DL3 = 1 END        --IF @LT4 = @Line  BEGIN SET @DL4 = 1 END        --IF @LT5 = @Line  BEGIN SET @DL5 = 1 END        --IF @LT6 = @Line  BEGIN SET @DL6 = 1 END        --IF @LT7 = @Line  BEGIN SET @DL7 = 1 END        --IF @LT8 = @Line  BEGIN SET @DL8 = 1 END        --IF @LT9 = @Line  BEGIN SET @DL9 = 1 END        --IF @LT10 = @Line  BEGIN SET @DL10 = 1 END        IF @LT11 = 453  BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END        --IF @LT11 = @Line  BEGIN SET @DL11 = 1 END        IF @LT12 = 453  BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END        --IF @LT13 = @Line  BEGIN SET @DL13 = 1 END        --IF @LT14 = @Line  BEGIN SET @DL14 = 1 END        --IF @LT15 = @Line  BEGIN SET @DL15 = 1 END        --IF @LT16 = @Line  BEGIN SET @DL16 = 1 END        --IF @LT17 = @Line  BEGIN SET @DL17 = 1 END        --IF @LT18 = @Line  BEGIN SET @DL18 = 1 END        --IF @LT19 = @Line  BEGIN SET @DL19 = 1 END        --IF @LT20 = @Line  BEGIN SET @DL20 = 1 END        --IF @LT21 = @Line  BEGIN SET @DL21 = 1 END        --IF @LT22 = @Line  BEGIN SET @DL22 = 1 END        --IF @LT23 = @Line  BEGIN SET @DL23 = 1 END        IF @LT24 = 453  BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END        --IF @LT25 = @Line  BEGIN SET @DL25 = 1 END        --IF @LT26 = @Line  BEGIN SET @DL26 = 1 END        --IF @LT27 = @Line  BEGIN SET @DL27 = 1 END        --IF @LT28 = @Line  BEGIN SET @DL28 = 1 END        IF @LT29 = 453  BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END        --IF @LT30 = @Line  BEGIN SET @DL30 = 1 END        --IF @LT31 = @Line  BEGIN SET @DL31 = 1 END        --IF @LT32 = @Line  BEGIN SET @DL32 = 1 END        --IF @LT33 = @Line  BEGIN SET @DL33 = 1 END        IF @LT34 = 453  BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END        --IF @LT35 = @Line  BEGIN SET @DL35 = 1 END        --IF @LT36 = @Line  BEGIN SET @DL36 = 1 ENDGO

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

Advise Please

Aug 9, 2007

helloI have two related tabletable1cIdcDesctable2IdNamephonecIdThese two tables are relatedwhere cId in table 1 is primary key and cId in table2 is foreign keyIf I want to delete a record in table 1 which is related to table 2Which is faster and more accurateshould I write my stored procedure like thisif exists(select * from table2 where cID = @CID)delete from table1 where cID = @CIDelsereturn 0------------------------------------------or------------------------------------------delete from table1 where cID = @CIDif @@error<>0return 0

View 14 Replies View Related

Please Advise!!!

Feb 7, 2005

Hi there!!

I just want to ask one question and i am looking for advice or recommendation.


I want to make an application using windows application in .NET. this application is only to have one or two users to enter details of clients on the database using the application and it will be a search for client name or number and other search methods which is similar.

My question now, what database shall i use for this kind of application SQL server or MS Access. i am looking for a new product to use as the cleint can not afford expensive software. Please advise!! thanks!!

View 3 Replies View Related

Advise: DTS

Jul 20, 2005

Version: SQL Server 7.0App: MS Access 2000I created DTS to move records from MySQL to SQL Server (approx over 500,000+per months dataset) for purpose of canned and adhoc reporting. The apphave a simple interface using MS Access where user will input the timeframeof the dataset they will process.Both servers are physically located in the same bldg from another state.Previous to MySQL, they send us cd in text format and my app runs the DTSusing a BAT file containing the DTSRUN command. But I would like stay awayfrom the DTSRUN since they take awhile to load to the server, plus it runson background on client pc. I wanted a functionality where it will run onthe server without user supervision, and they can go about their businessand do something else. In other words, let the server do majority of thework.Now that MySQL came into the picture, the Network Support created a filedsnwhere sqlserver sits and I use that as a reference on my DTS (using UNC,\myserverfile$sample.dsn)The problem:1) I work from home and when creating and running this DTS's, it takes awhile to process;2) When specifying the UNC path on DTS and saved, for some reason itswitches to the User/System DSN section;3) I also tested from office with the same result (i connect using cablemodem)Now the only way I can think of to solve this problem:a) save the DTS as a file and send it over to Network Support and have themload it up and manually change the source location OR have them modify theDTS and change the source locationb) then create a DTS jobThe person who will be running this DTS might be running it from home oroffice. Also, is there a way to automate the DTS job using an SP? I wantedto make it as easier as possible for the end user, where all they have to dois enable the job and run only for this time and for this package.TIA!Bob

View 1 Replies View Related

Pls Advise Me. HELP Pls...

May 20, 2006

on the 1st opening of my web page it uses the membership control of asp.net
for logging on. sometimes it takes time to logon and worse is, it will
encounter sql server error. that can be resolved by just restarting that
specific page and it will be alright, it will logon normally. this only issue happens only on the first opening of the logon page.
i have read a certain article and it says there that on the first time
you access the sql server, sometimes the sql server needs a little more
time communicating back and forth.
how can i make my log-on page wait a little longer till it communicates
or finishes communicating to the sql server?
im afraid that this may happen once i deploy my web project. i am
expecting over 100 users and they might save to the database all
together. will i encounter problem on this particular?
i am using ASP.NET 2.0 and SQLExpress for this project. pls advise me.
BIG THANKS.

View 11 Replies View Related

Design Advise

Feb 14, 2007

I have been given a DB that currently stores Organizations. I need to add the ability to track multiple contacts for the  Organizations. The DB has several one-to-many relationships on the Organizations table, such as Documents and Notes. I would like to track these by contact as well as the organization.
Is it possible to add a tblcontact and a one-to-many relationships to the Organizations table as well as to the tblDocuments and tblNotes?
I appreciate any suggestions.
tblOrg (OrgID)
tblDocuments (DocID, OrgID)
tblNotes (NoteID, OrgID)
tblContact ???

View 3 Replies View Related

Advise Please! Upgrade SQL 6.5 To 7.0

Dec 16, 1999

Hi there,

I would be appreciate to hear some advice regarding my mission to upgrade from SQL Server 6.5 to 7.0. The database would still be located in the same server. Do I have to do checking on the datatypes? - many thanks-

View 4 Replies View Related

Certification: Looking For Advise

Oct 1, 2004

Hi All

I have been working with Ms access Database the last four years and have gained a lot of experience which help me to have extensive knowledge of Jet, Dao and Ado. Since The Begining of this year i hv been migrating to SQL server server and for that i bought two books :Programming Access project file with Microsoft Sql server and Access Developers Guide to Ms sql server : Chipman & Baron ( this oner is simply a great achievement ). They help me out in the very near past to complety migrate a two database approch Jet Application to a reliable (Adp/ SQL Server client server ) where major parts of the code business logic has been transfered to run on the server as stored procedures and all forms re-built as unbound. these merely leads to a great enhancement of the applications.

My goal now is register to MCDBA and get the certification. I am not interested by classroom training from Microsoft Parterns or online training. I am looking for books that can help to get well prepared for the exams.

Have found this two books in Amazon:

1 - MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, Second Edition

2- MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, 70-228, Second Edition

However their rating didn't encourage me to place directly my orders.

Does someone have any experience with them ?

Do you think they will considerably help me to add value to my knowledge of SQL Server and get ready for the exam ?

Do you know some others books you woul rather encourage me to buy ?

Any advice in this regards really appreciated.

View 2 Replies View Related

Backup Advise

Dec 31, 2007

HiWe usae Sql2000.I'm setting up backups jobs and need advise.When I run a backup of a database, does it automatically clean out thetransaction log ? For a complete backup ? for a differential backup ?When would it be best to run a shrinkdatabase command ? Why would I wantto run the shrinkdatabase ?ThnaksDavid Greenberg

View 3 Replies View Related

Need Advise On SSIS?

Jan 4, 2007

I am very new in SSIS. I have the Report Server set up with two dozen of the OLAP reports in it. The reports are connected to the OLAP database on a seperate computer running on Sql 2005 server and the data source for the OLAP DB is pointed to the warehouse database on another computer running on Sql 2000 server. The warehouse server updates daily from a number of "Production" Sql servers running on Sql 6.5 and Sql 7.0 with the legacy front-end data entry application. I know the update from the "Production" Sql 6.5/7.0 servers to the warehouse server complete around 9am in the morning everyday. How do I set up a package in SSIS to schedule the update of the OLAP database say 9:30am so that end-user can have the latest data to their report. The OLAP database has one fact table, half a dozen of cubes and ten dimensions. I know it is not an easy task to set up the package in SSIS. Please provide sample and step on how to do this. Thanks. One last question how to prevent/warn users from opening the report when SSIS updating the OLAP database?

View 1 Replies View Related

SCD Design Advise

Jan 4, 2008

Hello,

I have a dimension table that, aside from its surrogate key (which is auto-generated using an identity column), has only one varchar column. I'd like to use an SCD Transform to insert only new output in the dimension, but this won't (?) work because I have no business key (the single varchar column comes from the SSIS pipeline), so my question is: how should I approach this problem? Is the design incorrect (e.g. should I generate the SK in the pipeline and use it as a business key?!), or am I doing something wrong in SSIS?

Thanks

View 5 Replies View Related

ADVISE.....Experts ! Please

May 15, 2008

I am an Application Developer.
I know just this about T-SQL :
Insert, UPDATE, delete aRecord.
JOIN tables.
Create a Hierarchy column.
execute MERGE Statement. [in order to create IDENTICAL tables .]

Create a Master-detail TABLE . [PrimaryKey-Foreig-Key]

WHAT ELSE SHOULD I KNOW widly speaking about T-SQL ??

What are ESSENTIAL things you should know to Be a BIT of an EXPERT ???



Please be STRAIGHT and SIMPLE.



THANKs a LOT.




View 8 Replies View Related

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related

Advise On Column Type

Jan 2, 2007

Hello,In my web application I am listing steps of action:STEP           ACTION10   10.1  10.210.910.1010.203030.1etcHow should I set up my columns in my database?  I tryed decimal but the problem is that 10.1 and 10.10 is in theory the same while in my case it is absolutely not. I also tryed nvarchar but does not sort correctly.Should I create 2 int column, one for the main step and another one for the sub-step  (one would contain the part before the "." and the other would contain the part after the ".")?Thanks

View 4 Replies View Related







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