Conditional Logic In Stored Procedure
Jul 20, 2005
Hello.
Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.
if @switch = "B"
SELECT * from ikb where
ikbtitle like @ins1 and
ikbtitle like @ins2 and
ikbtitle not like @ins3 and
ikbbody like @ins1 and
ikbbody like @ins2 and
ikbbody not like @ins3
end
Thanks for any help or information with this.
View 2 Replies
ADVERTISEMENT
Aug 9, 2015
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I have attached my data model for reference.
View 9 Replies
View Related
Jan 27, 2005
I have a table containing a 'queue' of rows waiting to be processed by my application.
Is it possible to call a single stored procedure that selects a row, returns the data and then deletes the row ?
If not, what is the best logic for doing it with two stored procedures ?
The table contains a unique ID, DateTime and nVarChar colums and I could easily add a 'flag' if required.
Any suggestions appreciated.
Steve.
View 3 Replies
View Related
Nov 30, 2000
I am new to trying to do IF/THEN logic in a stored procedure. I want to return one query if @property=1 and another if @property=0. Can anyone give me a hint in how to do this correctly. Here's the basic idea of what I am doing:
Create Procedure "get_orders_by_date_and_card"
@property int =0
as
if @property=0
begin
SELECT stores
FROM shops_master_Table
end
else
begin
SELECT restaurants
FROM restaurant_master_table
end
return
View 4 Replies
View Related
Sep 28, 2006
Hi
i have written a script that collects the applications and the components each application uses.
I wanted to create a stored procedure so that i could get a snapshot of the application and the components that an application has at a given time.
i have the following database structure
CREATE TABLE [Components] (
[CompID] [int] IDENTITY (1, 1) NOT NULL ,
[RT] [nchar] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[Name] [nchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Version] [smallint] NULL ,
PRIMARY KEY CLUSTERED
(
[CompID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ApplicationID] (
[ApplicationID] [int] IDENTITY (1, 1) NOT NULL ,
[NodeID] [nchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[WinVer] [nchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[Hw] [nchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[BT] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[MT] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Cust] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[BVer] [nchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[BRel] [nchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[WStyle] [nchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[DateInst] [datetime] NULL ,
[SnapDate] [datetime] NULL ,
PRIMARY KEY CLUSTERED
(
[ApplicationID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AppComponents] (
[ApplicationID] [int] NOT NULL ,
[CompID] [int] NOT NULL ,
[InsT] [datetime] NULL ,
[Installed] [bit] NULL ,
[SnapDate] [datetime] NOT NULL ,
CONSTRAINT [FK_AppsComponents_ApplicationID] FOREIGN KEY
(
[ApplicationID]
) REFERENCES [ApplicationID] (
[ApplicationID]
),
CONSTRAINT [FK_AppsComponents_Components] FOREIGN KEY
(
[CompID]
) REFERENCES [Components] (
[CompID]
)
) ON [PRIMARY]
GO
is there a way of checking if the component is already in the data base if it is then just get the CompID and attach that CompId in the AppsComponents link table and put that in the AppComponents table. the snapdate field is to be the current datetime when the data is collected. Is there a current date function in SQL server? Is it possible to do this in a stored procedure?
View 8 Replies
View Related
May 21, 2008
I am working with stored procedures to provide conditional business logic for a customer based on triggers. Is it possible for a stored procedure to execute an external program ie opening or distributing a crystal report?
View 9 Replies
View Related
May 16, 2007
I have a stored procedure using UNION joins on three SQL queries.
Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :)
I need to return one result set, sorted by date... one complete result per day. eg: 5/15/2007 | XX | XX | XX | XX | XX | XX |5/16/2007 | XX | XX | XX | XX | XX | XX |5/17/2007 | XX | XX | XX | XX | XX | XX |
Currently, when I run the query, I'm getting three separate date values for each date...
eg:5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |etc
How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong".
Here is the stored procedure if you can help. I'd really really love the help!
C R E A T E P R O C E D U R E sp_ApptActivityDate
(@strWHERE as varchar(500), @strWHERECANCELED as varchar(500))
as
exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_ALL_ApptActivity ' + @strWHERE + '
UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_Active_ApptActivity' + @strWHERE + '
UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + '
ORDER BY dbo.vw_ALL_ApptActivity.[Date] ' )GO
View 12 Replies
View Related
May 18, 2000
Using: SQL Server 7.0
Is there any way to use conditional SQL in the SELECT clause of a stored procedure, i.e., use an IF-THEN to SELECT either COUNT(*) or a column-list based on the value of a parameter passed to the stored procedure? (I'd like to avoid having two sets of queries with the same WHERE clause).
View 3 Replies
View Related
Mar 28, 2001
Using: SQL Server 7.0
I need to create a stored procedure for a report Web page which allows the user to specify different criteria. Is there a way to conditionally add different WHERE clauses as needed to a SQL SELECT statement?
View 5 Replies
View Related
Aug 9, 2006
hello there
I have a dropdown list with a default value of
All employees
I would like to create the Where statement only if the
value of the dropdown list is Not All Employees
Select fieldname, fieldname2, fieldname3
from Table
If @dropdownVariable is not "All employees"
Where Employee = @dropdownVariable
View 4 Replies
View Related
Nov 13, 2003
I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.
CREATE PROCEDURE Milestone_Get
(@myID int, @iShowAll int)
AS
SELECT uid, name, date, registration_confirmed
FROM tbl_members
WHERE
If @iShowAll = 0
begin
(uid = @myID) AND (registration_complete = 0)
end
else
begin
(uid = @myID)
end
GO
Thanks,
david
View 2 Replies
View Related
Nov 4, 2005
I need to create a conditional if or case statement in SQL Server 2000
for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
Fuzzy
View 8 Replies
View Related
May 3, 2006
I want to return a subset of data from my 'items' table using a stored procedure based on a number of parameters as follows :
customer code varchar(8)
userid varchar(10)
status tinyint
where the customer code can be any valid customer code OR blank, the userid can be any valid userid or blank and the status can be 1 (item is open), 2 (item is closed) or 3 (item is open or closed, i.e. no filter on status)
Is there a better way to achieve this than the following disaster:
IF LEN(RTRIM(LTRIM(@custcode))) = 0
BEGIN
IF LEN(RTRIM(LTRIM(@userid))) = 0
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
ELSE
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
END
ELSE
BEGIN
IF LEN(RTRIM(LTRIM(@userid))) = 0
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
ELSE
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
END
View 6 Replies
View Related
Aug 11, 2006
Is there a simple way to do conditional totaling?
I have a table that has incremental values, so the maximum value is the current total, however on occassion the value is reset and the increment begins anew. What I need to do is get the total the maximum value each time it is reset.
ie.
Value Date
----- -----
12 1-1
14 1-2
100 1-8
35 1-10
50 1-12
5 1-15
the total would be 155 (100 + 50 + 5)
View 4 Replies
View Related
Feb 6, 2007
Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255) )
AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Please help,
Thanks,
bullpit
View 7 Replies
View Related
Apr 23, 2005
Hi,
I'm converting some direct SQL queries into stored procedures. The
original queries have conditional parts appended depending on certain
parameters like this:
Sql = "First part"
If certain condition then
Sql &= "Second part"
Endif
How could I do this in a stored procedure? So far I have made them like this:
IF @condition > 0
Fist part Second part
ELSE
First part
You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?
Thanks!
Noc
View 3 Replies
View Related
Apr 17, 2007
Created a stored procedure which returns Selected table from database.
I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure
(
@condition varchar(20),
@ID bigint,
@date1 as datetime,
@date2 as datetime
)
AS
/* SET NOCOUNT ON */
IF @condition LIKE 'all'
SELECT CllientEventDetails.*
FROM CllientEventDetails
WHERE (ClientID = @ID)
IF @condition LIKE 'current_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom <= ISNULL(@date1, EventFrom)) AND
(EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE ''
SELECT CllientEventDetails.*
FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
View 2 Replies
View Related
May 4, 2015
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
[Code] .....
View 6 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Apr 5, 2006
Hey guys,I have to import a csv file to my database and then add
some values to other fields based on these values. At present I use a
store procedure that does a bulk insert and then I use an update and
fetch inside the sql to perform these updates. I am not sure if this is
the best way. Is this bad, since some amount of busniess logic is in
the store procedure?If it is, how can I do this better?Thanks for the answer.
View 1 Replies
View Related
Jul 20, 2005
Hello,I stuck in a delimma.Where to put the business logic that involves only one updatebut N number of selects from N tables........with N where conditions
View 5 Replies
View Related
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
Aug 19, 2015
I have a table (currently with no constraints or relationships) with two columns:
Employee_CodeEarned_Leave_Balance
The leave balance should be updated automatically on a daily basis using a formula. I understand this can be done using stored scheduled procedures. To give you a full and clear picture, this is what I need:
IF [Employee_Code] LIKE 'KUW%'
[Earned_Leave_Balance] = (Today - [Employment_Date]) * (42 / 365)
ELSE
[Earned_Leave_Balance] = (31/12/CurrentYear - [Employment_Date]) * (30 / 335)
View 6 Replies
View Related
Dec 13, 2005
i would like some conditional settings in a stored procedure
i have a variable
@Variable
and I want to do a conditional statement like
if @Variable = 1 then @Variable2 = 1
Elseif @Variable = 3 then @Variable2 = 4
Else @Variable = 11 then @Variable2 = 12
not sure about how to implement elseif bit
i know you can have
{if this Else That} in T-Sql
View 3 Replies
View Related
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
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
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
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
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
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
Mar 7, 2008
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
View 6 Replies
View Related
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
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
