Error using scalar valued function in Stored Procedure

Hi,

I am getting the following error msg while executing a stored procedure (getAllCollections) which uses a scalar valued function.

error msg:

Could not find server 'Select dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Stored procedure:

ALTER PROCEDURE [dbo].[getAllCollections]

(

@.pCids varchar(7500)

)

AS

declare @.cmd varchar(8000)

set @.cmd='Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (' +@.pCids + ')'

print @.cmd

exec @.cmd

Where as the 'dbo.getCollectionList' is the scalar valued function. If i execute the query separately as below it works fine:

Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (1234)

I believe i am doing some silly mistake somewhere. Any help will be much appreciated.

Thanks.

Saran:

I think all you need to do is change this:

exec @.cmd

to this:

exec ( @.cmd )

Dave

|||

When you do exec @.cmd it takes the contents of @.cmd as a stored procedure name. Since you had 3 periods in your string, it was thinking 'SELECT dbo' was a server name, and 'collection_id) as cList from CollectionT c WHERE c' as a database.

Like Dave said, use exec (@.cmd)

|||

You should replace the dynamic SQL with a safer solution. You can use a TSQL table-valued function to split the list of IDs and use it like:

Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c

WHERE exists(

select * from split_str as s

where s.value = c.collection_id

)

Please see the link below for some solutions that show how to split a string into a table or list of values.

http://www.sommarskog.se/arrays-in-sql.html

|||

Thanks a lot. It solved the issue. Silly me. :)

- Saran.