Concat function - 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)

Concat function

Author

Topic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 11:27:36

Alright, I recently read this blog entry:
http://weblogs.sqlteam.com/jeffs/archive/2004/06/03/1491.aspx

This entry linked to an article that enabled you to use xp's in an UDF to build concatenated values.

I was following along with a post this morning:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36439

And I decided to experiment. I got surprisingly far (I thought) as I managed to use xp's to return a concatenated @variable from a dynamic sql query without using a table of any sort. Please take a look at my sql to this point and help me through this.

Even though I only used objects found in the 'Extended Stored Procedures' section, i still recieved:
Server: Msg 557, Level 16, State 2, Procedure ConcatStr, Line 15
Only functions and extended stored procedures can be executed from within a function.

Here is my code:

quote:

CREATE FUNCTION dbo.ConcatStr
(
@qryStr nvarchar(1000),
@colName nvarchar(100),
@params nvarchar(1000)
)
Returns nvarchar(2000) as
BEGIN
Set @QryStr = Replace(@QryStr,@colName,'@rtnVal = isnull(@rtnVal + '','', '''') + ' + @colName)
Select @QryStr = 'Declare @rtnVal nvarchar(1000);' + @QryStr + ';Select @rtnValue = @rtnVal'

Declare @p1 int

Set @params = isnull(@params + ', ', '') + '@rtnValue nvarchar(4000) output'
exec master..sp_prepare @p1 output, @params, @QryStr, 1

Declare @rtnValue nvarchar(4000)
exec master..sp_execute @p1, '1', @rtnValue output

Return @RtnValue
END

Go
Declare @qryStr nvarchar(1000),
@colName nvarchar(100),
@params nvarchar(1000)

Set @qryStr = 'Select Prefix From myDb.dbo.myClientList Where Active = @Active'
Set @colName = 'Prefix'
Set @params = '@active nvarchar(1)'

Select myDb.dbo.ConcatStr(@qryStr,@colName,@params)