Sp_executeSQL Perform Faster Than Stored Procedure, Why?

Dec 24, 2007



Hi,

I Have a stored procedure named pPatientFindByID that return one patient record from the Client.Patient table. The stored procedure has one parameter @patientID uniqueidentifier. i have set the NOCOUNT ON before running the SQL statement and making it OFF after it finishes execution. The table has a primary key ( clustered ) on patientID. The table has 66840 records in it. The index IS 0 (ZERO) percent fragmented and 3 level depth.

The stored proc returnes null or 1 record maximum. My problem is that the stored procedure takes about 2268 microsecond (2 millisecond) on average. while i execute the same SQL using sp_executeSQL it takes about 710 microsecond (< 1 millisecond). That is 3 time faster than stored procedure.

The SET OPTION are same for both the statement.

To troubleshoot the problem i have enabled the trace and captured the events. there were no recompile for both the stored proc and sp_executeSQL and not even the statement level recompilation.

I am trying to figure out why my stored procedure is taking longer than sp_executesql. Both the (stored proc and sp_executeSQL) has the same execution plan.


This is affecting our application performance. Does anyone know about this or have experience the same problem ever? your help would be greatly appriciated.

Data


1. Stored Procedure


/****** Object: StoredProcedure [dbo].[pPatientFindByID] Script Date: 12/24/2007 11:19:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[pPatientFindByID]

@patientID uniqueidentifier

WITH EXECUTE AS OWNER

AS

BEGIN

SET NOCOUNT ON



SELECT

[patient].[AVINumber] AS 'AVINumber',

[patient].[BirthDate] AS 'BirthDate',

[patient].[BreedID] AS 'BreedID',

[patient].[ChangeDate] AS 'ChangeDate',

[patient].[ChangeWorkstationID] AS 'ChangeWorkstationID',

[patient].[ClientID] AS 'ClientID',

[patient].[Color] AS 'Color',

[patient].[County] AS 'County',

[patient].[CountyRabiesTagNumber] AS 'CountyRabiesTagNumber',

[patient].[CreateDate] AS 'CreateDate',

[patient].[CreateWorkstationID] AS 'CreateWorkstationID',

[patient].[DeathDate] AS 'DeathDate',

[patient].[DeathReasonCode] AS 'DeathReasonCode',

[patient].[FirstVisitDate] AS 'FirstVisitDate',

[patient].[HasAllergy] AS 'HasAllergy',

[patient].[HasBitesOrScratch] AS 'HasBitesOrScratch',

[patient].[HasChronicCondition] AS 'HasChronicCondition',

[patient].[HasDeclinedPreventiveCare] AS 'HasDeclinedPreventiveCare',

[patient].[HasMedicalAllergy] AS 'HasMedicalAllergy',

[patient].[HospitalID] AS 'HospitalID',

[patient].[IsBites] AS 'IsBites',

[patient].[IsBitesInFear] AS 'IsBitesInFear',

[patient].[IsClinicalStudy] AS 'IsClinicalStudy',

[patient].[IsLitters] AS 'IsLitters',

[patient].[IsMixBreed] AS 'IsMixBreed',

[patient].[IsScratches] AS 'IsScratches',

[patient].[LastVisitDate] AS 'LastVisitDate',

[patient].[ManufacturerNameOfAVIChip] AS 'ManufacturerNameOfAVIChip',

[patient].[ManufacturerNameOfMicrochip] AS 'ManufacturerNameOfMicrochip',

[patient].[MicrochipNumber] AS 'MicrochipNumber',

[patient].[Name] AS 'Name',

[patient].[Note] AS 'Note',

[patient].[PatientID] AS 'PatientID',

[patient].[RabiesTagNumber] AS 'RabiesTagNumber',

[patient].[SexCode] AS 'SexCode',

[patient].[SpeciesID] AS 'SpeciesID',

[patient].[Status] AS 'Status',

[patient].[SystemLog] AS 'SystemLog',

[patient].[Weight] AS 'Weight',

[patient].[WellnessplanLevel] AS 'WellnessplanLevel',

[patient].[WellnessplanValue] AS 'WellnessplanValue'

FROM

[Client].[Patient] [patient]

WHERE

[PatientID] = @patientID

SET NOCOUNT OFF

END


2. sp_executeSQL


set nocount on

execute

sp_executeSQL N'

SELECT [patient].[AVINumber]

,[patient].[BirthDate]

,[patient].[BreedID]

, [patient].[ChangeDate]

, [patient].[ChangeWorkstationID]

, [patient].[ClientID]

, [patient].[Color]

, [patient].[County]

, [patient].[CountyRabiesTagNumber]

, [patient].[CreateDate]

, [patient].[CreateWorkstationID]

, [patient].[DeathDate]

, [patient].[DeathReasonCode]

, [patient].[FirstVisitDate]

, [patient].[HasAllergy]

, [patient].[HasBitesOrScratch]

, [patient].[HasChronicCondition]

, [patient].[HasDeclinedPreventiveCare]

, [patient].[HasMedicalAllergy]

, [patient].[HospitalID]

, [patient].[IsBites]

, [patient].[IsBitesInFear]

, [patient].[IsClinicalStudy]

, [patient].[IsLitters]

, [patient].[IsMixBreed]

, [patient].[IsScratches]

, [patient].[LastVisitDate]

, [patient].[ManufacturerNameOfAVIChip]

, [patient].[ManufacturerNameOfMicrochip]

, [patient].[MicrochipNumber]

, [patient].[Name]

, [patient].[Note]

, [patient].[PatientID]

, [patient].[RabiesTagNumber]

, [patient].[SexCode]

, [patient].[SpeciesID]

, [patient].[Status]

, [patient].[SystemLog]

, [patient].[Weight]

, [patient].[WellnessplanLevel]

, [patient].[WellnessplanValue]

FROM [Client].[Patient] [patient] WHERE [PatientID] = @patientID

',N'@patientID uniqueidentifier'

,'B1ABC1EA-D4AB-DB11-921E-00087434402F'





set nocount off

3- Trace Data



Name Event TextData Duration
SQL:BatchCompleted 12 exec dbo.pPatientFindByID @patientID='B1ABC1EA-D4AB-DB11-921E- 00087434402F' 2268
SQL:BatchCompleted 12 set nocount on execute sp_executeSQL N' SELECT [patient].[AVINumber] 710





DBA

View 8 Replies


ADVERTISEMENT

Why Does A Query Runs Considerably Faster Than Stored Procedure?

Dec 29, 2003

I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.

All the tests where made using the Query Analizer.

What could be wrong?

Thanks a lot in advance.

View 13 Replies View Related

Urgent: Whichj One Is Faster : SSIS Or Stored Procedure

Dec 17, 2007



Hi
My PM and me are on a discussion. He wants SSIS to simply upload the source excel files in staging and do rest of the ETL in SQl server stored procedure which I feel is primitive.
I have suggested him to use SSIS for whole ETL and not Stored procedure( method which indistry has discarded)
He sayd Stored procedure are more efficient than SSIS.

Please guide with some facts.

View 12 Replies View Related

Report Error Using Stored Procedure With Exec Sp_executesql

Feb 13, 2008

Hi,

Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?

I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.

Any help is much appreciated.
Debbie

View 4 Replies View Related

How Assign Value To Cursor Using Sp_executesql Procedure

Feb 25, 2004

What is error here when i declare cursor ?

declare curQueryVehicleHave cursor for
exec sp_executesql @strQueryVehicleHave



@strQueryVehicleHave this string contain a query

View 2 Replies View Related

Make Stored Proc Faster

Jun 17, 2004

Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:

ALTER PROCEDURE sproc_ReturnAvailability

@ExtractDate DateTime,
@DateFrom DateTime,
@DateTo DateTime,
@96hrPlusFlag int,
@AppointmentsCount int OUTPUT

AS

IF @96hrPlusFlag = 0

BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE

BEGIN
IF @96hrPlusFlag = 1

SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime >@DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)

END

Cheers...

View 1 Replies View Related

Will A Store Procedure Execute Faster Than Regular Select ?

Nov 7, 2003

Hello,

Lets say I have a SP that return 1000 records,

do I get any better speed if doing it on a SP instead of just SELECT without an SP ?

if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?


(Why should I use SP if not passing any parameters ?)

View 7 Replies View Related

Sp_executesql Within A Stored Proc?

Jun 16, 2008

I have the following code which works, but every time i try to put a procedure around it to pass in the 3 variables I set in this code manually it bombs on me.

~SQL~
USE [myDb]
-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE@tableName nvarchar(50)
DECLARE@personId nvarchar(36)
DECLARE@encId nvarchar(36)
DECLARE@prov_reviewOUT nvarchar(1)
DECLARE@prov_opened_templateOUT nvarchar(1)

--TEST:: add input values
set @tableName = 'master_im_'
set @personId = '9B9192AF-97E1-4FD6-8279-362E4944ECAC'
set @encId = '63570609-529B-4488-BBBF-E039A8F10D92'

-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'

-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '

-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT

select @prov_reviewOUT, @prov_opened_templateOUT

~endSQL~

works fine every time...

but when i do this:
~sql~
create procedure svsp_test
@tableName nvarchar(50),
@personId nvarchar(36),
@encId nvarchar(36),
@prov_reviewOUT nvarchar(1) OUTPUT,
@prov_opened_templateOUT nvarchar(1) OUTPUT
AS

-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)

-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'

-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '

-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,
@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT

~endsql~

and then run the proc like this:


declare @prov_reviewOUT nvarchar(1), @prov_opened_templateOUT nvarchar(1)
exec svsp_test 'master_im_', '9B9192AF-97E1-4FD6-8279-362E4944ECAC', '63570609-529B-4488-BBBF-E039A8F10D92', null, null
select @prov_reviewOUT, @prov_opened_templateOUT

I always get null returned. If i put a select statement into the stored proc create code as the last line, it shows the correct values, but trying to read them from the sp in the create procedure statement always returns null. Can someone help me figure out what I'm doing incorrectly please?

Thanks!

View 3 Replies View Related

Sp_executesql Vs. Stored Proc.

Jul 23, 2005

Greetings All, currentley there is a heated discussion in my place ofwork over which method is better/more efficient for simple selects.Background:1.) Simple Application that uses sql server for backened db.2.) The application is only inserting and selecting data from the db.3.) The developers want to use sp_executesql for simple selects andthe dba's want to use a stored proc.[color=blue]>From my reading it seems that sp_executesql has a bit of overhead with[/color]it and it is not as efficient as stored procs.I would appreciate anyone's input on which would be better for simplerepetitive inserts to the db: Stored Proc, or sp_executesql?Regards, TFD.

View 3 Replies View Related

AdHoc Query Faster Than Stored Proc?

Aug 28, 2004

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?

View 5 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related

Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc

Aug 15, 2007



Hi,


I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.


We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.


Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?


Thank you,


Michael Tzoanos

View 4 Replies View Related

Exec Sp_executesql Vs. Sp_executesql And Performance

Jul 23, 2005

This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?

View 3 Replies View Related

Not Able To Create Hash Table Inside Stored Proc Through Execute Sp_executesql @strQuery

Aug 21, 2007

Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.

now instead of select query , i want to creat hash table.
so i wrote :

set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through

execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.

Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .


please guide me how to do this?
its very urgent for me.
thanks in advance.

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

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







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