February 12, 2009 at 2:09 am
Hi there,
In the code below the last SELECT statement gets a list of countries, with concatenated strings of cars :
DECLARE@cars TABLE (countryid INT,model VARCHAR(20) )
INSERT INTO @cars (countryid,model) VALUES (1,'Ford')
INSERT INTO @cars (countryid,model) VALUES (1,'Chrystler')
INSERT INTO @cars (countryid,model) VALUES (2,'Opel')
INSERT INTO @cars (countryid,model) VALUES (2,'BMW')
INSERT INTO @cars (countryid,model) VALUES (2,'Volkswagen')
SELECT * FROM @cars
DECLARE@countries TABLE (id INT,countryname VARCHAR(20) )
INSERT INTO @countries (id,countryname) VALUES (1,'USA')
INSERT INTO @countries (id,countryname) VALUES (2,'Germany')
SELECT * FROM @countries
SELECT co.*,(SELECT ca.model+', ' FROM @cars ca WHERE ca.countryid=co.id ORDER BY ca.model FOR XML PATH('') ) AS carlist
FROM @countries co
The SELECT statement that I use to get the concatenated string, is one I use in more than one place, so I prefer to put it in a Stored Procedure named spGetCarList.
Is it possible to call that SP in a SELECT statement? Example in pseudo-code:
stored procedure spGetCarList:
CREATE PROCEDURE [dbo].[spGetCarList]
@countryid INT ,
@carList VARCHAR(MAX) OUTPUT
AS
BEGIN
SELECT ca.model+', ' FROM @cars ca WHERE ca.countryid=@countryid ORDER BY ca.model FOR XML PATH('')
END
query string to get list:
SELECT co.*, spGetCarList(co.id) AS carlist
FROM @countries co
It can be done with a user defined function, but that is no option for me (in case you want to know: my stored procedures run from the master system procedures, so all my databases can use them because they are fired within the context of each database. Calls to function do not run in the context of the database)
Thanks,
Raymond
February 12, 2009 at 5:41 am
[font="Verdana"]Absolutely it is not possible. Instead user User defined function[UDF]. You can call UDF in select statement.
Mahesh[/font]
MH-09-AM-8694
February 12, 2009 at 6:00 am
No, you can't. You can execute the proc and get a result set within another proc:
EXEC spGetCarList @Parm1 = '42'
You could insert the output of the procedure into a temp table and then use that within your code. This is an example from Jeff Moden that doesn't require you to create the temp table first:
SELECT *
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Do a search on the site. There are other examples.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2009 at 1:12 pm
[font="Verdana"]Actually, I think if you look at table-valued functions, they would do the trick.
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
[/font]
February 13, 2009 at 12:44 am
Thanks for all ya guys' input. I'll start trying a few ideas....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply