how do I use dynamic sql inside functions?
-
It happens that I need it to be a function to return values and receive params, where the query has columns as the function params:
function x( @param1 ) {
@sql = 'select'+ @param1+ 'from table'
execute (@sql)
..somecode..
return @y
}
I've read around and it seems that it can't be done. But i DO need it to return a value afterall.
So I'm wondering.. Any way to make a procedure receive params AND return a value just like the function do? Or perhaps any workaround?
[]s
-
You can only use EXEC in a function if calling and extended procedure.
Why do you think you need dynamic sql for this task?
-
because I want to pass the column name to the function which varies
so...well.. it leads me to a dynamic query Can it be done inside a function in another away? O.o
[]s
-
Look up the case statement, you don't need dynamic sql for this.
-
ah. sorry. I forgot to mention that this is a query from a cursor
i expected this to work but wont
SET @cr_sql = 'DECLARE cursor_join CURSOR FOR SELECT DISTINCT( '+ @coluna +' ) FROM Split2Table( @valor, @delimiter )'
EXECUTE( @cr_sql )
-
What are you trying to do exactly starting from my boss wants me to do this?
-
my boss just says 'solve it no matter how' hehehe
this is the solution more reasonable i found. I just want the column to be dynamic, because this is a function I could use in any other table.
-
why not a stored procedure with a output parameter ?
-
just to kill your curiosity a litle brief of what i do:
in a table i get a value like this: '14;-1;11;'
( yeah, they didn't normalize the table
((((each value is an id in another table. i need to bring the respective description of each id, translating it to the still not normalized values 'descrfrom14 - descrfrom-1 - descrfrom11'
so I did 3 functions:
1) split the values into 3 rows without the delimiters like
------
14
-1
11
2) foreach each of the previous result, joining to bring the respective descriptions
-------
descrfrom14
descrfrom-1
descrfrom11
3) a join function to put all 3 together from the previous result
----------
descrfrom14 - descrfrom-1 - descrfrom11
[]s
-
a procedure can receive params and return a value as the function can?
because after all i need to select the result of it from another query, like this:
(select dbo.JoinColuna( 'fieldname', field_with_thevalues_unormalized, ';' ) ) as description
-
From your description I don't understand, how do you know from which table which of the values should be? You have values 14, -1 and 11 that are ID in "another" table... but what table(s)? Or is it always the same table?
Stored procedure can not be used as a part of select statement... but I still think that it would be best to describe in more detail what input you have, what is the desired result and how the result is searched for. I mean description in words, like "column A contains ID values in table mytbl1, delimited with ";". Based on ID I need to get column named "description" from the same table and concatenate the result into one column delimited with "-"". I don't know why it has to be used in a select, so please explain that, too. Then some of the gurus here hopefully could bring up a better solution, without cursors and dynamic SQL.
Of course, the best way would be to normalize the database, but I know that often we are stuck with what we have and can't change the design
-
the function can be used as a part of select, as it is working.
i have a field with N ids delimited by ;
these ids are from a single table, that's why i can make a function to read id making a query join with the related tables.
I need to convert the ids delimited into the description of these ids ( id;id;id; >> desc-desc-desc )
the solution I made:
- split the ids into a table
- get the descriptions of each id into another table
- join the rows into a single value delimited with '-'
a query outside the functions must do a
SELECT myJoin( xtable.ids_field, ';' ), otherfields FROM yadayada
and the myJoin() is using the mySplit() and myDescription() functions.
So, in the midle of the code, i need one field to be passed as argument to one function which i want to be usefull for another situations.
I wish i could but I can't normalize the ids
I'm sorry i'm messing up, but I don't know what you guys are not understanding. I'm trying to abstract it to focus the point of problem to make it easier for you guys. Where and how I will use the functions would complicate it, i think. Would help if I pasted the 3 functions and the query here?
thanks for patience darling! =^.^=
[]s
-
If you are looking for a universal solution that would do all this, be able to accept name of column as a parameter and be used directly in a select then I think it is unfortunately not possible. But I may be wrong, so let's wait what other people have to say to it.
Good luck!
-
boy i think we messed it all! i will start it over. let me show you the code!
drop function Split2Table
CREATE FUNCTION Split2Table( @input varchar( 8000 ), @delimiter char( 1 ) = '|' )
RETURNS @output TABLE( id int identity, valor varchar( 2000 ), rolename varchar( 2000 ) )
AS
BEGIN
DECLARE @minhavar varchar( 2000 )
DECLARE @rolename varchar( 2000 )
DECLARE @join varchar( 2000 )
DECLARE @resultado varchar( 2000 )
WHILE( 1 = 1 )
BEGIN
--ver se o delimitador existe na string
IF charindex( @delimiter, @input ) = 0
BEGIN
--se não existir o delimitador, insere somente a string na tabela de saída
IF( @input '' )
BEGIN
SELECT @rolename = ( SELECT resultado from Roleid2Rolename( @input ) )
INSERT INTO @output ( valor ) VALUES ( @resultado )
END
BREAK
END
ELSE
BEGIN
DECLARE @valor varchar(2000)
SET @valor = substring( @input, 1, charindex( @delimiter, @input ) -1 )
IF( @valor '' )
BEGIN
SELECT @rolename = ( SELECT resultado from Roleid2Rolename( @valor ) )
INSERT INTO @output ( valor, rolename ) VALUES( @valor, @rolename )
SET @input = substring( @input, charindex( @delimiter, @input )+ 1, len( @input ) )
END
END
END
RETURN
END
-- SELECT top 10 valor, rolename FROM Split2Table( '14;0;-1;-2;', ';' )
results:
14Administrators
0Administrators
-1All Users
-2Host
-
now the join :
drop function JoinColuna;
CREATE FUNCTION JoinColuna( @coluna varchar( 2000 ), @valor varchar( 2000 ), @delimiter char( 1 ) )
RETURNS varchar( 2000 )
AS
BEGIN
DECLARE @saida varchar( 2000 ); SET @saida = ''
DECLARE @cr_rolename varchar( 2000 )
DECLARE @cr_sql varchar( 2000 )
-- 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
OPEN cursor_join
FETCH NEXT FROM cursor_join INTO @cr_rolename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @saida = @saida + '-' + @cr_rolename
FETCH NEXT FROM cursor_join INTO @cr_rolename
END
CLOSE cursor_join
DEALLOCATE cursor_join
SET @saida = SUBSTRING( @saida, 2, LEN( @saida ) )
RETURN( @saida )
END
-- select dbo.JoinColuna( 'rolename', '14;0;-1;-2;', ';' ) as myjoin;
results:
Administrators-All Users-Host
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply