Exec StoredProcedure in Query?

I have two stored procedures that have a common column and I need to write a query like this:

pseudo code:

Code Snippet

select * from (exec firstProcedure 'argument') T where T.ID not in (exec secondProcedure 'arg') S;

What's the proper way to do this?

It is not possible, Convert your Sp as inline table or table valued function (UDF).

Sample,

Code Snippet

Create Function firstProcedure (@.arg int)

returns table

return

(

Select id from Sysobjects

)

GO

Create Function secondProcedure (@.arg int)

returns table

return

(

Select id+10 id from Sysobjects

)

GO

Select * from firstProcedure(1) Where id not in (Select id from secondProcedure(2))

|||Another alternative is to create temp tables and use INSERT INTO ... EXEC proc syntax to load the data into two different temp tables and then to join the temp tables. My first choice is normally to do as ManiD suggested and create either a function or a view; however, there are some procedures that simply cannot be converted. If this is the case, the temp table option might work best.|||

You can also use functions OPENQUERY (if you added a linked server) or OPENROWSET.

Example:

SELECT TOP 10 *

FROM OPENROWSET('SQLOLEDB', '(local)';'my_user';'my_pwd', 'EXEC Northwind..[Ten Most Expensive Products]') as t

go

AMB

sql

Exec Stored Procedure

Hello

Which is faster :

to write a a big stored procedure with if conditions, or to separate them and call them using exec??

i.e:

if @.id=1
insert into ...
else if @.id=2
update...
--------
or
if @.id=1
exec InsertProcedure
else if @.id=2
exec UpdateProcedure

Please help

Its very difficult to jugde that which is faster inLine Query or Store procedure

Read What One of Microsoft Press books says about Store procedure

"

Stored procedures are executable server-side routines. They give you great power and performance benefits if used wisely. Unlike user-defined functions (UDFs), stored procedures are allowed to have side effects. That is, they are allowed to change data in tables, and even the schema of objects. Stored procedures can be used as a security layer. You can control access to objects by granting execution permissions on stored procedures and not to underlying objects. You can perform input validation in stored procedures, and you can use stored procedures to allow activities only if they make sense as a whole unit, as opposed to allowing users to perform activities directly against objects.

Stored procedures also give you the benefits of encapsulation; if you need to change the implementation of a stored procedure because you developed a more efficient way to achieve a task, you can issue an ALTER PROCEDURE statement. As long as the procedure's interface remains the same, the users and the applications are not affected. On the other hand, if you implement your business logic in the client application, the impact of a change can be very painful.

Stored procedures also provide many important performance benefits. By default, a stored procedure will reuse a previously cached execution plan, saving the CPU resources and the time it takes to parse, resolve, and optimize your code. Network traffic is minimized by shortening the code strings that the client submits to Microsoft SQL Server—the client submits only the stored procedure's name and its arguments, as opposed to the full code. Moreover, all the activity is performed at the server, avoiding multiple roundtrips between the client and the server. The stored procedure will pass only the final result to the client through the network"

And About User defined Funciton Or Say inline Query Statement

User-defined functions (UDFs) are routines that perform calculations/computations and return a value—scalar (singular) or table. Microsoft SQL Server 2000 introduced UDFs, which you developed with T-SQL and could incorporate in queries, computed columns, and constraints.

|||

probably the big stored procedure with everything in it will be faster but only by a matter of milliseconds. I would choose which ever option you will find easier to maintain and develop.

|||

Are there any clues or proves ??

Please I need that urgenlty

|||

It depends on what you are doing in the IF Loops. If you are just doing INSERTs one big proc should be fine, as far as performance is concerned. If you split it into multiple smaller procs, you have the ability to call individual proc if you need to reuse the INSERT to one of the tables rather than call the giant proc.

|||

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Thank you

|||

In case of splitting sp , will SQL server builds and stores execution plans for exec called procedures??

Plz Advice

|||

JRICE:

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Please re-read my earlier reply.

|||

Thanks for your reply,

I need an advice if Im concern about performance and I need reusability of my stored procedures, will it differ that much?

thank you in advance

exec an SP to return rows to another SP

Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??

Something like...

CREATE procedure spThisSP(@.param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @.param)

CheersYou could re-write the sub proc as a function|||Thanks for the reply pkr but I was hoping that I could re-use stored procedures somehow?
Duplicating the code one way or another seems "wrong"...

Cheers|||You could use a temp table...
select into #temp
exec proc1

select * from #temp

But I HATE temp tables.|||i didnt understand what you are trying to do but i know you can call a stored proc from another stored proc..

hth|||pkr,

But I HATE temp tables.

May I ask why? I'm not being critical, just trying to understand various people's like and dislike of temp tables.

Thanks,
Don|||My main hate is a really irrating bug/feature I've hit in the past where the db is convinced the temp table already exists when you try to create it but says it doesn't exist when you try to use it!!

Besides that, there are too many locking issues with tempDb that make it very difficult to effectively police developer code - IMO. I just try to avoid them wherever possible.|||Cool, thanks.

