Calling procedure from select statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Calling procedure from select statement?

Author

Topic

skihappy
Starting Member

3 Posts

Posted - 2007-09-10 : 21:45:11

I want to return the contents of a cell, similar to array(x,y) from a select statement, and have tried the following:

Code: ( sql )
SELECT TOP 100 PERCENT dbo.ReturnDoorPrice(dbo.[Quote Units].DoorStyle, dbo.[Quote Units].UnitNo) AS Door, dbo.UnitsCost.CODE,
dbo.UnitsCost.DESCRIPTION
FROM dbo.[Quote Units] INNER JOIN ... etc

where i have created a procedure and function

Code: ( sql )
CREATE Procedure [dbo].[ReturnDoorPrice](@Doorstyle int,@unit int)
AS
begin
declare @lvreturn AS nvarchar(75);
EXEC @lvreturn = dbo.ReturnDoorPriceString @Doorstyle,@Unit;
print @lvreturn;
exec sp_executesql @lvreturn;
end
and the following function:

CREATE FUNCTION [dbo].[ReturnDoorPriceString](@Door int,@unit int)
returns nvarchar(75) AS
BEGIN
declare @lvstring AS nvarchar(75);

SET @lvstring = 'select top 1 ['+cast (@door AS nvarchar(3))+'] FROM dbo.[Door Price] WHERE (ID = ' + cast (@unit AS nvarchar(10)) +')'

RETURN @lvstring
END

I can get the string OK
i then can get the valve ok by execute sample
EXEC dbo.ReturnDoorPrice 2,1780;

returns value 81.675

But used with select causes error

how can i?
- Use procedure within select statement
or
- fix function to return value, not string

any help appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-10 : 21:57:31

you can't use sp_executesql inside a function. This is not allowed

KH
[spoiler]Time is always against us[/spoiler]

skihappy
Starting Member

3 Posts

Posted - 2007-09-10 : 23:48:10

Thanks KH, have worked that out already...

so call the procedure in a select statement?

or is there a better way?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-11 : 05:58:59

quote:
Originally posted by skihappy

Thanks KH, have worked that out already...

so call the procedure in a select statement?

or is there a better way?


Create table #temp(col1 .......)

Insert into #temp
EXEC procedure_name

Select columns from #temp

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 06:05:32

you should be thinking about how to go about that dsql you're creating... instead of trying to think how to execute that string

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 06:16:17

If your table "dbo.[Door Price]" was normalized, this would never happen!

E 12°55'05.25"
N 56°04'39.16"

skihappy
Starting Member

3 Posts

Posted - 2007-09-11 : 12:08:42

quote:
Originally posted by Peso

If your table "dbo.[Door Price]" was normalized, this would never happen!

E 12°55'05.25"
N 56°04'39.16"

PESO
all i can say is:
1) My system is normalized! the structure is complex
2) Everyone asks for help ocasionally - we were all beginners once
3) if you reply to posts, BE CONSTRUCTIVE in your critism, not make blatent comments!
4) does your comment help me?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources