|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
 Posted - 06/21/2004 : 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)
Maybe there is no solution, but I thought I'd ask!?!
Corey |
|
Concat function