Don|||Thanks for all the replies. From your discussion I found thisarticle which provides some more helpful pointers in using temp tables.

exclude a field from a wildcard search (was "A simple question! (heeelp)")

Hey guys, i'm new in SQL and i have a very enervating problem.
I have a form and with some fields.I want users to be able to write just a part of a word in order to search for it (except the first field) .So,as you can see in the querie below i have put the "%" symbol as i know in all fields except tha first one.
So,this querie works only for the first field. If i put the "%" in the first field too the all the fields are ok.
The point is that I don't want the first field to search that way (with "%")...What should i do?
Thanks in advance!


strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID") & "' and "
strQ = strQ & " Surname LIKE '%" & Request.Form.Item("surname") & "%' and"
strQ = strQ & " Name LIKE '%" & Request.Form.Item("name") & "%' and"
strQ = strQ & " IdentityNo LIKE '%" & Request.Form.Item("IdentityNo") & "%' and"
strQ = strQ & " Address LIKE '%" & Request.Form.Item("Address") & "%' and"
strQ = strQ & " Area LIKE '%" & Request.Form.Item("Area") & "%' and"
strQ = strQ & " Zip LIKE '%" & Request.Form.Item("Zip") & "%' and"
strQ = strQ & " PhoneNo LIKE '%" & Request.Form.Item("PhoneNo") & "%' and"
strQ = strQ & " CellNo LIKE '%" & Request.Form.Item("CellNo") & "%' and"
strQ = strQ & " Email LIKE '%" & Request.Form.Item("Email") & "%' and"
strQ = strQ & " AddrNo LIKE '%" & Request.Form.Item("AddrNo") & "%';"Rather than put every column in the query you should perhaps consider testing if the requestform value has a value in. it is pointless including columns in the where clause if your setting will return all / any rows already.

eg ' not knowing the language you are using, but guessing at VB or one of its realtives...

strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID")
if len(request.form.item("surname"))>0 then
strQ = strQ & " AND Surname LIKE '%" & Request.Form.Item("surname") & "%'"
endif
if len(request.form.item("name"))>0 then
strQ = strQ & " AND Name LIKE '%" & Request.Form.Item("name") & "%'"
endif

etc.....

your code as written suggests it is expecting the user to place a value in each and every setting. You also might consider whether you are looking for an 'AND' or an 'OR' constraint ie are you looking for every occurance of Surname=Smith AND name=John or every row containing some with a Surname=Smith OR name=John
HTH|||I use "and" cause i want the search function to operate if i have fill in one or more fields.But the problem it's not there.This works fine.|||And somethings else

how can i see the results of COUNT?

strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"

P.S: I use vbscript and javascript|||And somethings else

how can i see the results of COUNT?

strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"

P.S: I use vbscript and javascript

Ok i found that (expr1000)!!!!

Exception from HRESULT: 0x80131904

Hi,

I write a custom component (destination component) that handle the error of my dataflow.

The custom component works fine on design time and runtime by using BIDS.

When I'm using the same package that use my custom component with DTEXEC,

I got the following error :

System.Exception: AcquireConnections : Exception from HRESULT: 0x80131904

at SSISGenerator.SSISErrorHandler.ErrorHandlerDestination.AcquireConnections(
Object transaction)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)

The error message point out that the problem is in acquireconnections method.

This is the code i'm using in my custom component for the acquireconnections method.

public override void AcquireConnections(object transaction)

{

try

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET,cm.Name));

this.sqlConnection = cmado.AcquireConnection(null) as SqlConnection;

if (this.sqlConnection == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET, cm.Name));

if (sqlConnection.State != ConnectionState.Open)

this.sqlConnection.Open();

}

}

catch (Exception e)

{

throw new Exception(MSG_ACQUIRECONNECTIONS + e.Message);

}

}

Does someone got an idea ?

Mathieu

Could you try to debug your method while called from DTExec?

Thanks.

|||

Hi,

I did some tests and I will explain you the result.

Debug the method with DTExec

I debugged with DTEXEC a solution that contain two project

01 - class library project for the custom component

02 - integration service project for a test package

I did the following tasks for the debug proces

01 - On debug tab, I put the information needed for debugging (start an external program to debug (DTEXEC) and the file for the parameter)

02 - I put the debug dll in GAC

03 - I make sure that I have a breakpoint set on PreExecute

04 - I hit F5 and the error didn't show up.

05 - I took the custom component (dll) and I copy it on the dev server.

06 - I put the debug dll in GAC.

07 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

08 - I remove the debug information and I've compiled the custom component in release mode.

09 - I put the release dll in GAC

10 - I executed the test package on my machine with DTEXEC and the error didn't show up.

11 - I took the custom component (dll) and I copy it on the dev server.

12 - I put the release dll in GAC

13 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

After all these tests, I was suprise since the error message disappears. So, I will do other tests and post the result.

For now, I have one idea for this error message. When I did some tests, I was using the same command line over and over. I'm using a environment variable that indicate the location of my dtsconfig file for the connection string of my connection. I'm not sure if I create the environment variable before openning the commandline. I think it can be one of the reason.

We will see.

Mathieu