Stored Procedure in SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure in SELECT

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-12 : 15:56:58

I've written a new, complicated stored procedure that returns a scalar value for some new machines that we have.

Now I need to edit all of the existing software so that it can get this value, too. The problem is, the existing software consists of about 4 different large applications, and all of the existing applications use a basic "SELECT Col1, Col2 FROM Table1" format.

Here's what I want to do, but I don't know how to do it:


SELECT Col1, Col2, (exec sp_NewProc Col1) AS Col3
FROM Table1

If I can match this format, I don't have to monkey with any of the existing procedures, tables, etc. If I do change existing tables, I have to find a way to do it so that it does not cause any applications that are not using the new version to become unresponsive (like what happened earlier today).

Could someone show me how to do this?

This will go into the Fill() method of a DataSet in Visual Studio 2005.

Avoid Sears Home Improvement

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-12 : 16:06:25

You can't call an SP there.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-12 : 16:07:23

What does sp_NewProc do ? Can you post the code?

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-12 : 16:10:27

sp_NewProc takes in a part number, searches 3 databases, and returns a single integer.

The procedure by iteself squeezes into two screen-fulls on my monitor, but it runs very well.

(FYI: This value will be the air pressure our parts are tested with).

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-12 : 16:15:38

[code]CREATE PROCEDURE sp_GetUlTestPressure(@Serial_Number varchar(20)) As
begin
/* Created By Joe Pool
- This stored procedure is to be used by VTI
- so that they can easily access the test pressure select * from TestPressures
- for a given coil number.
*/
declare @coilType varchar(20), @isPsi bit, @item varchar(255), @test_result varchar(255)
declare @strPsi varchar(20), @index int, @subIndex int, @intPsi int, @lblPsi int, @count int, @bool int, @delimiter nchar(5)
set @delimiter='psi'
set @strPsi='-1'
set @lblPsi=-1
set @isPsi=0
set @count=0
select @intPsi=TestPressure from TestPressures where (CoilType=(select distinct CoilType from ParseFile where PartNo=SubString(@Serial_Number, 1, 7)))
select @test_result=Test_Result from ACP_Parts where (System_ID like '%Label%') and (Serial_Number like @Serial_Number)
if (@test_result is not null) begin
-- Example @test_result format: W.O.458079 R4195J,Grp4,RD4,TH71,Cond/SubCooler,650psi
-- Example for @Serial_Number='C036115 1211 09':
-- W.O.467075 R2042A,Grp1,RD3,TH20,Evaporator (001569 Override), Poor Print Quality, Poor Print Quality, Poor Print Quality
set @index=CharIndex(@delimiter, @test_result)
if (4 < @index) begin
set @item=SubString(@test_result, @index - 3, 3) -- gets value before the delimiter
if (IsNumeric(@item)=1) begin
set @lblPsi=Cast(@item as int)
end
end
if (0 < @lblPsi) begin
select /* @coilType as 'CoilType', */ @lblPsi as 'TestPressure'
end else if (0 < @intPsi) begin
select /* @coilType as 'CoilType', */ @intPsi as 'TestPressure'
end else begin
select /* @coilType as 'CoilType', */ Cast(-1 as int) as 'TestPressure'
end
end else begin
if (0 < @intPsi) begin
select /* @coilType as 'CoilType', */ @intPsi as 'TestPressure'
end else begin
select /* @coilType as 'CoilType', */ Cast(-1 as int) as 'TestPressure'
end
end
end
GO[/code]

Avoid Sears Home Improvement

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-12 : 16:23:54

convert this SP into a function and then use it in your select.


SELECT Col1, Col2,dbo.Function_NewProc(Col1) AS Col3
FROM Table1

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-12 : 16:23:56

can it not be changed in to a scalar function and called from the select statement?

2 cents

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-12 : 17:00:29

Well...

I guess that works. The query is running now, but whereas the query used to return in under a second for the entire table, it has now been running for over 3 minutes ...and it is still going!

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-12 : 18:04:16

I manually cancelled the query after it had been running for 23 minutes.

Avoid Sears Home Improvement

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 18:20:42

Other idea:

Put the results of Store proc in Temp table and index it . Join with your query.

souLTower
Starting Member

39 Posts

Posted - 2009-02-18 : 09:16:45

JP your performance will be slower if you use the approach you are trying since the function needs to be called for every row. If you really need to use a function there may be a variable sniffing issue. It's suggested to use local variables to avoid this. For example:


create function getStuff(@partNumber int) returns in AS
  declare myLocalPartNumber int
  set @myLocalPartNumber = @partNumber

  -- Do your query here
END

Another approach would be to create an indexed view which does the same lookup as the procedure you are using. For example:


select distinct REMOTE.partNumber, LOCAL.partNumber from
(
  -- Derived query to gather all possible local part numbers 
  -- which would normally have been passed to the proc
) LOCAL inner join
(
  -- derived query to gather all part numbers from your 3 other DBs
)

By using the later approach you will gain performance by not hitting a function for every row.

God Bless

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:31:54

you can however call the sp in select using OPENROWSET

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-18 : 11:23:28

Hi visakh16,

Would OPENROWSET take the same connection string that my SELECT statement in Visual Studio already uses? If so, I could fill it on the fly.

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 11:57:45

quote:
Originally posted by jp2code

Hi visakh16,

Would OPENROWSET take the same connection string that my SELECT statement in Visual Studio already uses? If so, I could fill it on the fly.

Avoid Sears Home Improvement

whats the current connection string you're using?

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-18 : 12:49:07

"Data Source=CPAPP.ACPINC.LOCAL;Initial Catalog=AIO_Data_Results;User ID=datauser1;Password=datauser1"

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 13:05:41

something like:-
something like


SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=AIO_Data_Results;Trusted_Connection=yes;User ID=datauser1;Password=datauser1',
     'your query.....') AS a;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources