how do I use dynamic sql inside functions?
-
and those are the bad guys which doesnt work:
It will only work IF I remove the dynamic query and replace by a static declare cursor query.
-- SET @cr_sql = 'DECLARE cursor_join CURSOR FOR SELECT DISTINCT( '+ @coluna +' ) FROM Split2Table( @valor, @delimiter )'
-- EXECUTE( @cr_sql )
SET @cr_sql = N'DECLARE cursor_join CURSOR FOR SELECT DISTINCT( '+ @coluna + N' ) FROM Split2Table( @valor, @delimiter )'
EXEC sp_executesql @cr_sql
-
Luciana:
It seems like you're forgotting to use the parameters in you sql ... here is the sintax of sp_executesql... try out it and use the @params to define your paramas and pass the params to the sp at execution...
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
I think that would be helpful..
Nicolas Donadio
-
Luciana,
If I understand what you are up to, once you have the values in a table, could you not simply use a formulation like this:
--
create table x (x char)
go
insert x select 'x'
insert x select 'y'
insert x select 'z'
insert x select 'Q'
declare @x varchar(255)
set @x = ''
select @x = @x + ' - ' + x
from x
select @x
(Along with whatever formatting of the result string you require.)
EHC
-
because i dont want to do many steps to convert some ids to their values. this way, I would have to code much more, and in the client application.
i do need a function to be called from the query and return the result as it was a value, in a transparently way
but thanks anyway
[]s
-
Luciana
what EHC is saying is to use his simpler solution instead the cursor, you don´t need the cursor...
You can use a single function... which calls Split2Table to obtain data in a temprary table var, and retrieve one soinlge string from this table var using EHC solution...
CREATE FUNCTION IDLIst2DescList( @IDList, @Delimiter)
RETURNS VARCHAR(1000)
AS BEGIN
DECLARE @RESULT VARCHAR(1000)
SELECT @RESULT = ISNULL(@RESULT+' - ', '') + Valor FROM Split2Table(@IDList, @Delimiter)
RETuRN @RESULT
END
and... in your main code, you'll have something like...
SELECT
...
Description = IDList2DescList( ids_field, ';'),
...
FROM
YourTabl
-
One of us missed the requirements of the whole project.
Can you repost the question with sample data and the required output please?
-
Thanks, Nicolas, that is what I meant. 🙂
Luciana, from what you have shown us, you do not need to use dynamic SQL nor a cursor to do this.
Just use this technique inside your function, pretty much as Nicolas has shown.
EHC
-
I'm not sure now where to post, whether here or in the other thread - but hopefully it doesn't matter. This is a very simplified function without any validity checks (whether the parsed string contains only numbers etc.), just to show how it works - but you can see it really is simple and you can call it in a SELECT. If I understood everything correctly, then the only remaining problem is that you can not specify table or column name. Do you need to use such function with many different tables? If there not too many tables you need to work with, you could make a special function for each of the tables.
/*testing environment*/
create table details(detid int, descr varchar(10))
insert into details (detid, descr) values (1, 'descr1')
insert into details (detid, descr) values (2, 'descr2')
insert into details (detid, descr) values (3, 'descr3')
insert into details (detid, descr) values (4, 'descr4')
insert into details (detid, descr) values (5, 'descr5')
/*function to parse input and return concatenated description*/
CREATE FUNCTION dbo.getdetails (@input varchar(500))
RETURNS varchar(2000)
AS
BEGIN
/*declare table variable to store parsed IDs*/
DECLARE @tmp_det TABLE (tmpid int identity, detid int)
/*declare other variables - intermediate and for final result*/
DECLARE @remains varchar(500),
@value int,
@result varchar(2000)
SET @remains = @input
SET @result = ''
/*parse the string and write IDs to table variable*/
WHILE CHARINDEX(';', @remains) > 0
BEGIN
SET @value = CAST(LEFT(@remains, CHARINDEX(';', @remains)-1) AS INT)
INSERT INTO @tmp_det (detid) VALUES (@value)
SET @remains = RIGHT(@remains, LEN(@remains)- CHARINDEX(';', @remains))
END
/*now insert the last piece (does not contain delimiter)*/
INSERT INTO @tmp_det (detid) VALUES(@remains)
/*get descriptions and concatenate them*/
SELECT @result = @result + descr + '-'
FROM @tmp_det t
JOIN details d on d.detid = t.detid
ORDER BY tmpid
/*delete trailing delimiter (-)*/
IF @result <> '' SELECT @result = LEFT(@result, LEN(@result)-1)
RETURN @result
END
call of the function : select dbo.getdetails (column_with_id_string), yadayada from yourtable join.... where ..... etc. You don't need cursors and it does both parse and output - of course you can divide the logic into 2 functions (parsing and getting description), if you find it better.
-
sample data: '14;-3;11;' ( n id values comma delimited ) like:
main_table
----------
roles otherfields
14;-3;11; ...
roles_table
-----------
id description
14 Administrator
-3 Administrator
11 Customers
expected data: unique descriptions of the ids still delimited, like:
'Administrator - Customers'
-
nicolas! hi!
I tried this function you said, and it worked!!! No need cursor and dynamic query anymore! yay! \o/
Now i'm trying to figure it out how I can pass a table result from a function to another in another post!
Thanks for everyone!! You guys are great! =^.^=
[]s
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply