February 19, 2011 at 12:05 am
dear kumar..
i knew the basics in SQL. what i m asking is some example for executing sql query which is passed thru another function like follows:
select dbo.test('select colname from table')
so this test() function pass the select query as string to another function and that second function has to return the result for the select statement.
Is it possible?
The way you have written it is not possible. You can't pass a select statement as parameter.
Why don't you use it like this:
SELECT dbo.test(colname) from table.
Otherwise you have to run a loop and then get each row of column one by one in a variable and then pass that variable to function.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
February 19, 2011 at 12:36 am
If you know any of the following, pls tell me.
Is that any other option for execute the select statement which passed as an argument in the function?
or
can we insert the result set values of the passed select statement into another table with in the function?
or
Is any other way call proc in function?
February 19, 2011 at 12:48 am
The answer to first 2 questions is - No
For third, there are indirect ways like using a batch file to execute it, but practially they are not great ways to achieve what you want.
if you have to perform DML, then go for procs.
You may paste here what exactly you are trying to achieve, and I may be in position to help you more.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
February 19, 2011 at 1:41 am
thanks, i need to concate multiple row values into a single row. so i have create proc for this. it's working. but i can't able to call it from my Powerbuilder (front end) application. i can call only functions from the backend. that is what i prefer function rather than procedures. the procedure syntax is:
create procedure [dbo].
(@query as varchar(3900)=null)
as
begin
declare @list as varchar(8000)
declare @Emp_UniqueID VARCHAR(1000)
declare @len AS bigint;
set @list = ''
if @query is not null
begin
exec('insert into list_items '+@query)
--Cursor Creation..
DECLARE cur_list CURSOR FOR SELECT listname FROM list_items;
OPEN cur_list
FETCH NEXT FROM cur_list INTO @Emp_UniqueID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @list = @list+CAST(@Emp_UniqueID AS varchar(5))+ ', '
FETCH NEXT FROM cur_list INTO @Emp_UniqueID
END
exec('delete from list_items')
CLOSE cur_list
DEALLOCATE cur_list
end
select @list
end
The above procedure returns what i expect. but i cannot my call this from my app. i need this as function.
If u know any other possibilites, tell me pls.
Viewing 4 posts - 61 through 63 (of 63 total)
You must be logged in to reply to this topic. Login to reply