Calling a Stored Procedure

[melginger]

Hi, I have a stored procedure that should return me a field with string data

My storedProc has 2 parameters

I don't know if I can do that but I want to call the sp during a SELECT depending of a CASE

SP:

Create proc spChListe 
	 @ElementListeID int = 205,
	 @ListeID int = 7

AS

DECLARE @Title AS varchar(8000)
SET @Title=''

SELECT @Title=@Title+Donnees+Suffixe  FROM T1

SELECT @Title

Now here's my SELECT:

SELECT 
CASE TypeDonneesID

WHEN 10 THEN

	CASE WHEN Donnees <> '' 

	THEN

		
        [b]spChListe 205,7[/b]


	else '' END

 ELSE
	Donnees

END as Donnees

FROM T2

I know that written like that, it doesn't work... But Is there a way to make it works?

Thanks

Mel

 

[SQLSister]

You can’t call a stored procedure a spart of a select.

You have three options (in order of preference)
Put the code in the select (you can only do this if the sp is not doing anything too complex)
Create function (You can only do this if using SQL Server 2000, functions aren't available in earlier versions)
Insert the results of the stored procedure into a temp table and then join to the temp table in the select. (Last resort if using an earlier version of SQL Server or if the procedure contains T-SQL that won’t work in a function.)

It looks to me like the sp is so simple it could easily be in the select. Incidentally your SP doesn't make sense to me, you aren't using the input variables for anything, so why are they there?


Questions about posting. See faq183-874